如何为PostgreSQL 11的只读用户指定部分表的写入权限？

核心思路 #

保留用户对大部分表的只读权限，仅针对Table1和Table2授予数据写入（插入、更新、删除）权限，同时严格限制其修改数据库架构或表结构的权限。

操作步骤 #

• 以超级用户身份连接目标数据库：

  psql -U superuser -d your_database_name
  

• 授予用户指定表的写入权限：

  GRANT INSERT, UPDATE, DELETE ON TABLE Table1, Table2 TO your_readonly_user;
  

• 确保用户拥有访问schema的基础权限，并保留对所有表的只读权限（若之前的全局只读权限被变更）：

  GRANT USAGE ON SCHEMA public TO your_readonly_user;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_readonly_user;
  

• 设置默认权限，让用户对后续新建的表自动获得只读权限（可选）：

  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_readonly_user;
  

• 验证权限是否生效：

  -- 检查用户对Table1的插入权限（返回t则生效）
  SELECT has_table_privilege('your_readonly_user', 'Table1', 'INSERT');
  -- 检查用户是否无表结构修改权限（返回f则正确）
  SELECT has_table_privilege('your_readonly_user', 'Table1', 'ALTER');
  

注意事项 #

• 若用户之前被授予过ALTER、CREATE、DROP等架构修改权限，需先执行REVOKE ALL PRIVILEGES ON SCHEMA public FROM your_readonly_user;撤销，再重新授予必要权限。
• 若目标表位于非publicschema下，需将上述命令中的public替换为对应的schema名称。

内容的提问来源于stack exchange，提问作者Grigory Zhadko