Confused by the default privilege
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
Gee, I pasted the ending demonstration as html.
Re-pasting a text version.
----------------------------------------------------------------------------------
┌────
│ 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
└────