PostgreSQL 权限信息表information_schema.table_privileges_sql

概述information_schema.table_privileges表记录着所有用户的权限信息。 postgres=# \d+ information_schema.table_privileges View "information_schema.table_privileges" Column | T

information_schema.table_privileges表记录着所有用户的权限信息。

postgres=# \d+ information_schema.table_privileges                       VIEw "information_schema.table_privileges"     Column     |               Type                | ModifIErs | Storage  | Description ----------------+-----------------------------------+-----------+----------+------------- grantor        | information_schema.sql_IDentifIEr |           | extended | 授权者 grantee        | information_schema.sql_IDentifIEr |           | extended | 被授权者 table_catalog  | information_schema.sql_IDentifIEr |           | extended | 数据库名 table_schema   | information_schema.sql_IDentifIEr |           | extended | schema名 table_name     | information_schema.sql_IDentifIEr |           | extended | 表名 privilege_type | information_schema.character_data |           | extended | 对表的 *** 作权限 is_grantable   | information_schema.yes_or_no      |           | extended |  with_hIErarchy | information_schema.yes_or_no      |           | extended | VIEw deFinition: SELECT u_grantor.rolname::information_schema.sql_IDentifIEr AS grantor,grantee.rolname::information_schema.sql_IDentifIEr AS grantee,current_database()::information_schema.sql_IDentifIEr AS table_catalog,nc.nspname::information_schema.sql_IDentifIEr AS table_schema,c.relname::information_schema.sql_IDentifIEr AS table_name,c.prtype::information_schema.character_data AS privilege_type,CASE WHEN pg_has_role(grantee.oID,c.relowner,'USAGE'::text) OR c.grantable THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS is_grantable,CASE WHEN c.prtype = 'SELECT'::text THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS with_hIErarchy FROM ( SELECT pg_class.oID,pg_class.relname,pg_class.relnamespace,pg_class.relkind,pg_class.relowner,(aclexplode(COALESCE(pg_class.relacl,acldefault('r'::"char",pg_class.relowner)))).grantor AS grantor,pg_class.relowner)))).grantee AS grantee,pg_class.relowner)))).privilege_type AS privilege_type,pg_class.relowner)))).is_grantable AS is_grantable FROM pg_class) c(oID,relname,relnamespace,relkind,relowner,grantor,grantee,prtype,grantable),pg_namespace nc,pg_authID u_grantor,( SELECT pg_authID.oID,pg_authID.rolname FROM pg_authID UNION ALL SELECT 0::oID AS oID,'PUBliC'::name) grantee(oID,rolname) WHERE c.relnamespace = nc.oID AND (c.relkind = ANY (ARRAY['r'::"char",'v'::"char"])) AND c.grantee = grantee.oID AND c.grantor = u_grantor.oID AND (c.prtype = ANY (ARRAY['INSERT'::text,'SELECT'::text,'UPDATE'::text,'DELETE'::text,'TruncATE'::text,'REFERENCES'::text,'TRIGGER'::text])) AND (pg_has_role(u_grantor.oID,'USAGE'::text) OR pg_has_role(grantee.oID,'USAGE'::text) OR grantee.rolname = 'PUBliC'::name);

现在创建一个角色并赋予SELECT权限，来观察该系统表的数据。

1. 创建角色

postgres=# create role john login NOSUPERUSER NOCREATEDB NOCREATERolE NOinherit;CREATE RolE

2. 赋予该角色一个数据库的表的SEKECT权限

-- 赋予数据库的链接权限postgres=# GRANT CONNECT ON DATABASE postgres TO john;GRANT

-- 赋予表的查询功能postgres=# GRANT SELECT ON ALL tableS IN SCHEMA public TO john;GRANT

-- tb1表的INSERT INTO权限postgres=# GRANT INSERT ON tb1 TO john;GRANT

3. 查看该角色的所有权限

postgres=# SELECT * from information_schema.table_privileges where grantee='john' order by privilege_type; grantor  | grantee | table_catalog | table_schema |      table_name      | privilege_type | is_grantable | with_hIErarchy ----------+---------+---------------+--------------+----------------------+----------------+--------------+---------------- postgres | john    | postgres      | public       | tb1                  | INSERT | NO | NO postgres | john | postgres | public | book | SELECT | NO | YES postgres | john | postgres | public | weather | SELECT | NO | YES postgres | john | postgres | public | citIEs | SELECT | NO | YES postgres | john | postgres | public | tb1 | SELECT | NO | YES postgres | john | postgres | public | book2 | SELECT | NO | YES postgres | john | postgres | public | person | SELECT | NO | YES postgres | john | postgres | public | tb2 | SELECT | NO | YES postgres | john | postgres | public | orders | SELECT | NO | YES postgres | john | postgres | public | test_unlogged | SELECT | NO | YES postgres | john | postgres | public | test | SELECT | NO | YES postgres | john | postgres | public | system_monitor | SELECT | NO | YES postgres | john | postgres | public | tb3 | SELECT | NO | YES postgres | john | postgres | public | pg_stat_statements | SELECT | NO | YES postgres | john | postgres | public | vIEw_business_device | SELECT | NO | YES postgres | john | postgres | public | student | SELECT | NO | YES postgres | john | postgres | public | pgbench_tellers | SELECT | NO | YES postgres | john | postgres | public | pgbench_branches | SELECT | NO | YES postgres | john | postgres | public | pgbench_accounts | SELECT | NO | YES postgres | john | postgres | public | pgbench_history | SELECT | NO | YES postgres | john | postgres | public | goods | SELECT | NO | YES postgres | john | postgres | public | bloat | SELECT | NO | YES (22 rows)