Revoke permissions for PostgreSQL
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
and is by default granted a special role
, 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
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.
source to share