PostgreSQL

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

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

  1. test=# drop schema if exists public; 
  2. DROP SCHEMA

2、创建三个用户

  1. test=# create user bas encrypted password '123456';
  2. CREATE ROLE
  3. test=# create user app1 encrypted password '123456';
  4. CREATE ROLE
  5. test=# create user app3 encrypted password '123456';
  6. CREATE ROLE

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

  1. test=# create schema bas authorization bas;
  2. CREATE SCHEMA
  3. test=# create schema app1 authorization app1;
  4. CREATE SCHEMA
  5. test=# create schema app3 authorization app3;
  6. CREATE SCHEMA

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

bas用户

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

app1用户

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

app3用户

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

5、权限配置

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

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

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

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

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

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

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

  1. test=> alter default privileges in schema bas \
  2. grant select on tables to app1,app3;
  3. ALTER DEFAULT PRIVILEGES
  4. test=> alter default privileges in schema bas \
  5. grant update on tables to app1;
  6. ALTER DEFAULT PRIVILEGES

6、测试验证

app3用户测试

  1. test=> select * from bas.tbl_bas1;
  2.  id  
  3. -----
  4.  100
  5. (1 row)
  6. test=> select * from bas.tbl_bas2;
  7.  id  
  8. -----
  9.  200
  10.  300
  11. (2 rows)
  13. test=> update bas.tbl_bas1 set id=null;
  14. ERROR:  permission denied for table tbl_bas1
  15. test=> update bas.tbl_bas2 set id=null;
  16. ERROR:  permission denied for table tbl_bas2
  18. test=> delete from bas.tbl_bas1;
  19. ERROR:  permission denied for table tbl_bas1
  20. test=> delete from bas.tbl_bas2;
  21. ERROR:  permission denied for table tbl_bas2

app1用户

  1. test=> select * from bas.tbl_bas1;
  2.  id  
  3. -----
  4.  100
  5. (1 row)
  6. test=> select * from bas.tbl_bas2;
  7.  id  
  8. -----
  9.  200
  10.  300
  11. (2 rows)
  13. test=> update bas.tbl_bas1 set id=id+1;
  14. UPDATE 1
  15. test=> update bas.tbl_bas2 set id=id+1;
  16. UPDATE 2
  18. test=> delete from bas.tbl_bas1;
  19. ERROR:  permission denied for table tbl_bas1
  20. test=> delete from bas.tbl_bas2;
  21. ERROR:  permission denied for table tbl_bas2

bas用户新增表tbl_bas3

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

app1用户

  1. [postgres@ha4 ~]$ psql -p6000 -Uapp1 test
  2. test=> select * from bas.tbl_bas3;
  3.  id  
  4. -----
  5.  500
  6.  900
  7. (2 rows)
  8. test=> update bas.tbl_bas3 set id=id+1;
  9. UPDATE 2
  11. app3用户
  12. [postgres@ha4 ~]$ psql -p6000 -Uapp3 test
  13. test=> select * from bas.tbl_bas3;
  14.  id  
  15. -----
  16.  501
  17.  901
  18. (2 rows)
  19. test=> update bas.tbl_bas3 set id=id+1;
  20. ERROR:  permission denied for table tbl_bas3

第1~3步需要数据库管理用户操作，后面配置不需要。