PostgreSQL : ALTER DEFAULT PRIVILEGES

关于权限的问题,曾经有个问题一直困扰着,由于关系不是很大一直没有深究,今天偶然的一次机会看手册时看到 “ALTER DEFAULT PRIVILEGES” 命令,立即解决了困扰已久的问题。

问题描述

在生产数据库中通常不允许开发人员使用生产帐号连接数据库,那么有时候开发人员又需要查询数据库权限,用于日常问题排查,通常的做法是创建一个查询帐号,并把数据库中指定表的查询权限开放给给查询帐号,这也是目前我们生产系统维护过程中使用较多的方法,但是如果开发人员希望对整库所有表的查询权限均开放,而且以后生产库中新建的表也需要默认的开放此帐号的查询权限,这时如何处理?这时,今天学习到的命令即可解决这个问题,命令为 “ALTER DEFAULT PRIVILEGES”,下面简单测试下。

环境信息

PostgreSQL 版本: 9.1.0
数据库名: mydb
数据库属主: mydb
查询帐号 mydb_select

备注:假设数据库 mydb 库中用户仅创建 mydb schema。

创建只读帐号

1
2
3
4
5
6
[postgres@pgb ~]$ psql  
psql (9.1.0)
Type "help" for help.

postgres=# create role mydb_select LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE encrypted password 'mydb_select';
CREATE ROLE

备注:上面创建帐号 mydb_select。

给帐号赋权

1
2
3
4
5
6
7
8
9
postgres=# \c mydb mydb  
You are now connected to database "mydb" as user "mydb".

mydb=> grant connect on database mydb to mydb_select;
GRANT
mydb=> grant usage on schema mydb to mydb_select;
GRANT
mydb=> grant select on all tables in schema mydb to mydb_select;
GRANT

备注:上面命令给帐号 mydb_select 开通了数据库 mydb 的只读权限,可以访问mydb 库中 mydb schema 下的所有表。

权限测试

1
2
3
4
5
6
7
8
mydb=> \c mydb mydb_select;  
You are now connected to database "mydb" as user "mydb_select".

mydb=> select * from mydb.test limit 1;
id | name
--------+------
831681 | AAA
(1 row)

备注: mydb_select 用户查询 mydb.test 表成功,没有问题。

在 mydb 库中创建一张新表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mydb=> create table test_33 as select * From test limit 10 ;  
SELECT 10

mydb=> \dp test_33
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
mydb | test_33 | table | mydb=arwdDxt/mydb+|
(1 row)

mydb=> \c mydb mydb_select;
You are now connected to database "mydb" as user "mydb_select".
mydb=> select * from mydb.test_33;
ERROR: permission denied for relation test_33

备注:此时用户 mydb_select 没有权限查询,因为 mydb.test_33 是赋权后新建的表。

使用 “ALTER DEFAULT PRIVILEGES” 赋权

1
mydb=> ALTER DEFAULT PRIVILEGES IN SCHEMA mydb grant select on tables to mydb_select;ALTER DEFAULT PRIVILEGES

备注:我们更改用户 mydb_select 的默认权限,使得在数据库 mydb中 mydb 模式下创建的新表,默认给用户 mydb_select 开通查询权限,这个命令仅对未来创建的数据库对像生效,对当前存在的数据库对像不起作用, 这个命令的详细信息,参考本文末尾的附一。

权限测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mydb=> create table test_34 as select * From test limit 10 ;  
SELECT 10

mydb=> \dp test_34
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+--------------------+--------------------------
mydb | test_34 | table | mydb=arwdDxt/mydb +|
| | | mydb_select=r/mydb |
(1 row)

mydb=> \c mydb mydb_select
You are now connected to database "mydb" as user "mydb_select".

mydb=> select * From mydb.test_34 limit 1;
id | name
--------+------
831681 | AAA
(1 row)

mydb=> select * From mydb.test_33 limit 1;
ERROR: permission denied for relation test_33

备注:这时用户 mydb_select 果然拥有之后创建表(mydb.test_34) 的查询权限了,而对之前的表 mydb.test_33 依然没有查询权限,这也正好验证子这前关于这个命令的说明,即仅对未来创建的数据库对像生效。

附一 ALTER DEFAULT PRIVILEGE 命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Name
ALTER DEFAULT PRIVILEGES -- define default access privileges

Synopsis
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTIONS
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTIONS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
Description

ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in
the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the
privileges for tables (including views), sequences, and functions can be altered.

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%