【案例】PostgreSQL表权限授予情况查询

问题背景

用户反馈user2和read_only用户都能访问user1模式下的test_t表，但是系统表information_schema.table_privileges里面只有一条授权记录。

本地模拟和分析这个场景。

问题分析

创建测试数据

-- 创建用户
create user read_only password 'test';
create user user1 password 'user1';
create user user2 password 'user2';
-- 创建模式
create schema authorization user1;
create schema authorization user2;
-- 创建测试表
psql -U user1
create table test_t(id int);
-- 授权read_only,user2用户访问user1模式下的test_t表
psql -U user1
grant usage on schema user1 to read_only,user2;
grant select on user1.test_t to read_only,user2;

超级管理员查询表授权情况

[postgres@centos7 ~]$ psql -c "select * from information_schema.table_privileges where table_name='test_t' and grantee in ('user2','read_only')"
 grantor |  grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+-----------+---------------+--------------+------------+----------------+--------------+----------------
 user1   | read_only | postgres      | user1        | test_t     | SELECT         | NO           | YES
 user1   | user2     | postgres      | user1        | test_t     | SELECT         | NO           | YES
(2 rows)

user2用户连接查询表授权情况

[postgres@centos7 ~]$ psql -U user2 -c "select * from information_schema.table_privileges where table_name='test_t'"
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 user1   | user2   | postgres      | user1        | test_t     | SELECT         | NO           | YES
(1 row)

read_only用户连接查询表授权情况

[postgres@centos7 ~]$ psql -U read_only -c "select * from information_schema.table_privileges where table_name='test_t'" grantor |  grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+-----------+---------------+--------------+------------+----------------+--------------+----------------
 user1   | read_only | postgres      | user1        | test_t     | SELECT         | NO           | YES
(1 row)

如上可以看出普通用户连接查询到的information_schema.table_privileges里的信息只和用户自身相关。

普通用户想要查看表完整的授权情况，可以使用pg_class视图查询，如下：

[postgres@centos7 ~]$ psql -U user2 -c "select relnamespace::regnamespace as schema,relname as tablename,relowner::regrole as owner,relacl from pg_class where relname = 'test_t';"
 schema | tablename | owner |                        relacl                         
--------+-----------+-------+-------------------------------------------------------
 user1  | test_t    | user1 | {user1=arwdDxt/user1,read_only=r/user1,user2=r/user1}
(1 row)

[postgres@centos7 ~]$ psql -U read_only -c "select relnamespace::regnamespace as schema,relname as tablename,relowner::regrole as owner,relacl from pg_class where relname = 'test_t';"
 schema | tablename | owner |                        relacl                         
--------+-----------+-------+-------------------------------------------------------
 user1  | test_t    | user1 | {user1=arwdDxt/user1,read_only=r/user1,user2=r/user1}
(1 row)

如上read_only=r/user1表示user1用户授权read_only用户表的r权限（select），user2=r/user1表示user1用户授权user2用户表的r权限（select）

ACL权限缩写说明，参考官方文档：http://postgres.cn/docs/12/ddl-priv.html

总结

表授权情况可以使用以下两个视图查询

• information_schema.table_privileges.privilege_type

  普通用户（grantee）只能查询到和自己相关的授权情况

• pg_catalog.pg_class.relacl

  普通用户也能把表的授权情况查全，但是需要解读ACL权限点。