PostgreSQL数据库安全

来源：互联网发布：a股人工智能龙头编辑：程序博客网时间：2024/04/28 20:32

http://www.wohedb.com/db_html_doc/administrator_guide/adm_2_security.htm

http://www.wohedb.com 中文数据库管理系统

第二章数据库安全

PostgreSQL实现了基于角色的存取控制机制。角色是权限的集合。可以将权限赋给用户，也可以将权限赋给角色。可以将角色赋给一个用户，该用户将拥有角色的所有权限。也可以将角色赋给其它的角色。PostgreSQL中的用户和角色使用同一个名字空间。数据库中不能有两个用户同名，不能有两个角色同名，也不能有一个用户和一个角色同名。

数据库中存在一个内置的超级用户postgres，postgres可以在数据库中创建新的超级用户，也可以把一个超级用户变成普通用户。同时数据库中也有一个内置的名为PUBLIC的角色, 任何用户都可以给PUBLIC授权和回收权限。数据库中的所有用户和角色都自动拥有PUBLIC角色拥有的一切权限。

2.1 数据库权限

PostgreSQL中的权限分为系统权限和对象权限。系统权限只能赋给用户，不能赋给角色。对象权限既能赋给用户，又能赋给角色。系统权限有以下三种：

权限名称

权限功能

CREATEDB

创建数据库

CREATEROLE

创建、删除和修改角色或用户

对象权限与具体的数据库对象有关，不同的数据库对象有不同的权限。 PostgreSQL中的对象表(table)、序列(sequnce)、数据库(database)、函数(function)、语言(languange)、模式(schema)和表空间(tablespace)都有自己的权限类型。

（1）表权限如下表

权限名称

权限功能

SELECT

查询表

INSERT

表中插入新记录

UPDATE

更新表

DELETE

删除表中的记录

REFERENCES

可以在表中创建外键约束, 必须在被外键引用的表上同时有该权限才能创建外键约束

TRIGGER

可以在表上创建触发器

（2）序列权限如下表

权限名称

权限功能

SELECT

对序列使用currval函数

USAGE

对序列使用currval 函数和nextval函数

UPDATE

对序列使用nextval 函数和setval函数

（3）数据库权限如下表

权限名称

权限功能

CONNECT

可以连接数据库

TEMPORAR

可以在数据库中创建临时表

TEMP

同上，可以在数据库中创建临时表

CREATE

可以在数据库中创建新的模式

PUBLIC角色自动拥有新建的数据库上的CONNECT权限。

（4）函数权限如下表

权限名称

权限功能

EXECUTE

可以调用函数，也可以使用任何在该函数基础上实现的运算符

（5）语言权限如下表

权限名称

权限功能

USAGE

可以使用该语言创建函数

（6）模式权限如下表

权限名称

权限功能

CREATE

在模式里面创建新的数据库对象，如果要重命令模式里面的一个数据库对象，执行命令的用户必须对该模式有CREATE权限，同时必须是并重命名的对象的所有者。

USAGE

允许访问模式里面的数据库对象

（7）表空间权限如下

权限名称

权限功能

CREATE

在表空间里面创建表、临时表和索引，新创建的数据库可以将该表空间作为它的默认表空间。如果该权限被回收，在该表空间中被创建的数据库对象仍然被存放在该表空间中。

2.2 数据库用户和角色

2.2.1创建数据库用户

使用下面的命令来创建数据库用户：

CREATE USER name [ [ WITH ] option [ ... ] ] [ WITH ] { ENCRYPTED | UNENCRYPTED } PASSWORD ‘password’[ [ WITH ] option [ ... ] ]

Option可以是:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CONNECTION LIMIT connlimit

| VALID UNTIL ’timestamp’

执行该命令的用户必须具有CREATEROLE权限或者是超级用户，其中WITH关键字可以省略。

SUPERUSER表示新创建的用户是超级用户，NOSUPERUSER表示新创建的用户不是超级用户，默认是NOSUPERUSER，只有postgres才能创建超级用户。

CREATEDB表示新创建的用户有CREATEDB权限，NOCREATEDB表示新创建的用户没有CREATEDB权限，默认是NOCREATEDB。

CREATEROLE表示新新创建的用户有CREATEROLE权限，NOCREATEROLE表示新创建的用户没有CREATEROLE权限，默认是NOCREATEROLE。

CONNECTION LIMIT connlimit 设定该用户能在数据库中建立的并发连接的上限，默认是没有上限。

{ ENCRYPTED | UNENCRYPTED } PASSWORD ‘password’设定用户的密码，指明ENCRYPTED,则用户的密码用加密的方式(MD5方法加密)存在数据库中，指明UNENCRYPTED则用户的密码用明文的方式存在数据库中。

VALID UNTIL ‘timestamp’ 指定密码的过期时间，timestamp格式是”年-月-日”，例如” 2010-09-23”，默认是永不过期。

