PostgreSQL 学习笔记009 —— PostgreSQL 权限管理

心有阳光 2023-01-04

1033

PostgreSQL 权限管理

权限管理

在postgresql数据库中，任何逻辑对象(包括数据库)都是有所有者的，也就是说数据库对象都是属于某个用户的，所以，无需把对象的权限赋予所有者，因为所有者默认就拥有所有的权限，在postgresql数据库中，删除及其修改对象的权限都不能赋予别的用户，它是所有者的固有权限，不能赋予或撤销，所有者也隐式地拥有把操作该对象的权限授予其他用户的权利。

用户的权限分两类

一类是在创建用户时就指定的权限，有：

超级用户的权限
创建数据库的权限
是否允许login的权限
更多见\help create role

这些权限是创建用户时指定的，后面可以使用alter role来修改。

另一类是有GRANT和REVOKE命令来管理的，有：

在数据库中创建schema的权限
在指定的数据库中创建临时表的权限
连接某个数据库的权限
在某个数据库中创建数据库对象的权限，如表、视图、函数等
在一些表中做SELECT 、INSERT、UPDATE、DELETE等操作的权限
在一张表的列上做 SELECT 、UPDATE、DELETE等操作的权限
对序列进行查询(执行序列的currval函数)、使用(执行序列的currval和nextval函数)、更新的权限
在表上创建触发器的权限
把表、索引创建到指定表空间的权限

postgresql中的权限是按照以下几个层次进行管理的

管理赋予用户的特殊权限，如超级用户的权限，创建数据库的权限、创建用户的权限、login权限等
在数据库中创建schema的权限
在schema中创建数据库对象(如表、索引)的权限
对表进行操作(insert、update、delete、select)的权限
操作(update、delete、select)表中某些字段的权限

授权

Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH ADMIN OPTION ]
    [ GRANTED BY role_specification ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

SELECT：允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。也允许使用COPY TO。在UPDATE或DELETE中引用现有列值也需要此权限。对于序列，此权限还允许使用currval函数。对于大对象，此权限允许读取对象。

INSERT：允许将新行INSERT到指定的表中。如果列出了特定列，则只能在INSERT命令中为这些列分配（因此其他列将接收默认值）。也允许COPY FROM。

UPDATE：允许更新指定表的任何列或列出的特定列，需要SELECT权限。

DELETE：允许删除指定表中的行，需要SELECT权限。

TRUNCATE：允许在指定的表上创建触发器。

REFERENCES：允许创建引用指定表或表的指定列的外键约束。

TRIGGER：允许在指定的表上创建触发器。

CREATE：对于数据库，允许在数据库中创建新的schema、table、index。

CONNECT：允许用户连接到指定的数据库。在连接启动时检查此权限。

TEMPORARY、TEMP：允许在使用指定数据库时创建临时表。

EXECUTE：允许使用指定的函数或过程以及在函数。

USAGE：对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。

ALL PRIVILEGES：一次授予所有可用权限。

撤销授权

Command:     REVOKE
Description: remove access privileges
Syntax:
REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ ADMIN OPTION FOR ]
    role_name [, ...] FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

常用命令

回收public权限

-- 任何用户对public的schema都有all的权限
-- 移除所有用户（public），superuser 除外，对指定 DB 下的 public schema 的 create 权限。
revoke create on schema public from public;
REVOKE

授予增删查改权限

postgres=# GRANT update,delete,insert,select ON ALL TABLES IN SCHEMA public TO test;
GRANT

创建只读用户

-- 回收public模式的create权限
postgres=# revoke create on schema public from public;
GRANT

-- 创建只读用户，密码为readonly
postgres=# create user testuser password 'readonly';
CREATE ROLE

-- 授权public模式给testuser
postgres=# grant usage on schema public to testuser;
GRANT

-- 授权public模式的所有表权限给testuser用户
postgres=# grant select on all tables in schema public to testuser;
GRANT

-- 切换到testuser测试
postgres=# \c - testuser;
You are now connected to database "postgres" as user "testuser".

-- 切换到postgres用户
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".

-- 将public模式的默认表查询权限授予testuser用户
postgres=# alter default privileges in schema public grant select on tables to testuser;
ALTER DEFAULT PRIVILEGES

-- 删除用户，要先删除权限依赖
-- postgres=# drop user testuser;
-- ERROR:  role "testuser" cannot be dropped because some objects depend on it
-- DETAIL:  privileges for schema public
--privileges for default privileges on new relations belonging to role postgres in schema public

postgres=# alter default privileges in schema public revoke usage on sequences from testuser;
ALTER DEFAULT PRIVILEGES
postgres=# alter default privileges in schema public revoke select,insert,delete,update on tables from testuser;
ALTER DEFAULT PRIVILEGES
postgres=# revoke select,insert,delete,update on all tables in schema public from testuser;
REVOKE
postgres=# revoke usage on all sequences in schema public from testuser;
REVOKE
postgres=# revoke all on schema public from testuser;
REVOKE

postgres=# drop user testuser;
DROP ROLE

查看用户权限

-- 查看某用户的系统权限
postgres=# SELECT * FROM  pg_roles WHERE rolname='postgres';
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-----
 postgres | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |  10
(1 row)

-- 查看某用户的表级别权限
postgres=# select * from information_schema.table_privileges where grantee='postgres';

-- 查看某用户的usage权限
postgres=# select * from information_schema.usage_privileges where grantee='postgres';
 grantor  | grantee  | object_catalog |   object_schema    |   object_name   | object_type | privilege_type | is_grantable
----------+----------+----------------+--------------------+-----------------+-------------+----------------+--------------
 postgres | postgres | postgres       | information_schema | cardinal_number | DOMAIN      | USAGE          | YES
 postgres | postgres | postgres       | information_schema | character_data  | DOMAIN      | USAGE          | YES
 postgres | postgres | postgres       | information_schema | sql_identifier  | DOMAIN      | USAGE          | YES
 postgres | postgres | postgres       | information_schema | time_stamp      | DOMAIN      | USAGE          | YES
 postgres | postgres | postgres       | information_schema | yes_or_no       | DOMAIN      | USAGE          | YES
(5 rows)

-- 查看某用户在存储过程函数的执行权限
postgres=# select * from information_schema.routine_privileges where grantee='postgres';


-- 查看某用户在某表的列上的权限
postgres=# select * from information_schema.column_privileges where grantee='postgres';

-- 查看当前用户能够访问的数据类型
postgres=# select * from information_schema.data_type_privileges ;

-- 查看用户自定义类型上授予的USAGE权限
postgres=# select * from information_schema.udt_privileges where grantee='postgres';

postgresql 数据库

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

PostgreSQL 学习笔记（共20篇）

postgresql的基础学习