1. 云图网首页
  2. 技术专区
  3. 数据库

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.

原创文章，作者：6024010，如若转载，请注明出处：https://blog.ytso.com/237893.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论