postgres=# create group g1;CREATE ROLE                                                         postgres=# \du                                   List of roles Role name |                         Attributes                         | Member of -----------+------------------------------------------------------------+----------- g1        | Cannot login                                               | {} myrole    | Cannot login                                               | {} postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {} u1        |                                                            | {myrole}

REASSIGN OWNED BY doomed_role TO successor_role;DROP OWNED BY doomed_role;DROP ROLE doomed_role;

select     nsp.nspname as SchemaName    ,cls.relname as ObjectName     ,rol.rolname as ObjectOwner    ,case cls.relkind        when 'r' then 'TABLE'        when 'm' then 'MATERIALIZED_VIEW'        when 'i' then 'INDEX'        when 'S' then 'SEQUNCE'        when 'v' then 'VIEW'        when 'c' then 'TYPE'        else cls.relkind::text    end as ObjectTypefrom pg_class clsjoin pg_roles rol    on rol.oid = cls.relownerjoin pg_namespace nsp     on nsp.oid = cls.relnamespacewhere nsp.nspname not in ('information_schema', 'pg_catalog')    and nsp.nspname not like 'pg_toast%'    and rol.rolname = 'postgres'  order by nsp.nspname, cls.relname;

postgres=# SELECT       r.rolname,       ARRAY(SELECT b.rolname            FROM pg_catalog.pg_auth_members m            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)            WHERE m.member = r.oid) as memberofFROM pg_catalog.pg_roles rWHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',                        'rds_replication','rds_superuser',                        'rdsadmin','rdsrepladmin')ORDER BY 1;          rolname          |                           memberof                           ---------------------------+-------------------------------------------------------------- g1                        | {} myrole                    | {} pg_execute_server_program | {} pg_monitor                | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} pg_read_all_settings      | {} pg_read_all_stats         | {} pg_read_server_files      | {} pg_stat_scan_tables       | {} pg_write_server_files     | {} postgres                  | {} u1                        | {myrole}(11 rows)

postgres=# grant u1 to g1;GRANT ROLEpostgres=# WITH RECURSIVE x AS(  SELECT member::regrole,         roleid::regrole AS role,         member::regrole || ' -> ' || roleid::regrole AS path  FROM pg_auth_members AS m  WHERE roleid > 16384  UNION ALL  SELECT x.member::regrole,         m.roleid::regrole,         x.path || ' -> ' || m.roleid::regrole FROM pg_auth_members AS m    JOIN x ON m.member = x.role  )  SELECT member, role, path  FROM x  ORDER BY member::text, role::text; member |  role  |        path        --------+--------+-------------------- g1     | myrole | g1 -> u1 -> myrole g1     | u1     | g1 -> u1 u1     | myrole | u1 -> myrole(3 rows)

-- Revoke privileges from 'public' roleREVOKE CREATE ON SCHEMA public FROM PUBLIC;REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;-- Read-only roleCREATE ROLE readonly;GRANT CONNECT ON DATABASE mydatabase TO readonly;GRANT USAGE ON SCHEMA myschema TO readonly;GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;-- Read/write roleCREATE ROLE readwrite;GRANT CONNECT ON DATABASE mydatabase TO readwrite;GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;-- Users creationCREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';-- Grant privileges to usersGRANT readonly TO reporting_user1;GRANT readonly TO reporting_user2;GRANT readwrite TO app_user1;GRANT readwrite TO app_user2;

postgres=# create user u1;CREATE ROLEpostgres=# create user u2;CREATE ROLEpostgres=# grant create on database postgres to u1;GRANTpostgres=# \c postgres u1You are now connected to database "postgres" as user "u1".postgres=> create schema myschema;CREATE SCHEMApostgres=> \dn+                           List of schemas   Name   |  Owner   |  Access privileges   |      Description       ----------+----------+----------------------+------------------------ myschema | u1       |                      |  public   | postgres | postgres=UC/postgres+| standard public schema          |          | =C/postgres         +|           |          | myuser=UC/postgres   |  repack   | postgres |                      | (3 rows)

