Hi, My use case is to create an isolated interface schema consisting of only views and functions (possibly many schemas, for multi-tenancy or multi-version), which has the minimal access exposure. To reduce the mental and maintenance burden, I am inclined to create one role per interface schema, instead of creating separate roles for the owner and the user. As a consequence, the default privileges must be revoked from the owner. Explicit revocation works just fine, except that it requires repetitive and forgettable statements for each object in the schema.
The default privileges come to rescue. It mostly works, despite a bit of confusion to me. The ending contents are some experiments and demonstrations. To sum up, I have to either leave some non-critical privileges (e.g., trigger, references) by the default privilege mechanism or manually revoke all privileges, to stop the owner having all the default privileges. Plus, the first alternative is not applicable to functions because there is only one privilege for functions (execute). To me, it is confusing and less intuitive. Or is there something I miss? TL;DR Revoking all default privileges is effectively equivalent to revoking nothing, because an empty string of access privileges is handled as 'default'. Maybe 'NULL' for 'default', and '' (empty string) means nothing? Regards. ------------------------------------------------------------------------------------------ drop owned by owner; drop role if exists owner, guest; create role owner; create role guest; drop schema if exists s; create schema if not exists s authorization owner; DROP OWNED DROP ROLE CREATE ROLE CREATE ROLE DROP SCHEMA CREATE SCHEMA 1. tables 1.1. no-op set role to owner; create or replace view s.v1 as select 1; \dp+ s.v1 Schema Name Type Access privileges Column privileges Policies s v1 view select * from information_schema.role_table_grants where table_name='v1'; grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy owner owner postgres s v1 INSERT YES NO owner owner postgres s v1 SELECT YES YES owner owner postgres s v1 UPDATE YES NO owner owner postgres s v1 DELETE YES NO owner owner postgres s v1 TRUNCATE YES NO owner owner postgres s v1 REFERENCES YES NO owner owner postgres s v1 TRIGGER YES NO set role to owner; select * from s.v1; ?column? 1 1.2. default privilege: revoke all from owner alter default privileges for user owner revoke all on tables from owner; \ddp+ Owner Schema Type Access privileges owner table set role to owner; create or replace view s.v2 as select 1; \dp+ s.v2 Schema Name Type Access privileges Column privileges Policies s v2 view select * from information_schema.role_table_grants where table_name='v2'; grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy owner owner postgres s v2 INSERT YES NO owner owner postgres s v2 SELECT YES YES owner owner postgres s v2 UPDATE YES NO owner owner postgres s v2 DELETE YES NO owner owner postgres s v2 TRUNCATE YES NO owner owner postgres s v2 REFERENCES YES NO owner owner postgres s v2 TRIGGER YES NO set role to owner; select * from s.v2; ?column? 1 1.3. default privilege: revoke all but one from owner alter default privileges for user owner revoke all on tables from owner; alter default privileges for user owner grant trigger on tables to owner; \ddp+ Owner Schema Type Access privileges owner table owner=t/owner set role to owner; create or replace view s.v3 as select 1; \dp+ s.v3 Schema Name Type Access privileges Column privileges Policies s v3 view owner=t/owner select * from information_schema.role_table_grants where table_name='v3'; grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy owner owner postgres s v3 TRIGGER YES NO set role to owner; select * from s.v3; ERROR: 42501: permission denied for view v3 LOCATION: aclcheck_error, aclchk.c:3461 1.4. manual revoke all from owner alter default privileges for user owner revoke all on tables from owner; \ddp+ Owner Schema Type Access privileges owner table set role to owner; create or replace view s.v4 as select 1; \dp+ s.v4 Schema Name Type Access privileges Column privileges Policies s v4 view select * from information_schema.role_table_grants where table_name='v4'; grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy owner owner postgres s v4 INSERT YES NO owner owner postgres s v4 SELECT YES YES owner owner postgres s v4 UPDATE YES NO owner owner postgres s v4 DELETE YES NO owner owner postgres s v4 TRUNCATE YES NO owner owner postgres s v4 REFERENCES YES NO owner owner postgres s v4 TRIGGER YES NO set role to owner; select * from s.v4; ?column? 1 So far, the situation is identical to s.v2. set role to owner; revoke all on table s.v4 from owner; \dp+ s.v4 Schema Name Type Access privileges Column privileges Policies s v4 view select * from information_schema.role_table_grants where table_name='v4'; grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy set role to owner; select * from s.v4; ERROR: 42501: permission denied for view v4 LOCATION: aclcheck_error, aclchk.c:3461
