核心概念

在PostgreSQL中，用户和角色在概念上是统一的。从8.1版本开始，CREATE USER 实际上是 CREATE ROLE 的别名，两者的唯一区别在于：CREATE USER 默认授予登录（login）权限，而 CREATE ROLE 默认不包含该权限。

角色：是一种实体，可拥有数据库对象（如表、视图）和被授予的权限。角色既可以分配给单个用户，也能作为用户组使用。

用户：本质上就是一个拥有登录权限（login）的角色。

组角色：一种支持其他角色"加入"的权限管理角色，现多采用角色继承机制实现批量权限管理。

权限层次结构

PostgreSQL的权限是分层的，理解这个层次至关重要:

权限层级结构依次为：实例级（Cluster level）→数据库级（Database level）→模式级（Schema level）→对象级（Table/View/Function etc）。需要注意的是，模式是PostgreSQL特有的概念，其他层级概念与MySQL保持一致。

1.角色/用户管理

创建用户（可登录的角色）

Create user username with password 'your_strong_password';
--或者等价
Create role username with login password 'your_strong_password';

创建组角色（不可登录）

Create role group_name NoLogin;

修改用户/角色

--修改密码
Alter user username with password 'new_password';
--重命名
Alter user old_username rename To new_username;
--给角色添加/删除属性
Alter role role_name with createdb;    --授予创建数据库权限
Alter role role_name with superuser;   --授予超级用户权限（慎用！！！）
Alter role role_name with nologin;     --禁止登录

除非拥有超级用户权限，否则无法成功登录PostgreSQL。这是因为连接时需明确指定目标数据库，无论是显式还是隐式方式。

• 显式指定：通过 -d 参数直接指定目标数据库（例如：psql -U 用户名 -d 数据库名）。

• 隐式指定：未明确指定时，PostgreSQL 将按以下顺序尝试连接：

  1. 查找与用户名同名的数据库（如用户 myuser 会优先连接 myuser 库）。
  2. 若同名数据库不存在，则尝试连接安装时创建的默认 PostgreSQL 管理库。

bash-4.2$ psql -U username -h127.0.0.1
Password for user username:
psql:error:connection to server at "127.0.0.1",port 5432 failed:
FATAL:database "username" does not exist

删除用户/角色

Drop role role_name;
--或者
Drop user username;

注意：若角色拥有数据库对象，则无法直接删除。需先转移所有权，或使用以下命令：

1. Drop owned by role_name;
2. Reassign owned by role_name to new_owner;

2.权限管理

数据库权限

连接到一个数据库（如postgres）后，可以授予其他数据库的权限。

--允许角色连接到数据库
Grant connect on database_name to role_name;
--允许角色在数据库中创建模式
Grant create on database_name to role_name;

授予连接（connect）权限后，我们就能登录psql查看该数据库的所有表结构，但无法对表进行任何操作。

mydb=> \dt
         List of relations
Schema | Name | Type | Owner
-------------------------------
public | users | table | postgres
(1 row)

mydb=>select * from users;
ERROR: permission denied for table users
mydb=>

表/视图权限

这是最常见的权限控制：

--允许访问public模式（前提）

Grant   usage  on  schema  public  to  users;

--授予public模式下所有现有表的select权限

Grant   select  on  all  tables  in  schema  public  to  users;

--授予public模式下所有现有表，增删改查的权限

Grant  select，insert，update，delete  on  all  tables  in  schema  public  to  user;

--授予public模式下所有表的所有权限

Grant  all  privileges  on   all  tablet  in  schema   public   to   myuser;

--授予users表在public模式（默认模式）

Grant   all   privileges  on  table  public,users  to  myuser;

PostgreSQL 权限管理命令

PostgreSQL 提供了 GRANT 和 REVOKE 两条核心命令来实现权限控制。

GRANT 命令用于向用户或用户组授予特定数据库对象（如表、视图、函数等）的操作权限（如 SELECT、INSERT、UPDATE、DELETE 等）。

语法：

GRANT privilege [, ...] ON object TO {user | group | PUBLIC} [, ...] [WITH GRANT OPTION];

该语法说明：

• privilege：表示要授予的具体权限
• object：权限作用对象，可以是表、视图、函数等数据库对象
• user/group：指定权限接收者，可以是单个用户或用户组
• public：特殊关键字，表示授予所有用户
• WITH GRANT OPTION：可选参数，允许被授权者进一步授权

例如，授权用户 zhangsan 对表 test 执行 SELECT 和 INSERT 操作的语句如下：

GRANT SELECT, INSERT ON test TO zhangsan;

