Clever Geek Handbook

Revoke permissions for PostgreSQL

I'm trying to revoke all user permissions on the database, but I can't seem to get it to work.

I am doing:

REVOKE ALL PRIVILEGES ON DATABASE db1 FROM user1;
+3


source to share


1 answer


REVOKE ALL

refers to all permissions for a single object, not any related objects. In this case, you say "all permissions that apply to the database are as one object." The only permission that exists at the database level is CONNECT

and is by default granted a special role Public

, of which all other roles are members.

Thus, to deny access to a user in this way, you will have to revoke the CONNECT privilege from Public and then explicitly GRANT the roles you want to grant.



There are several other options that come to mind:

  • Set a DENY rule for this combination of user and database in pg_hba.conf
  • Revoke the USE of all schemas in the database so that they can connect but not access. I believe the schema has Public

    granted this role to the public role, so you need to revoke it first, like with CONNECT
  • Discard all objects from the database. This requires multiple type operators REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo FROM somebody

    for different types of objects. You should also use ALTER DEFAULT PRIVILEGES

    to ensure that the objects you create from now on are not accessible.
+2


source






More articles:


All Articles