下面是一些实际的例子：

(1) CREATE USER li_ming WITH PASSWORD ‘jw8s0F4’ CREATEDB CREATEROLE CONNECTION LIMIT 100 LOGIN;

该命令创建一个名为li_ming的数据库用户，它不是超级用户，它的密码是jw8s0F4，密码是用加密方式存放在数据库中，它具有CREATEDB、 CREATEROLE 和LOGIN权限，它在数据库中建立的并发连接数目不能超过100。

(2) CREATE USER li_zhe WITH PASSWORD ‘hjkkoi’ SUPERUSER VALID UNTIL ‘2010-09-09’;

该命令创建一个名为li_zhe的数据库用户，它是超级用户，它的密码是hjkkoi，密码是用加密方式存放在数据库中，它在数据库中建立的并发连接数目没有上限，它的密码将在2010年09月09日过期。

2.2.2 修改数据库用户的属性

使用下面的命令来修改数据库用户的属性：

ALTER USER name [ [ WITH ] option [ ... ] ]

其中option可以是:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CONNECTION LIMIT connlimit

| { ENCRYPTED | UNENCRYPTED } PASSWORD ’password’

| VALID UNTIL ’timestamp’

具有CREATEROLE权限的用户或者超级用户才能执行该命令，option选项的含义参见CREATE USER命令。数据库超级用户可以修改任何普通用户的属性。只用postgres才能修改其它的超级用户的属性。一个用户只能修改自己的密码，不能修改自己的其它属性。

下面是一些实际的例子:

（1）ALTER USER li_ming NOCREATEDB NOCREATEROLE NOLOGIN;

这条命令将使数据库用户li_ming失去CREATEDB、CREATEROLE和LOGIN权限。

（2）ALTER USER li_ming password ‘hjkop3’;

这条命令将数据库用户li_ming的密码改为hjkop3。

2.2.3 删除用户

使用下面的命令来删除数据库用户：

DROP USER [ IF EXISTS ] name [, ...]

DROP USER命令删除一个数据库用户。执行命令的用户必须有具有CREATEROLE 权限或者是超级用户。如果被删除的用户是超级用户，执行命令的用户必须是postgres。一个用户被删除以后，该用户拥有的所有数据库对象都会被自动删除，所有依赖于该用户拥有的数据库对象的数据库对象也会被自动删除。下面是一个例子：

（1）DROP USER user1;

删除用户user1。

2.2.4 创建角色

使用下面的命令来创建数据库角色：

CREATE ROLE name

具有CREATEROLE权限的用户才能执行该命令。下面是一个例子：

（1）CREATE ROLE role1;

这条命令在数据库中创建一个名为role1的角色。

2.2.5 删除角色

使用下面的命令来删除数据库角色：

DROP ROLE [ IF EXISTS ] name [, ...]

具有CREATEROLE权限的用户才能执行该命令。下面是一个例子：

（1）DROP ROLE role1;

删除角色role1。

2.2.6 给角色或用户授予对象权限

可以使用下面的命令来给角色或用户授予对象权限：

（1）GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }

[,...] | ALL [ PRIVILEGES ] }

ON [ TABLE ] tablename [, ...]

TO { rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

（2）GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }

ON SEQUENCE sequencename [, ...]

TO { role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

（3）GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }

ON DATABASE dbname [, ...]

TO { role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

（4）GRANT { EXECUTE | ALL [ PRIVILEGES ] }

ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]

