Ubuntu中PostgreSQL的权限管理方法

Ubuntu中PostgreSQL权限管理主要通过用户/角色创建、权限分配及回收实现，核心命令如下：

一、用户与角色管理

1. 创建用户/角色

   • 用户（可登录）：
     CREATE USER 用户名 WITH PASSWORD '密码' [LOGIN] [CREATEDB] [CREATEROLE]
     示例：CREATE USER admin WITH PASSWORD '123456' LOGIN CREATEDB;
   • 角色（不可登录，用于权限组）：
     CREATE ROLE 角色名 [CREATEDB] [CREATEROLE]
     示例：CREATE ROLE readonly NOLOGIN;

2. 修改用户/角色属性

   • ALTER USER 用户名 WITH PASSWORD '新密码' [SUPERUSER | NOSUPERUSER]
   • ALTER ROLE 角色名 ADD/DROP CREATEDB/CREATEROLE

3. 删除用户/角色
   DROP USER 用户名; 或 DROP ROLE 角色名;

二、权限分配与回收

1. 数据库级权限

   • 授予权限：
     GRANT {ALL PRIVILEGES | SELECT/INSERT/UPDATE/DELETE} ON DATABASE 数据库名 TO 用户/角色
     示例：GRANT ALL PRIVILEGES ON DATABASE testdb TO admin;
   • 回收权限：
     REVOKE {ALL PRIVILEGES | SELECT/INSERT/UPDATE/DELETE} ON DATABASE 数据库名 FROM 用户/角色

2. 表/模式级权限

   • 授予权限：
     GRANT {SELECT/INSERT/UPDATE/DELETE/TRUNCATE} ON 表名 TO 用户/角色 [WITH GRANT OPTION]
GRANT USAGE ON SCHEMA 模式名 TO 用户/角色（允许访问模式）
   • 回收权限：
     REVOKE {SELECT/INSERT/UPDATE/DELETE/TRUNCATE} ON 表名 FROM 用户/角色

3. 特殊权限

   • 超级用户权限：仅限postgres用户或通过ALTER ROLE赋予（需谨慎）
   • 连接权限：通过GRANT CONNECT ON DATABASE控制

三、常用操作示例

1. 创建可登录用户并授权：

   sudo -u postgres psql  
   CREATE USER dev WITH PASSWORD 'dev123' LOGIN CREATEDB;  
   GRANT ALL PRIVILEGES ON DATABASE devdb TO dev;  
   \q  
   

2. 创建只读角色并应用到所有表：

   sudo -u postgres psql  
   CREATE ROLE readonly NOLOGIN;  
   GRANT CONNECT ON DATABASE testdb TO readonly;  
   GRANT USAGE ON SCHEMA public TO readonly;  
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;  
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;  
   \q  
   

四、验证与工具

• 查看权限：
  • 用户/角色列表：\du
  • 数据库权限：\l（查看数据库所有者及权限）
  • 表权限：\dp 表名
• 图形化管理：使用pgAdmin（需安装）通过界面操作用户和权限。

注意事项

• 避免直接使用SUPERUSER权限，优先通过角色继承控制权限。
• 修改权限后需确保客户端重新连接以生效。
• 生产环境中建议通过pg_hba.conf限制IP访问。

以上操作基于PostgreSQL原生权限模型，适用于Ubuntu系统下的数据库权限管理。