如何给PostgreSQL特定角色授予指定库全表SELECT/INSERT权限并隔离其他库？

一、禁止角色访问目标外的数据库 #

PostgreSQL默认所有登录角色都拥有所有数据库的CONNECT权限，要实现隔离需先撤销全局默认权限，再仅授权目标数据库的连接权限：

• 撤销所有角色对非目标数据库的连接权限（以test2为例，可替换为其他需禁止的数据库）：

  REVOKE CONNECT ON DATABASE test2 FROM PUBLIC;
  

  注：PUBLIC是PostgreSQL内置的所有角色组，撤销后新创建的角色默认无法连接这些数据库
• 仅授予目标数据库的连接权限给指定角色：

  GRANT CONNECT ON DATABASE test TO test;
  

二、无需逐个指定表名，授予目标数据库全表的SELECT/INSERT权限 #

要让角色能访问目标库内所有现有及未来创建的表，需分三步配置schema和表权限：

1. 授予角色对目标库中schema的使用权限（以默认的public schema为例）：

   GRANT USAGE ON SCHEMA public TO test;
   

2. 授予对该schema下所有现有表的SELECT/INSERT权限：

   GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO test;
   

3. 配置默认权限，让未来在该schema下创建的表自动继承该角色的SELECT/INSERT权限：

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT ON TABLES TO test;
   

验证效果 #

• 使用test角色登录后，尝试连接test2数据库会提示权限不足，无法连接
• 连接test数据库后，执行\t可正常列表示，且能对表执行SELECT/INSERT操作

内容的提问来源于stack exchange，提问作者Tom Raganowicz