postgres=> grant create on schema myschema to u2;GRANTpostgres=> \c postgres u2You are now connected to database "postgres" as user "u2".postgres=> create table myschema.t1(id int);CREATE TABLEpostgres=> \dt+ myschema.t1                            List of relations  Schema  | Name | Type  | Owner | Persistence |  Size   | Description ----------+------+-------+-------+-------------+---------+------------- myschema | t1   | table | u2    | permanent   | 0 bytes | (1 row)postgres=> \c postgres u1You are now connected to database "postgres" as user "u1".postgres=> drop table myschema.t1 ;        ---schema的owner u1删掉了u2创建的表DROP TABLE---当然也可以更加暴力使用cascade删除整个模式postgres=> drop schema myschema cascade;    NOTICE:  drop cascades to table myschema.t1DROP SCHEMA

postgres=# create user u3;CREATE ROLEpostgres=# create user u4;CREATE ROLEpostgres=# alter user u3 createdb;ALTER ROLEpostgres=# \c postgres u3You are now connected to database "postgres" as user "u3".postgres=> create database db1;CREATE DATABASEpostgres=> \l+ db1                                              List of databases Name | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description ------+-------+----------+-------------+-------------+-------------------+---------+------------+------------- db1  | u3    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7737 kB | pg_default | (1 row)db1=> \c db1 u4You are now connected to database "db1" as user "u4".db1=> create table t1(id int);CREATE TABLEdb1=> \dt+                          List of relations Schema | Name | Type  | Owner | Persistence |  Size   | Description --------+------+-------+-------+-------------+---------+------------- public | t1   | table | u4    | permanent   | 0 bytes | (1 row)db1=> \c db1 u3You are now connected to database "db1" as user "u3".db1=> drop table t1;               ---虽然无权删表ERROR:  must be owner of table t1postgres=# \c postgres u3You are now connected to database "postgres" as user "u3".postgres=> drop database db1 ;  ---那我直接删库DROP DATABASE

postgres=> \c postgres postgres You are now connected to database "postgres" as user "postgres".postgres=# drop table test;DROP TABLEpostgres=# create table test(id int,info text);CREATE TABLEpostgres=# insert into test values(1,'test');INSERT 0 1postgres=# revoke select on table test from u2;REVOKEpostgres=# grant select(info) on test to u2;GRANTpostgres=# \c postgres u2You are now connected to database "postgres" as user "u2".postgres=> select * from test;ERROR:  permission denied for table testpostgres=> select info from test; info ------ test(1 row)postgres=> select id from test;ERROR:  permission denied for table test

postgres=# \h create policy Command:     CREATE POLICYDescription: define a new row level security policy for a tableSyntax:CREATE POLICY name ON table_name    [ AS { PERMISSIVE | RESTRICTIVE } ]    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]    [ USING ( using_expression ) ]    [ WITH CHECK ( check_expression ) ]URL: https://www.postgresql.org/docs/13/sql-createpolicy.html

postgres=# \z test                                Access privileges Schema | Name | Type  |     Access privileges     | Column privileges | Policies --------+------+-------+---------------------------+-------------------+---------- public | test | table | postgres=arwdDxt/postgres | info:            +|         |      |       |                           |   u2=r/postgres   | (1 row)

postgres=# update pg_database set datistemplate = 'true' where datname = 'mydb';UPDATE 1postgres=# drop database mydb;ERROR:  cannot drop a template databasepostgres=# update pg_database set datistemplate = 'false' where datname = 'mydb';UPDATE 1postgres=# drop database mydb;DROP DATABASE

