在 PostgreSQL 中，用户和角色的管理是数据库安全性和权限控制的重要组成部分。用户和角色在 PostgreSQL 中基本上是一样的，在创建用户时实际上就是在创建一个角色。以下是关于用户与角色管理的详细说明。
1、创建和管理角色
1.1、创建角色
你可以使用 CREATE ROLE 命令创建一个新角色：
CREATE ROLE role_name;
如果你希望这个角色能够登录（即将其作为用户使用），需要添加 LOGIN 属性：
CREATE ROLE role_name WITH LOGIN PASSWORD 'password';
1.2、修改角色属性
你可以使用 ALTER ROLE 命令修改角色的属性。例如，赋予角色超级用户权限：
ALTER ROLE role_name WITH SUPERUSER;
1.3、设置角色密码：
ALTER ROLE role_name WITH PASSWORD 'new_password';
1.4、撤销某些特权，例如超级用户权限：
ALTER ROLE role_name WITH NOSUPERUSER;
1.5、删除角色
使用 DROP ROLE 命令删除一个角色：
DROP ROLE role_name;
2、用户/角色权限管理
2.1、授予权限
使用 GRANT 命令授予角色对特定数据库对象的权限。例如，授予角色对某个表的全部权限：
GRANT ALL PRIVILEGES ON TABLE tablename TO role_name;
授予连接数据库的权限：
GRANT CONNECT ON DATABASE dbname TO role_name;
授予特定权限，如只读访问权限：
GRANT SELECT ON TABLE tablename TO role_name;
2.2、撤销权限
使用 REVOKE 命令撤销角色对特定数据库对象的权限。例如，撤销角色对某个表的全部权限：
REVOKE ALL PRIVILEGES ON TABLE tablename FROM role_name;
撤销连接数据库的权限：
REVOKE CONNECT ON DATABASE dbname FROM role_name;
检查权限
要检查某个角色的权限，可以查询系统视图 pg_roles 和 pg_user:
SELECT * FROM pg_roles WHERE rolname = 'role_name';
或者查看具体表的权限信息：
\dp tablename
3、角色继承
在 PostgreSQL 中，角色可以继承其他角色的权限。通过这种方式，可以更方便地管理一组用户的权限。
3.1、创建可继承的角色
假设你有两个角色 parent_role 和 child_role，并希望 child_role 继承 parent_role 的所有权限：
CREATE ROLE parent_role WITH LOGIN PASSWORD 'password';
CREATE ROLE child_role WITH INHERIT LOGIN PASSWORD 'password';
GRANT parent_role TO child_role;
3.2、检查角色继承关系
要检查角色继承关系，可以查询系统视图 pg_auth_members:
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY role, member;
4、示例操作
以下是一些常见的用户和角色管理操作示例：
创建一个普通用户
CREATE ROLE user1 WITH LOGIN PASSWORD 'user1password';
创建一个超级用户
CREATE ROLE admin WITH LOGIN PASSWORD 'adminpassword' SUPERUSER;
为用户分配数据库权限
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO user1;
为用户分配模式和表权限
GRANT USAGE ON SCHEMA myschema TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO user1;
修改用户密码
ALTER ROLE user1 WITH PASSWORD 'newpassword';
删除用户
DROP ROLE user1;
5、总结
PostgreSQL 提供了灵活且强大的用户和角色管理功能，通过适当的角色划分和权限设置，可以确保数据库的安全性和高效管理。无论是通过命令行工具还是图形化工具，都能轻松实现用户和角色的创建、修改、删除及权限配置。
