作者：瀚高PG实验室 （Highgo PG Lab）- 禹晓
在实际的生产环境 ，我们经常会碰到这样的情况：因为业务场景需要，本部门某些重要的业务数据表需要给予其他部门查看权限，因业务的扩展及调整，后期可能需要放开更多的表查询权限。为解决此种业务需求，我们可以采用创建视图的方式来解决，已可以通过创建影子用户的方式来满足需求，本文主要介绍影子用户的创建及授权方法。
场景1：只授予usage on schema 权限
session 1:
--创建readonly用户，并将test模式赋予readonly用户。
-
postgres=
# create user readonly with password 'postgres';
-
CREATE
ROLE
-
postgres=
# grant usage on schema test to readonly;
-
GRANT
-
postgres=
# \dn
-
List
of schemas
-
Name | Owner
-
-------+-------
-
test | postgres
session 2:
--登陆readonly用户可以查询test模式下现存的所有表。
-
postgres=#
\c
postgres
readonly
-
You
are
now
connected
to
database
"postgres"
as
user
"readonly"
.
-
postgres=>
select
*
from
test.emp
;
-
empno
|
ename
|
job
|
mgr
|
hiredate
|
sal
|
comm
|
deptno
-
-------+--------+-----------+------+------------+---------+---------+--------
-
7499
|
ALLEN
|
SALESMAN
|
7698
|
1981-02-20
|
1600.00
|
300.00
|
30
-
7521
|
WARD
|
SALESMAN
|
7698
|
1981-02-22
|
1250.00
|
500.00
|
30
-
7566
|
JONES
|
MANAGER
|
7839
|
1981-04-02
|
2975.00
|
|
20
-
7654
|
MARTIN
|
SALESMAN
|
7698
|
1981-09-28
|
1250.00
|
1400.00
|
30
-
7698
|
BLAKE
|
MANAGER
|
7839
|
1981-05-01
|
2850.00
|
|
30
-
7782
|
CLARK
|
MANAGER
|
7839
|
1981-06-09
|
2450.00
|
|
10
-
7839
|
KING
|
PRESIDENT
|
|
1981-11-17
|
5000.00
|
|
10
-
7844
|
TURNER
|
SALESMAN
|
7698
|
1981-09-08
|
1500.00
|
0.00
|
30
-
7900
|
JAMES
|
CLERK
|
7698
|
1981-12-03
|
950.00
|
|
30
-
7902
|
FORD
|
ANALYST
|
7566
|
1981-12-03
|
3000.00
|
|
20
-
7934
|
MILLER
|
CLERK
|
7782
|
1982-01-23
|
1300.00
|
|
10
-
7788
|
test
|
ANALYST
|
7566
|
1982-12-09
|
3000.00
|
|
20
-
7876
|
ADAMS
|
CLERK
|
7788
|
1983-01-12
|
1100.00
|
|
20
-
1111
|
SMITH
|
CLERK
|
7902
|
1980-12-17
|
800.00
|
|
20
-
(14
rows)
换到session 1创建新表t1
-
postgres=
# create table test.t1 as select * from test.emp;
-
CREATE
TABLE
切换到session 2 readonly用户下，t1表无法查询
-
postgres=> select * from test.t
1 ;
-
2021-
03-
02
15:
25:
33.
290 CST
[21059] ERROR: permission denied for table t1
-
2021-03-02 15:25:33.290 CST [21059] STATEMENT: select * from test.t1 ;
-
**ERROR: permission denied for table t1
结论：如果只授予 usage on schema 权限，readonly 只能查看 test 模式下已经存在的表和对象。在授予 usage on schema 权限之后创建的新表无法查看。
场景2：授予usage on schema 权限之后，再赋予 select on all tables in schema 权限
针对上个场景session 2 **ERROR: permission denied for table t1 错误的处理
-
postgres=>
select * from test.t1 ;
-
**
ERROR: permission denied
for table t1
session 1： 使用postgres用户授予readonly用户 select on all tables 权限
postgres=# grant select on all tables in schema test TO readonly ;session 2: readonly用户查询 t1 表
-
postgres=>
select
*
from
test.t1;
-
empno
|
ename
|
job
|
mgr
|
hiredate
|
sal
|
comm
|
deptno
-
-------+--------+-----------+------+------------+---------+---------+--------
-
7499
|
ALLEN
|
SALESMAN
|
7698
|
1981-02-20
|
1600.00
|
300.00
|
30
-
7521
|
WARD
|
SALESMAN
|
7698
|
1981-02-22
|
1250.00
|
500.00
|
30
-
7566
|
JONES
|
MANAGER
|
7839
|
1981-04-02
|
2975.00
|
|
20
-
7654
|
MARTIN
|
SALESMAN
|
7698
|
1981-09-28
|
1250.00
|
1400.00
|
30
-
7698
|
BLAKE
|
MANAGER
|
7839
|
1981-05-01
|
2850.00
|
|
30
-
7782
|
CLARK
|
MANAGER
|
7839
|
1981-06-09
|
2450.00
|
|
10
-
7839
|
KING
|
PRESIDENT
|
|
1981-11-17
|
5000.00
|
|
10
-
7844
|
TURNER
|
SALESMAN
|
7698
|
1981-09-08
|
1500.00
|
0.00
|
30
-
7900
|
JAMES
|
CLERK
|
7698
|
1981-12-03
|
950.00
|
|
30
-
7902
|
FORD
|
ANALYST
|
7566
|
1981-12-03
|
3000.00
|
|
20
-
7934
|
MILLER
|
CLERK
|
7782
|
1982-01-23
|
1300.00
|
|
10
-
7788
|
test
|
ANALYST
|
7566
|
1982-12-09
|
3000.00
|
|
20
-
7876
|
ADAMS
|
CLERK
|
7788
|
1983-01-12
|
1100.00
|
|
20
-
1111
|
SMITH
|
CLERK
|
7902
|
1980-12-17
|
800.00
|
|
20
-
(14
rows)
session1 ：postgres用户的test模式下创建新表 t2
-
postgres=#
create
table test.t2 as
select * from test.emp;
-
SELECT
14
session 2：readonly用户查询 t2 表权限不足
-
postgres=>
select * from test.t2 ;
-
ERROR: permission denied
for table t2
session 1：再次赋予 grant select on all tables
postgres=# grant select on all tables in schema test TO readonly ;session 2：readonly用户又可以查看 T2 表
-
postgres=>
select
*
from
test.t2
;
-
empno
|
ename
|
job
|
mgr
|
hiredate
|
sal
|
comm
|
deptno
-
-------+--------+-----------+------+------------+---------+---------+--------
-
7499
|
ALLEN
|
SALESMAN
|
7698
|
1981-02-20
|
1600.00
|
300.00
|
30
-
7521
|
WARD
|
SALESMAN
|
7698
|
1981-02-22
|
1250.00
|
500.00
|
30
-
7566
|
JONES
|
MANAGER
|
7839
|
1981-04-02
|
2975.00
|
|
20
-
7654
|
MARTIN
|
SALESMAN
|
7698
|
1981-09-28
|
1250.00
|
1400.00
|
30
-
7698
|
BLAKE
|
MANAGER
|
7839
|
1981-05-01
|
2850.00
|
|
30
-
7782
|
CLARK
|
MANAGER
|
7839
|
1981-06-09
|
2450.00
|
|
10
-
7839
|
KING
|
PRESIDENT
|
|
1981-11-17
|
5000.00
|
|
10
-
7844
|
TURNER
|
SALESMAN
|
7698
|
1981-09-08
|
1500.00
|
0.00
|
30
-
7900
|
JAMES
|
CLERK
|
7698
|
1981-12-03
|
950.00
|
|
30
-
7902
|
FORD
|
ANALYST
|
7566
|
1981-12-03
|
3000.00
|
|
20
-
7934
|
MILLER
|
CLERK
|
7782
|
1982-01-23
|
1300.00
|
|
10
-
7788
|
test
|
ANALYST
|
7566
|
1982-12-09
|
3000.00
|
|
20
-
7876
|
ADAMS
|
CLERK
|
7788
|
1983-01-12
|
1100.00
|
|
20
-
1111
|
SMITH
|
CLERK
|
7902
|
1980-12-17
|
800.00
|
|
20
-
(14
rows)
影子用户创建
如果想让readonly只读用户不在每次 postgres用户在test模式中创建新表后都要手工赋予 grant select on all tables in schema test TO readonly 权限。则需要授予对test默认的访问权限，对于test模式新创建的也生效。
session 1：未来访问test模式下所有新建的表赋权，创建 t5 表。
-
postgres=
# alter default privileges in schema test grant select on tables to readonly ;
-
ALTER
DEFAULT
PRIVILEGES
-
postgres=
# create table test.t5 as select * from test.emp;
-
CREATE
TABLE
session 2：查询readonly用户
-
postgres=>
select
*
from
test.t5;
-
empno
|
ename
|
job
|
mgr
|
hiredate
|
sal
|
comm
|
deptno
-
-------+--------+-----------+------+------------+---------+---------+--------
-
7499
|
ALLEN
|
SALESMAN
|
7698
|
1981-02-20
|
1600.00
|
300.00
|
30
-
7521
|
WARD
|
SALESMAN
|
7698
|
1981-02-22
|
1250.00
|
500.00
|
30
-
7566
|
JONES
|
MANAGER
|
7839
|
1981-04-02
|
2975.00
|
|
20
-
7654
|
MARTIN
|
SALESMAN
|
7698
|
1981-09-28
|
1250.00
|
1400.00
|
30
-
7698
|
BLAKE
|
MANAGER
|
7839
|
1981-05-01
|
2850.00
|
|
30
-
7782
|
CLARK
|
MANAGER
|
7839
|
1981-06-09
|
2450.00
|
|
10
-
7839
|
KING
|
PRESIDENT
|
|
1981-11-17
|
5000.00
|
|
10
-
7844
|
TURNER
|
SALESMAN
|
7698
|
1981-09-08
|
1500.00
|
0.00
|
30
-
7900
|
JAMES
|
CLERK
|
7698
|
1981-12-03
|
950.00
|
|
30
-
7902
|
FORD
|
ANALYST
|
7566
|
1981-12-03
|
3000.00
|
|
20
-
7934
|
MILLER
|
CLERK
|
7782
|
1982-01-23
|
1300.00
|
|
10
-
7788
|
test
|
ANALYST
|
7566
|
1982-12-09
|
3000.00
|
|
20
-
7876
|
ADAMS
|
CLERK
|
7788
|
1983-01-12
|
1100.00
|
|
20
-
1111
|
SMITH
|
CLERK
|
7902
|
1980-12-17
|
800.00
|
|
20
-
(14
rows)
总结：影子用户创建的步骤
-
--创建影子用户
-
create
user readonly
with
password
'postgres';
-
--将schema中usage权限赋予给readonly用户,访问所有已存在的表
-
grant
usage
on
schema
test
to readonly;
-
grant
select
on
all
tables
in
schema
test
to readonly;
-
--未来访问test模式下所有新建的表
-
alter
default
privileges
in
schema
test
grant
select
on
tables
to readonly ;
转载：https://blog.csdn.net/pg_hgdb/article/details/114313062
查看评论