SRE文档

深浅模式

业务场景：
三个独自子系统bas、app1、app3
bas系统的数据为app1和app3系统所共有的基础数据
app1可修改bas系统数据，但不能删除
app3只能查询bas系统数据，不能修改和删除
需求：只需配置一次，后续新增表无需再次配置
业务场景：
三个独自子系统bas、app1、app3
bas系统的数据为app1和app3系统所共有的基础数据
app1可修改bas系统数据，但不能删除
app3只能查询bas系统数据，不能修改和删除
需求：只需配置一次，后续新增表无需再次配置

1. 删除public模式，各自使用私有模式管理数据 ​

test=# drop schema if exists public; 
DROP SCHEMA
test=# drop schema if exists public; 
DROP SCHEMA

2. 创建三个用户 ​

test=# create user bas encrypted password '123456';
CREATE ROLE
test=# create user app1 encrypted password '123456';
CREATE ROLE
test=# create user app3 encrypted password '123456';
CREATE ROLE
test=# create user bas encrypted password '123456';
CREATE ROLE
test=# create user app1 encrypted password '123456';
CREATE ROLE
test=# create user app3 encrypted password '123456';
CREATE ROLE

3. 创建三个用户对应的schema并各自关联 ​

test=# create schema bas authorization bas;
CREATE SCHEMA
test=# create schema app1 authorization app1;
CREATE SCHEMA
test=# create schema app3 authorization app3;
CREATE SCHEMA
test=# create schema bas authorization bas;
CREATE SCHEMA
test=# create schema app1 authorization app1;
CREATE SCHEMA
test=# create schema app3 authorization app3;
CREATE SCHEMA

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

bas用户

[postgres@ha4 ~]$ psql -p6000 -Ubas test
test=> create table tbl_bas1(id int);
CREATE TABLE
test=> create table tbl_bas2(id int);
CREATE TABLE
test=> insert into tbl_bas1 values(100);
INSERT 0 1
test=> insert into tbl_bas2 values(200),(300);
INSERT 0 2
[postgres@ha4 ~]$ psql -p6000 -Ubas test
test=> create table tbl_bas1(id int);
CREATE TABLE
test=> create table tbl_bas2(id int);
CREATE TABLE
test=> insert into tbl_bas1 values(100);
INSERT 0 1
test=> insert into tbl_bas2 values(200),(300);
INSERT 0 2

app1用户

[postgres@ha4 ~]$ psql -p6000 -Uapp1 test
test=> create table tbl_app1(id int);
CREATE TABLE
[postgres@ha4 ~]$ psql -p6000 -Uapp1 test
test=> create table tbl_app1(id int);
CREATE TABLE

app3用户

[postgres@ha4 ~]$ psql -p6000 -Uapp3 test
test=> create table tbl_app3(id int);
CREATE TABLE
[postgres@ha4 ~]$ psql -p6000 -Uapp3 test
test=> create table tbl_app3(id int);
CREATE TABLE

5. 权限配置 ​

使用bas用户配置schema的usage权限给app1和app3用户

test=> grant usage on schema bas to app1,app3;
GRANT
test=> grant usage on schema bas to app1,app3;
GRANT

使用bas用户配置当前所有表的select权限

test=> grant select on all tables in schema bas to app1,app3;
GRANT
test=> grant select on all tables in schema bas to app1,app3;
GRANT

使用bas用户配置当前所有表的update权限

test=> grant update on all tables in schema bas to app1;
GRANT
test=> grant update on all tables in schema bas to app1;
GRANT

使用bas用户配置新增表的默认权限

test=> alter default privileges in schema bas \
grant select on tables to app1,app3;
ALTER DEFAULT PRIVILEGES
test=> alter default privileges in schema bas \
grant update on tables to app1;
ALTER DEFAULT PRIVILEGES
test=> alter default privileges in schema bas \
grant select on tables to app1,app3;
ALTER DEFAULT PRIVILEGES
test=> alter default privileges in schema bas \
grant update on tables to app1;
ALTER DEFAULT PRIVILEGES

6. 测试验证 ​

app3用户测试

test=> select * from bas.tbl_bas1;
 id  
-----
 100
(1 row)
test=> select * from bas.tbl_bas2;
 id  
-----
 200
 300
(2 rows)

test=> update bas.tbl_bas1 set id=null;
ERROR:  permission denied for table tbl_bas1
test=> update bas.tbl_bas2 set id=null;
ERROR:  permission denied for table tbl_bas2

test=> delete from bas.tbl_bas1;
ERROR:  permission denied for table tbl_bas1
test=> delete from bas.tbl_bas2;
ERROR:  permission denied for table tbl_bas2
test=> select * from bas.tbl_bas1;
 id  
-----
 100
(1 row)
test=> select * from bas.tbl_bas2;
 id  
-----
 200
 300
(2 rows)

test=> update bas.tbl_bas1 set id=null;
ERROR:  permission denied for table tbl_bas1
test=> update bas.tbl_bas2 set id=null;
ERROR:  permission denied for table tbl_bas2

test=> delete from bas.tbl_bas1;
ERROR:  permission denied for table tbl_bas1
test=> delete from bas.tbl_bas2;
ERROR:  permission denied for table tbl_bas2

app1用户

test=> select * from bas.tbl_bas1;
 id  
-----
 100
(1 row)
test=> select * from bas.tbl_bas2;
 id  
-----
 200
 300
(2 rows)

test=> update bas.tbl_bas1 set id=id+1;
UPDATE 1
test=> update bas.tbl_bas2 set id=id+1;
UPDATE 2

test=> delete from bas.tbl_bas1;
ERROR:  permission denied for table tbl_bas1
test=> delete from bas.tbl_bas2;
ERROR:  permission denied for table tbl_bas2
test=> select * from bas.tbl_bas1;
 id  
-----
 100
(1 row)
test=> select * from bas.tbl_bas2;
 id  
-----
 200
 300
(2 rows)

test=> update bas.tbl_bas1 set id=id+1;
UPDATE 1
test=> update bas.tbl_bas2 set id=id+1;
UPDATE 2

test=> delete from bas.tbl_bas1;
ERROR:  permission denied for table tbl_bas1
test=> delete from bas.tbl_bas2;
ERROR:  permission denied for table tbl_bas2

bas用户新增表tbl_bas3

[postgres@ha4 ~]$ psql -p6000 -Ubas test
test=> create table tbl_bas3(id int);
CREATE TABLE
test=> insert into tbl_bas3 values(500),(900);
INSERT 0 2
[postgres@ha4 ~]$ psql -p6000 -Ubas test
test=> create table tbl_bas3(id int);
CREATE TABLE
test=> insert into tbl_bas3 values(500),(900);
INSERT 0 2

app1用户

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

app3用户
[postgres@ha4 ~]$ psql -p6000 -Uapp3 test
test=> select * from bas.tbl_bas3;
 id  
-----
 501
 901
(2 rows)
test=> update bas.tbl_bas3 set id=id+1;
ERROR:  permission denied for table tbl_bas3
[postgres@ha4 ~]$ psql -p6000 -Uapp1 test
test=> select * from bas.tbl_bas3;
 id  
-----
 500
 900
(2 rows)
test=> update bas.tbl_bas3 set id=id+1;
UPDATE 2

app3用户
[postgres@ha4 ~]$ psql -p6000 -Uapp3 test
test=> select * from bas.tbl_bas3;
 id  
-----
 501
 901
(2 rows)
test=> update bas.tbl_bas3 set id=id+1;
ERROR:  permission denied for table tbl_bas3