Re: Confused by the default privilege - Mailing list pgsql-hackers
From | 孙冰 |
---|---|
Subject | Re: Confused by the default privilege |
Date | |
Msg-id | CA+czfDWCGg7_zShLEUKggj2C1=DULxf-zkGwa9tySB=oG+VMdQ@mail.gmail.com Whole thread Raw |
In response to | Confused by the default privilege (孙冰 <subi.the.dream.walker@gmail.com>) |
List | pgsql-hackers |
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
└────
pgsql-hackers by date: