【案例】PostgreSQL视图权限问题

PostgreSQL视图权限问题

问题背景

用户发现在对视图进行查询的时候报错，提示没有视图基表的权限。

检查权限都是正常的，用户对视图和视图的基表都有select权限，对视图和视图的基表所在的模式也有usage权限。

本次模拟和分析该问题。

创建测试数据

create database testdb;
create user test PASSWORD 'test';
create user user1 PASSWORD 'user1';
create user user2 PASSWORD 'user2';
grant all privileges on database testdb to test,user1,user2;

\c testdb
CREATE SCHEMA AUTHORIZATION user1;
CREATE SCHEMA AUTHORIZATION user2;

\c testdb user1
create table test_t1(id int);
create table test_t2(id int);
create view test_v1 as select * from test_t1;
create view test_v2 as select * from test_t1 join test_t2 using(id);

授权用户user2对user1模式下视图和表的查询权限

\c testdb user1
grant usage on schema user1 to user2;
grant select on all tables in schema user1 to user2;
alter default privileges in schema user1 grant select on tables to user2;

此时使用user2连接testdb库可以正常查询user1模式下的对象

[postgres@centos7 ~]$ psql -U user2 -d testdb -c "select * from user1.test_t1"
 id
----
(0 rows)

[postgres@centos7 ~]$ psql -U user2 -d testdb -c "select * from user1.test_t2"
 id
----
(0 rows)

[postgres@centos7 ~]$ psql -U user2 -d testdb -c "select * from user1.test_v1"
 id
----
(0 rows)

[postgres@centos7 ~]$ psql -U user2 -d testdb -c "select * from user1.test_v2"
 id
----
(0 rows)

问题模拟

模拟

如下user1模式下创建的表和视图的owner都为user1

[postgres@centos7 ~]$ psql -U user1 -d testdb -c "\d"
        List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+-------
 user1  | test_t1 | table | user1
 user1  | test_t2 | table | user1
 user1  | test_v1 | view  | user1
 user1  | test_v2 | view  | user1
(4 rows)

此时的授权情况

#模式权限
[postgres@centos7 ~]$ psql -U user1 -d testdb -c "\dn+ user1"
                 List of schemas
 Name  | Owner | Access privileges | Description
-------+-------+-------------------+-------------
 user1 | user1 | user1=UC/user1   +|
       |       | user2=U/user1     |
(1 row)

[postgres@centos7 ~]$

#user1模式下的表和视图的acl权限
[postgres@centos7 ~]$ psql -U user1 -d testdb -c "select relnamespace::regnamespace,relname,relowner::regrole,relacl from pg_class where relnamespace::regnamespace::text='user1';"
 relnamespace | relname | relowner |               relacl
--------------+---------+----------+-------------------------------------
 user1        | test_t1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_t2 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_v1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_v2 | user1    | {user1=arwdDxt/user1,user2=r/user1}
(4 rows)

[postgres@centos7 ~]$

如上可以看出user2有test_t1、test_t2、test_v1、test_v2这些的select权限（r）权限是user1授予的

现在修改test_t2表的属主

psql -U user1 -d testdb -c "grant create,usage on schema user1 to test;"
psql -U postgres -d testdb -c "alter table user1.test_t2 owner to test"

检查现在user1模式下对象的acl权限

[postgres@centos7 ~]$ psql -U user1 -d testdb -c "select relnamespace::regnamespace,relname,relowner::regrole,relacl from pg_class where relnamespace::regnamespace::text='user1';"
 relnamespace | relname | relowner |               relacl
--------------+---------+----------+-------------------------------------
 user1        | test_t1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_v1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_v2 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_t2 | test     | {test=arwdDxt/test,user2=r/test}
(4 rows)

[postgres@centos7 ~]$

如上可以看出test_t2表的属主变了，同时acl权限也自动变了

#属主是user1时test_t2表的acl权限
{user1=arwdDxt/user1,user2=r/user1
#属主是test时test_t2表的acl权限
{test=arwdDxt/test,user2=r/test}

从当前的acl权限可以看出，user2对user1模式下的test_v2视图有select权限，对视图涉及基表test_t1有select权限、对视图涉及基表test_t2也有select权限。

此时user2连接查询视图test_v2

[postgres@centos7 ~]$ psql -U user2 -d testdb -c "select * from user1.test_v2"
ERROR:  permission denied for table test_t2
[postgres@centos7 ~]$

如上这个报错就很奇怪，从上面的acl可以看出，user2是有test_t2的查询权限的

处理

我们再试试user1查询test_v2

[postgres@centos7 ~]$ psql -U user1 -d testdb -c "select * from user1.test_v2"
ERROR:  permission denied for table test_t2
[postgres@centos7 ~]$

如上也报错，从上面的acl权限可以看出user1这个报错是正常的，因为属主修改后user1没有test_t2表的查询权限。

给user1授权test_t2的查询权限

[postgres@centos7 ~]$ psql -U test -d testdb -c "grant select on user1.test_t2 to user1;"
GRANT
[postgres@centos7 ~]$

此时的acl权限

[postgres@centos7 ~]$ psql -U user1 -d testdb -c "select relnamespace::regnamespace,relname,relowner::regrole,relacl from pg_class where relnamespace::regnamespace::text='user1';"
 relnamespace | relname | relowner |                    relacl
--------------+---------+----------+-----------------------------------------------
 user1        | test_t1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_v1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_v2 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_t2 | test     | {test=arwdDxt/test,user2=r/test,user1=r/test}
(4 rows)

[postgres@centos7 ~]$

此时user1查询test_v2视图

[postgres@centos7 ~]$ psql -U user1 -d testdb -c "select * from user1.test_v2"
 id
----
(0 rows)

[postgres@centos7 ~]$

再使用user2查询test_v2视图

[postgres@centos7 ~]$ psql -U user2 -d testdb -c "select * from user1.test_v2"
 id
----
(0 rows)

[postgres@centos7 ~]$

注意：此时user2的权限也正常了。

此时回收掉user2用户对user1.test_v2的select权限。

[postgres@centos7 ~]$ psql -U postgres -d testdb -c "revoke select on user1.test_v2 from user2"
REVOKE

[postgres@centos7 ~]$ psql -U user1 -d testdb -c "select relnamespace::regnamespace,relname,relowner::regrole,relacl from pg_class where relnamespace::regnamespace::text='user1';"
 relnamespace | relname | relowner |                    relacl
--------------+---------+----------+-----------------------------------------------
 user1        | test_t1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_v1 | user1    | {user1=arwdDxt/user1,user2=r/user1}
 user1        | test_t2 | test     | {test=arwdDxt/test,user2=r/test,user1=r/test}
 user1        | test_v2 | user1    | {user1=arwdDxt/user1}
(4 rows)

[postgres@centos7 ~]$
[postgres@centos7 ~]$ psql -U user2 -d testdb -c "select * from user1.test_v2"
ERROR:  permission denied for view test_v2
[postgres@centos7 ~]$

说明：查询视图需要注意三个权限点：

• 查询用户对视图所在的模式具有usage权限
• 查询用户对视图具有select权限
• 视图的owner对视图的基表具有select权限（涉及多张基表必须都有select权限）

当前案例，没有满足第三点（修改属主后视图test_v2的属主user1对基表test_t2没有查询权限），所以报错。

总结

查询视图需要注意三个权限点：

• 查询用户对视图所在的模式具有usage权限
• 查询用户对视图具有select权限
• 视图的owner对视图的基表具有select权限（涉及多张基表必须都有select权限）