postgres授权管理实现

使用工具 DataGrip

Postgres 版本 PostgreSQL 14.1

需求:

在test_db数据下有schema s1、s2 并创建两个用户 u1、u2

1)授权用户u1对s1具有只读权限对s2具有读写创建权限

2)授权用户u2对s1具有读写创建权限对s2具有读写创建权限

1.创建测试所用数据库(owner用户)

        create database test_db;

2.创建schema(owner用户)

        create schema s1;

        create schema s2;

3.创建role角色(owner用户)

3.1)创建对schema s1的只读角色

        create role s1_rr;

3.2)创建对schema s1的读写角色

        create role s1_rw;

3.3)创建对schema s2的读写角色

        create role s2_rw;

4.授予角色s1_rr对schema s1权限(owner用户)

4.1)授予角色s1_rr对schema s1的访问权限

        grant usage on schema s1 to s1_rr;

4.2)授予角色s1_rr对schema s1的读权限

        grant select on all tbales in schema s1 to s1_rr;

4.3)设置schema s1之后新建的表自动延续权限

        alter default privileges in schema s1 grant select on tables to s1_rr;

5.授予角色s1_rw对schema s1权限(owner用户)

5.1)授予角色s1_rw对schema s1的访问权限

        grant usage on schema s1 to s1_rw;

5.2)授予角色s1_rw对schema s1的读写权限

        grant select,insert,update,delete on all tbales in schema s1 to s1_rw;

 5.3)授予角色s1_rw对schema s2的表操作权限(owner用户建表删除不了,反之能删除)

        gran create on schema s1 to s1_rw;

5.3)设置schema s1之后新建的表自动延续权限

        alter default privileges in schema s1 grant select,insert,update,delete on tables to s1_rw;

6.授予角色s2_rw对schema s2权限(owner用户)

6.1)授予角色s2_rw对schema s2的访问权限

        grant usage on schema s2 to s2_rw;

6.2)授予角色s2_rw对schema s2的读写权限

        grant select,insert,update,delete on all tbales in schema s2 to s2_rw;

 6.3)授予角色s2_rw对schema s2的表操作权限(owner用户建表删除不了,反之能删除)

        gran create on schema s2 to s2_rw;

6.3)设置schema s2之后新建的表自动延续权限

        alter default privileges in schema s2 grant select,insert,update,delete on tables to s2_rw;

7.新建用户授予角色(owner用户)

7.1)新建u1\u2用户

        create user u1 with password '....';

        create user u2 with password '....'

7.2)授予u1对s1的读s2的读写权限

        grant s1_rr to u1;

        grant s2_rw to u1;

7.3)授予u2对s1\s2的读写权限

        grant s1_rw to u2;

        grant s2_rw to u2;

8.其他注意事项(根据业务情况设定)

8.1)回收所有用户关于 schema public 的权限

        revoke all privileges on schema public from PUBLIC;

8.2)不同数据库授权 owern账号切换到当前数据库将schema授权给指定角色

8.3)回收链接postgres库的所有账号的权限（除去owner）

        revoke connect on database postgres from public;