七月 11, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中分配权限。
目录
介绍
在创建一个对象时，会为其分配所有者。所有者通常是执行创建语句的角色。
对于大多数类型的对象，初始状态是只有所有者（或超级用户）才能对对象执行任何操作。若要允许其他角色使用它，必须授予权限。
有多种不同类型的权限：SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE 和 USAGE。
权限：
- SELECT - 能够对表执行 SELECT 语句。
- INSERT - 能够在表上执行 INSERT 语句。
- UPDATE - 能够对表执行 UPDATE 语句。
- DELETE - 能够对表执行 DELETE 语句。
- TRUNCATE - 能够对表执行 TRUNCATE 语句。
- REFERENCES - 能够创建外键（需要父表和子表的权限）。
- TRIGGER - 能够在表上创建触发器。
- CREATE - 能够执行 CREATE TABLE 语句。
- ALL - 授予所有权限。
- EXECUTE - 授予文件执行权限。
将权限分配给：
1. 组
2. 用户
3. 角色
4. 模式和搜索路径
- 能够创建用户、模式、角色、更改 SEARCH_PATH
- GRANT 和 REVOKE 权限
5. 对象所有权
组
一个组是要向其授予权限的一组数据库用户。
向组授予权限：
grant all privileges on database <db_name> to <group_name>;
用户
当用户创建表时，这些表属于 PUBLIC 模式。一个数据库可以使用模式在多个用户之间进行拆分。
向用户授予权限：
grant all privileges on database <db_name> to <user_name>;
grant all privileges on <table_name> to <user_name>;
撤消用户的权限：
revoke all privileges on database <db_name> from <user_name>
Grant insert privilege to all users on table tab1:
GRANT INSERT ON tab1 TO PUBLIC;
将一个表上的所有可用权限授予给用户 nijam：
GRANT ALL PRIVILEGES ON emp TO nijam;
请注意，如果由超级用户或 “emp” 的所有者执行，上面的语句确实会授予所有权限，但当由其他人执行时，它只会授予其他人可以授予的权限。
向用户 nijam 授予角色 admins 的成员资格：
GRANT admins TO nijam;
角色
从概念上讲，数据库角色与操作系统用户是完全独立的。在实践中，建立好对应关系可能很方便，但这不是必需的。数据库角色在整个数据库集群安装中是全局的（而不是每个单独的数据库）。
要分配权限，请使用 GRANT 命令。因此，如果 “nijam” 是现有角色，而 “emp” 是现有表，则可以授予更新表的权限：
GRANT UPDATE ON emp TO nijam;
特殊名称 PUBLIC 可用于给系统上的每个角色授予权限。在指定权限的位置用 ALL 代替，可以授予应用于对象上的所有权限。
GRANT all ON emp TO public;
模式
每个用户都有自己的模式和多个模式，可以更改模式搜索路径，以便每个用户首先找到自己的表，也可以访问其他用户的表。模式既可作为表的命名空间，也能提供安全性。通过为用户创建模式，并授予他们对该模式的权限，用户将在该模式下创建表，而不是 public 模式。模式搜索路径是给 PostgreSQL 数据库中的用户对象设置的，此参数的默认值为$user, public。
search_path可以基于用户进行设置，以让用户使用任意的模式列表来查看对象。
使用 psql 的\dp命令，可获取有关表和列的现有权限的信息：
=> \dp emp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+-----------------------+-----------------------+----------
public | emp | table | u1=arwdDxt/u1 +| col1: +|
| | | =r/u1 +| u1_rw=rw/u1 |
| | | admin=arw/u1 | |
(1 row)
\dp显示的条目解释如下：
rolename=xxxx -- privileges granted to a role
xxxx -- privileges granted to PUBLIC
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilege =
对象所有权
我们给用户提供连接数据库的连接权限：
GRANT CONNECT ON DATABASE database_name TO user_name;
我们给所有用户授予连接权限，以便进行数据库连接：
GRANT CONNECT ON DATABASE database_name TO Public;
GRANT CONNECT ON DATABASE database_name TO user_name;
REVOKE ALL ON ALL TABLES IN SCHEMA schema_name FROM PUBLIC;
GRANT CONNECT ON DATABASE database_name TO user_name;
我们给特定用户授予访问数据库中所有对象的权限：
grant all privileges on database dbname to dbuser;
GRANT CONNECT ON DATABASE database_name TO user_name;
创建具有登录权限的角色：
CREATE ROLE demo_role WITH LOGIN;
GRANT CONNECT ON DATABASE database_name TO user_name;
撤销登录权限：
ALTER ROLE demo_role WITH NOLOGIN;
GRANT CONNECT ON DATABASE database_name TO user_name;
GRANT UPDATE ON demo TO demo_role;
GRANT INSERT ON demo TO PUBLIC;
要查看授权表，可执行以下操作：
=> \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------------+----------+----------------------------+------------------------
public | demo | table | postgres=arwdDxt/postgres +|
| | | demo_role=w/postgres +|
| | | test_user=arwdDxt/postgres+|
| | | =a/postgres |
public | demo_id_seq | sequence | |
(2 rows)
CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;