REVOKE 命令用于撤销指定用户或用户组对特定对象的访问权限。

语法： REVOKE privilege [, ...] ON object FROM {user | group | PUBLIC} [, ...] [CASCADE | RESTRICT];

参数说明：

• privilege：要撤销的权限
• object：目标对象
• user/group/PUBLIC：权限授予对象 （各参数含义与 GRANT 命令一致）

示例： 撤销用户 zhangsan 对表 test 的 SELECT 权限： REVOKE SELECT ON test FROM zhangsan;

注意事项： 仅超级用户拥有权限授予和撤销的操作权限。

查询所有用户信息：

SELECT * FROM  pg_user;

组权限

我们设计了一个多应用共享场景：多个应用程序共用同一个库，每个应用拥有独立的密码，所有权限通过组机制进行集中管理。

1.创建组角色并授予数据库权限

--创建组角色（无需登录权限，仅用于权限聚合）
Create  role  app_group  nologin;
--授予组角色对目标数据库的连接权限
Grant  connect   on  database  目标库名  to   app_group;
--切换到目标数据库（必须先连接才能授予库内权限）
\c  目标库名;
--授予组角色对 public 模式的访问权限
Grant  usage   on   schema   public   to   app_group;
--授予组角色对现有表的操作权限（根据需求调整，如crud）
Grant   select,insert,update,delete  on  all   tables  in  schema   public   to   app_group;
  

2.为应用创建用户角色并加入组

--创建应用1的用户（带登录权限和密码）
Create   user   app1_user  with   login   password   'app1_pwd';
--创建应用2的用户
Create   user   app2_user   with   login   password   'app2_pwd';
--将用户加入组角色，继承组的所有权限
Grant   app_group   to   app1_user;
Grant   app_group   to   app2_user;

·权限统一管理：修改app_group的权限，所有应用用户自动继承，无需逐个调整。

·最小权限隔离：应用用户仅拥有组角色赋予的权限，避免直接授予超级权限。

·灵活扩展：新增应用时，只需创建新用户并加入组即可，无需重复配置权限。

权限继承

通过我们上个小节或者前面的配置，如果我们配置的时候库里面已经有10个表，但是我们后面业务增加还会出现新的库表，上面的配置账号是没有权限操作新表的。

这是PostgreSQL权限管理的一个重要特性----权限不会自动应用于未来新建的对象，必须通过额外配置才能让新表继承权限。

如果希望myuser对未来在public 模式中新建的表也自动拥有all权限，需要使用alter  default privileges命令：

--对未来在public 模式中创建的表，自动授予myuser所有权限
Alter  default   privileges   in   scheme    public;
Grant  all   on   tables    to     myuser;
--也可以指定新创建指定的权限
alter   default   privilege   in   scheme   public;
grant   select,insert  on   tables    to   myuser;

权限回收（Revoke）

讲解回收权限之前需要明白一个概念就是grant 是“叠加”而非，所有多次执行权限，只能增加权限，而不会减少权限，要减少就必须使用权限回收。

假设用户原本拥有select，insert，update，delete 权限

·执行revoke  delete  on  tablet   users   from   myuser;用户剩余权限为select，insect，update。

·执行grant   select ，insert，update   on  table   users    to    myuser;   --它的实际权限是不变的

对于通过角色继承获得的权限，必须从被继承的角色（父角色）中回收权限，才能彻底移除用户的该权限，直接对用户执行revoke无效，因为用户的权限来源于角色，而非直接授予。

示例场景：

角色app_group 被授予了users表的delete权限。

用户myuser是app_group 的成员（即myuser继承了app_group的权限）

正确操作：从父角色回收权限

--从app_group 中回收delete权限
Revoke   delete   on   table    users   from    app_group;

错误操作：直接对用户回收（无效）

--直接对 myuser 执行 revoke 无法移除继承的权限
Revoke  delete   on   table    users   from   myuser;

查看权限

postgres=# \x

Expanded  display is  on

postgres=# SELECT * FROM  pg_roles  WHERE rolname ='myuser';

-[ RECORD 1 ]--+-------------
rolname          |  myuser
rolsuper         |  f #是否未超级角色
rolinherit       |  t #是否允许继承权限
rolcreaterole    |  f #是否允许创建角色
rolcreatedb      |  f #是否允许创建数据库
rolcanlogin      |  f #是否允许登录
rolreplication   |  f #是否允许复制（主从配置）
rolconnlimit     |  -1  #是否有连接数限制
rolpassword      |  *********
rolvaliduntil    |
rolbypassrls     |  f
rolconfig        |
oid              |  16386