CREATE OR REPLACE FUNCTION confirm_drop ()    RETURNS event_trigger    LANGUAGE plpgsql    AS $$DECLARE    obj record;BEGIN    FOR obj IN    SELECT        *    FROM        pg_event_trigger_dropped_objects ()        LOOP            IF obj.object_name = 'users' AND obj.schema_name = 'public' THEN                RAISE EXCEPTION 'command % IS disabled FOR this TABLE ', tg_tag;            END IF;        END LOOP;END;$$;CREATE EVENT TRIGGER confirm_drop on sql_drop WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE confirm_drop();postgres=# create table users(id int);CREATE TABLEpostgres=# drop table users ;ERROR:  command DROP TABLE IS disabled FOR this TABLE CONTEXT:  PL/pgSQL function confirm_drop() line 12 at RAISE

postgres=# CREATE OR REPLACE FUNCTION confirm_drop ()    RETURNS event_trigger    LANGUAGE plpgsql    AS $$DECLARE    obj record;BEGIN    FOR obj IN    SELECT        *    FROM        pg_event_trigger_dropped_objects ()        LOOP            IF  obj.schema_name = 'public' THEN                RAISE EXCEPTION 'command % IS disabled FOR this TABLE ', tg_tag;            END IF;        END LOOP;END;$$;CREATE FUNCTIONpostgres=# create table t1(id int);CREATE TABLEpostgres=# drop table t1;ERROR:  command DROP TABLE IS disabled FOR this TABLE CONTEXT:  PL/pgSQL function confirm_drop() line 12 at RAISE

create function fetg() returns event_trigger language plpgsql as $$begin  if exists (    select 1    from pg_event_trigger_dropped_objects() as t    where      t.object_type = 'table column' and      t.object_identity like any(array['%.t.y', 'public.tt.zz']))  then    raise exception 'Columns t.y and public.tt.zz are important!';  end if;end $$;create event trigger etg on sql_drop execute procedure fetg();postgres=# create table t(x int, y int);CREATE TABLEpostgres=# alter table t drop column y;ERROR:  Columns t.y and public.tt.zz are important!CONTEXT:  PL/pgSQL function fetg() line 10 at RAISE

CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text[])AS$$SELECT  $1,c.oid::regclass, array(select privs from unnest(ARRAY [ ( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys')  and c.relkind='r' andhas_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')$$ language sql;

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table,     CASE         WHEN COUNT(privilege_type) = 7 THEN 'ALL'        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')    END AS grantsFROM information_schema.role_table_grantsGROUP BY table_name, table_schema, grantee;

CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges  text[])AS$$SELECT $1, datname, array(select privs from unnest(ARRAY[( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname not in ('template0');$$ language sql;

CREATE OR REPLACE FUNCTION tablespace_privs(text) RETURNS table(username text,spcname name,privileges text[])AS$$   SELECT $1, spcname, ARRAY[(CASE WHEN has_tablespace_privilege($1,spcname,'CREATE') THEN 'CREATE' ELSE NULL END)] FROM pg_tablespace WHERE has_tablespace_privilege($1,spcname,'CREATE');$$ language sql;

CREATE OR REPLACE FUNCTION fdw_wrapper_privs(text) RETURNS table(username text,fdwname name,privleges text[])AS$$  SELECT $1, fdwname, ARRAY[(CASE WHEN has_foreign_data_wrapper_privilege($1,fdwname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_foreign_data_wrapper WHERE has_foreign_data_wrapper_privilege($1,fdwname,'USAGE');$$ language sql;

CREATE OR REPLACE FUNCTION foreign_server_privs(text) RETURNS table(username text, srvname name, privileges text[])AS$$  SELECT $1, s.srvname ,  ARRAY[(CASE WHEN has_server_privilege($1,srvname,'USAGE') THEN 'USAGE' ELSE NULL END)] from pg_catalog.pg_foreign_server s  WHERE has_server_privilege ($1,srvname,'USAGE');$$language sql;

CREATE OR REPLACE FUNCTION language_privs(text) RETURNS table(username text,srvname name, privileges text[])AS$$SELECT $1, l.lanname, ARRAY[(CASE WHEN has_language_privilege($1,lanname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_language l where has_language_privilege($1,lanname,'USAGE');$$ language sql;

CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privileges text[])AS$$  SELECT $1, c.nspname, array(select privs from unnest(ARRAY[( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE');$$ language sql;

postgres=# select    r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantablefrom pg_namespacejoin lateral (  SELECT    *  from    aclexplode(nspacl) as x) a on truejoin pg_user e on a.grantee = e.usesysidjoin pg_user r on a.grantor = r.usesysid  where e.usename = 'postgres'; grantor  | grantee  |      nspname       | privilege_type | is_grantable ----------+----------+--------------------+----------------+-------------- postgres | postgres | pg_catalog         | USAGE          | f postgres | postgres | pg_catalog         | CREATE         | f postgres | postgres | public             | USAGE          | f postgres | postgres | public             | CREATE         | f postgres | postgres | information_schema | USAGE          | f postgres | postgres | information_schema | CREATE         | f(6 rows)

CREATE OR REPLACE FUNCTION view_privs(text) returns table(username text, viewname regclass, privileges text[])AS$$SELECT  $1, c.oid::regclass, array(select privs from unnest(ARRAY [( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and  c.relkind='v' and has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')$$ language sql;

CREATE OR REPLACE FUNCTION sequence_privs(text) RETURNS table(username text, sequence regclass, privileges text[])AS$$  SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and  c.relkind='S' andhas_table_privilege($1,c.oid,'SELECT,UPDATE')  AND has_schema_privilege($1,c.relnamespace,'USAGE')$$ language sql;

postgres=# select * from (select username,'SCHEMA' as object_type,schemaname as object_name,privileges    FROM schema_privs('xiongcc') UNION ALLSELECT username,'TABLE' as object_type ,relname::name as object_name ,privs FROM table_privs('xiongcc')   UNION ALLSELECT username,'DATABASE' as object_type ,dbname as object_name ,privileges FROM database_privs('xiongcc')  UNION ALLSELECT username,'TABLESPACE' as object_type ,spcname as object_name ,privileges FROM tablespace_privs('xiongcc')  ) as text1 order by 2 ; username | object_type |    object_name     |                        privileges                         ----------+-------------+--------------------+----------------------------------------------------------- xiongcc  | DATABASE    | mydatabase1        | {CONNECT,CREATE,TEMPORARY,CONNECT} xiongcc  | DATABASE    | template1          | {CONNECT} xiongcc  | DATABASE    | benchmarksql       | {CONNECT,TEMPORARY,CONNECT} xiongcc  | DATABASE    | mydb               | {CONNECT,TEMPORARY,CONNECT} xiongcc  | DATABASE    | postgres           | {CONNECT,TEMPORARY,CONNECT} xiongcc  | SCHEMA      | pg_catalog         | {USAGE} xiongcc  | SCHEMA      | information_schema | {USAGE} xiongcc  | SCHEMA      | schema1            | {CREATE,USAGE} xiongcc  | SCHEMA      | public             | {USAGE} xiongcc  | TABLE       | test99             | {SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER}(10 rows)

postgres=# select * from ( SELECT username,'VIEW' as object_type ,viewname as object_name ,privileges FROM view_privs('xiongcc')   UNION ALLSELECT username,'SEQUENCE' as object_type ,sequence as object_name ,privileges FROM sequence_privs('xiongcc')  ) as text1 order by 2 ; username | object_type |    object_name     |                        privileges                         ----------+-------------+--------------------+----------------------------------------------------------- xiongcc  | SEQUENCE    | myseq1             | {SELECT,UPDATE} xiongcc  | VIEW        | pg_stat_statements | {SELECT} xiongcc  | VIEW        | myview1            | {SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER} xiongcc  | VIEW        | pg_show_plans      | {SELECT}(4 rows)