-1

I have postgresql 15 db. There I have different roles based on which users are created.

What I need is to revoke grant option for roles. In my company we need to do it via different system.

I tried:

REVOKE GRANT OPTION FOR ALL ON SCHEMA schema_name FROM role_name;

but it does not work.

What do I need to do to revoke grant option from roles?

Krzysztof Chojnacki
  • 115
  • 4
  • 1
    GRANT/REVOKE are commands not db objects and thus not themselves subject to grant/revoke privileges. The ability to issue these commands stems from object ownership: if you own an object you can grant/revoke access and **this cannot be taken away.** A possible option is to create a `OwnsEverything` role which - well owns everything - all tables, all views, all roles, etc. You do not grant this role to any other role/user, use it for your *different system*. Note that a `superuser` can still issue grant/revoke. **You essentially control this through administration procedures.** – Belayer Aug 11 '23 at 17:43
  • Slight correction. Roles are not owned so above should not include *all roles*. – Belayer Aug 11 '23 at 19:52

1 Answers1

-1

Ensure you have sufficient privileges (most preferably superuser) to run the REVOKE command then try REVOKE <the_privilege or ALL> ON ALL TABLES IN SCHEMA <schema_name> FROM <role_name> CASCADE;

You can also consult the Postgres docs on REVOKE to get more information on the REVOKE command.

Nnaemeka Daniel John
  • 333
  • 1
  • 7
  • This doesn't answer the question. Besides that, you should hardly ever use a superuser role while working with the database cluster. On AWS and Azure you don't even get a superuser role – Frank Heikens Aug 11 '23 at 18:07