Re: Confused by the default privilege

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
└────