PG使用默认权限访问其它schema数据示例
通常在业务系统中,经常会出现不同系统间,不同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的方式:
- 连接或切换到相应DB中去访问数据
- 通过插件dblink或fdw 来访问 。
下图简单说明了PG里 user,db,sechma,table之间的关系,若有不妥之处欢迎大家指证交流。