云贝数据 | PG使用默认权限访问其它schema数据示例

作者：Asher.hu

原文链接：https://pgfans.cn/a/1734

【云贝学院】PostgreSQL认证课程已上线，学院有不定期公开课 需要的同学可以加老师微信：19941464235

通常在业务系统中，经常会出现不同系统间，不同schema间的数据相互访问。下面模拟在同一个DB下，不同schema 间的数据访问验证。

三个独自子系统asher、app1、app2 ：

• asher系统的数据为app1和app2系统所共有的基础数据
• app1可修改asher系统数据，但不能删除
• app2只能查询asher系统数据，不能修改和删除

要求：后续asher新增表无需再次配置,app1,app2仍然有权限访问。

1.删除public模式

# 删除public模式，各自使用私有模式管理数据(需用管理用户操作)
postgres@s2ahumysqlpg01-> psql
psql (12.4)
Type 'help' for help.
postgres=# drop database testdb ;
postgres=# create database testdb ;
CREATE DATABASE
\c testdb 
testdb=# drop schema if exists public; 
DROP SCHEMA           

2.创建三个用户

#创建三个用户(需用管理用户操作)      
testdb=# create user asher encrypted password '123456';
testdb=# create user app1  encrypted password '123456';
testdb=# create user app2  encrypted password '123456';           

3.创建sehema

# 创建三个用户对应的schema并各自关联(需用管理用户操作)
testdb=# create schema asher authorization asher; 
testdb=# create schema app1 authorization app1;
testdb=# create schema app2 authorization app2;           

4.三个用户各自创建测试表

4.1asher用户

postgres@s2ahumysqlpg01-> psql -Uasher testdb
Password for user asher: 
psql (12.4)
Type 'help' for help.

testdb=> 
create table tbl_asher1(id int);
create table tbl_asher2(id int);

insert into tbl_asher1 values(100);
insert into tbl_asher2 values(200),(300);

testdb=> \d
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 asher  | tbl_asher1 | table | asher
 asher  | tbl_asher2 | table | asher
(2 rows)           

4.2app1用户

postgres@s2ahumysqlpg01-> psql -Uapp1 testdb
Password for user app1: 
psql (12.4)
Type 'help' for help.

testdb=> \d
Did not find any relations.
testdb=> create table tbl_app1(id int);
CREATE TABLE
testdb=> \d
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 app1   | tbl_app1 | table | app1
(1 row)           

4.3app2用户

postgres@s2ahumysqlpg01-> psql -Uapp2 testdb
Password for user app2: 
psql (12.4)
Type 'help' for help.

testdb=> create table tbl_app2(id int);
CREATE TABLE
testdb=> \d
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 app2   | tbl_app2 | table | app2
(1 row)           

5.权限配置

5.1授予普通权限

使用asher用户配置schema的usage权限给app1和app2用户
grant usage on schema asher to app1,app2;


使用asher用户配置当前所有表的select权限
grant select on all tables in schema asher to app1,app2;


使用asher用户配置当前所有表的update权限
grant update on all tables in schema asher to app1;           

5.2配置默认权限

# 使用asher用户配置新增表的默认权限
alter default privileges in schema asher          grant select on tables to app1,app2;

alter default privileges in schema asher          grant update on tables to app1;           

6.测试验证

6.1app1用户

postgres@s2ahumysqlpg01-> psql -Uapp1 testdb
Password for user app1: 
psql (12.4)
Type 'help' for help.

testdb=> select * from asher.tbl_asher1;
 id  
-----
 100
(1 row)

testdb=> select * from asher.tbl_asher2;
 id  
-----
 200
 300
(2 rows)
# 更新测试
testdb=> update asher.tbl_asher1 set id=id+1;
UPDATE 1
testdb=> update asher.tbl_asher2 set id=id+1;
UPDATE 2
testdb=> 
# 删除测试
testdb=> delete from asher.tbl_asher1;
ERROR:  permission denied for table tbl_asher1
testdb=> delete from asher.tbl_asher2;
ERROR:  permission denied for table tbl_asher2           

6.2app2用户测试

postgres@s2ahumysqlpg01-> psql -Uapp1 testdb
Password for user app1: 
psql (12.4)
Type 'help' for help.

testdb=> \d
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 app2   | tbl_app2 | table | app2
(1 row)

testdb=> select * from asher.tbl_asher1;
 id  
-----
 100
(1 row)

testdb=> select * from asher.tbl_asher2;
 id  
-----
 200
 300
(2 rows)

更新测试：
testdb=> update asher.tbl_asher1 set id=null;
ERROR:  permission denied for table tbl_asher1

testdb=> update asher.tbl_asher2 set id=null;
ERROR:  permission denied for table tbl_asher2

删除测试：
testdb=> delete from asher.tbl_asher1;
ERROR:  permission denied for table tbl_asher1
testdb=> delete from asher.tbl_asher2;
ERROR:  permission denied for table tbl_asher2
testdb=>           

6.3 asher用户新增表tbl_asher3

postgres@s2ahumysqlpg01-> psql -Uasher testdb
Password for user asher: 
psql (12.4)
Type 'help' for help.

testdb=>  create table tbl_asher3(id int);
CREATE TABLE
testdb=> insert into tbl_asher3 values(500),(900);
INSERT 0 2




# app1用户验证新表的权限

[postgres@ha4 ~]$ psql -p6000 -Uapp1 test
test=> select * from asher.tbl_asher3;
 id  
-----
 500
 900
(2 rows)
test=> update asher.tbl_asher3 set id=id+1;
UPDATE 2


# app2用户验证新表的权限
[postgres@ha4 ~]$ psql -p6000 -Uapp2 test
test=> select * from asher.tbl_asher3;
 id  
-----
 501
 901
(2 rows)
test=> update asher.tbl_asher3 set id=id+1;
ERROR:  permission denied for table tbl_asher3           

7.小结

通过上面测试，我们满足了在同一个DB里，不同schema ，通过授权相互访问的需求。asher新增表无需再次配置,app1,app2仍然有权限访问。

但是在不同DB之间，数据是相互隔离的，每个DB都可以相同名（但不是同一个）的schema 。这就导至了，如果在访问其它DB里面的数据。就必须切换到相应DB里去访问 。就算postgres用户也不例表。pg中并没有 dbname..schemaname..tbl_name 访问的方式。 但是提供了dblink或fdw的方式(见下一节)。

所以，如果在PG中想访问不同DB的方式：

1. 连接或切换到相应DB中去访问数据

2. 通过插件dblink或fdw 来访问 。

   下图简单说明了PG里 user,db,sechma,table之间的关系，若有不妥之处欢迎大家指证交流。

*禁止转载，可转发（转发文章请注明出处）