TO { role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

（5）GRANT { USAGE | ALL [ PRIVILEGES ] }

ON LANGUAGE langname [, ...]

TO { role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

（6）GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }

ON SCHEMA schemaname [, ...]

TO { role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

（7）GRANT { CREATE | ALL [ PRIVILEGES ] }

ON TABLESPACE tablespacename [, ...]

TO { role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

执行该命令的用户必须满足下面的条件中的一个：

（1）是超级用户。

（2）是数据库对象的所有者。

（3）对权限有GRANT OPTION, 必须是直接拥有，从拥有的角色那里得到的GRANT OPTION是无效的。

USAGE、SELECT、UPDATE、CREATE和EXECUTE的含义已经在上面解释过。 ALL PRIVILEGES或者ALL表示该数据库对象上的所有权限。PUBLIC表示将权限赋给数据库中的所有角色和用户，即使是以后新建立的用户和角色也会自动拥有这些权限。GRANT OPTION 表示接收权限

的用户可以将该权限再赋给其它用户和角色， PUBLIC和GRANT OPTION不能同时使用。

下面是一些具体的例子：

（1）GRANT SELECT on salary to user1, role1;

这条命令将表salary上的SELECT权限赋给用户user1和角色role1。

（2）GRANT SELECT on salary, UPDATE on salary to role1 ;

这条命令将表salary上的SELECT和UPDATE权限赋给角色role1。

（3）GRANT ALL PRIVILEGES on salary to role1 ;

这条命令将表salary上的所有权限赋给角色role1。

（4）GRANT USAGE on SEQUENCE id_counter to role1 ;

这条命令将序列id_counter上的USAGE权限赋给角色role1。

（5）GRANT USAGE on SEQUENCE id_counter to PUBLIC;

这条命令将序列id_counter上的USAGE权限赋给数据库中的所有角色和用户。

（6）GRANT USAGE on SEQUENCE id_counter to user1,role1 WITH GRANT OPTION;

这条命令将序列id_counter上的USAGE权限赋给角色或用户user1和角色role1。用户user1可以将该权限再赋给其它用户或角色。

2.2.7 从角色或用户手中回收对象权限

使用下面的命令来从角色或用户手中回收对象权限：

（1）REVOKE [ GRANT OPTION FOR ]

{ { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }

[,...] | ALL [ PRIVILEGES ] }

ON [ TABLE ] tablename [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

（2）REVOKE [ GRANT OPTION FOR ]

{ { USAGE | SELECT | UPDATE }

[,...] | ALL [ PRIVILEGES ] }

ON SEQUENCE sequencename [, ...]

FROM { [ role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

（3）REVOKE [ GRANT OPTION FOR ]

{ { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }

ON DATABASE dbname [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

（4）REVOKE [ GRANT OPTION FOR ]

{ EXECUTE | ALL [ PRIVILEGES ] }

ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

（5）REVOKE [ GRANT OPTION FOR ]

{ USAGE | ALL [ PRIVILEGES ] }

ON LANGUAGE langname [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

（6）REVOKE [ GRANT OPTION FOR ]

{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }

ON SCHEMA schemaname [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

（7）REVOKE [ GRANT OPTION FOR ]

{ CREATE | ALL [ PRIVILEGES ] }

ON TABLESPACE tablespacename [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

执行该命令的用户必须曾经向被回收权限的用户或角色授予过被回收的权限。如果执行该命令的用户没有向被回收权限的用户或角色授予过被回收的权限，但该用户是超级用户，或者是权限引用的数据库对象的所有者，或者拥有被回收的权限和该权限的GRANT OPTION，数据库不会报错，被回收权限的用户或角色仍然拥有被回收的权限。

PUBLIC表示从从PUBLIC角色手中回收权限。GRANT OPTION FOR表示只回收权限的GRANT OPTION，不回收权限。REVOKE ALL PRIVILEGES 表示回收指定的数据库对象上的所有权限。CASCADE表示如果被回收权限的用户或角色将该权限又赋给了其它用户和角色，该权限也会从这些用户和角色手中被回收掉。RESTICT的含义与CASCADE相反，表示如果被回收权限的用户或角色将该权限又赋给了其它用户和角色，该权限不会从这些用户和角色手中被回收掉。如果CASCADE 和 RESTRICT 都没有指定，默认是RESTRICT。

下面是一些具体的例子：

（1）REVOKE SELECT on salary from PUBLIC, role1;

这条命令将表salary上的SELECT权限从角色PUBLIC，角色role1手中回收掉。

（2）REVOKE GRANT OPTION FOR SELECT on salary from user1;

这条命令将salary上的SELECT权限的GRANT OPTION从用户user1手中回收掉， user1仍然拥有SELECT权限，但他不能将该权限再赋给其它用户或角色。

（3）REVOKE SELECT on salary from user1 CASCADE;

这条命令将表salary上的SELECT权限从用户user1手中回收掉，如果user1将该权限赋给了其它用户或角色，这些用户或角色也会失去该权限。

2.2.7 将角色赋给角色和用户

使用下面的命令将角色赋给角色和用户：

GRANT role_name [, ...] TO role_or_user_name [, ...]

执行该命令的用户应该满足下面的两个条件中的一个:

（1）具有CREATEROLE权限。

（2）是超级用户。

下面是一些具体的例子：

（1）GRANT role1 to role2, user1;

这条命令将角色role1赋给角色role2和用户user1。

（2）GRANT role1 to role2, user1 WITH ADMIN OPTION;

这条命令将角色role1赋给角色role2和用户user1，role2的用户成员可以将role1赋给其它用户或角色，user1也可以将role1赋给其它用户或角色。

2.2.8 从角色和用户手中回收角色

使用下面的命令从角色和用户手中回收角色：

REVOKE role [, ...] FROM role_or_user_name [,...]

执行该命令的用户应该满足下的两个条件中的一个:

（1）具有CREATEROLE权限。

（2）是超级用户。

注意: 如果多个不同的用户将同一权限obj_priv赋给了同一个用户或角色role1，如果有一个授权者从role1手中回收权限obj_priv，那么role1仍然拥有权限obj_priv。只有所有的授权者对role1执行了回收权限obj_priv的操作，而且role1拥有的所有角色都不拥有权限obj_priv，role1才会真正地失去权限obj_priv。