PostgreSQL角色权限配置入门常见问题咨询

1. administrator角色配置正确性判断 #

你的写法可以满足基础需求，但存在两个需要注意的场景差异：

• 如果你是先创建administrator角色，再用该角色执行建schema、建表操作，该配置完全生效，administrator天然拥有schema及内部所有表的全量读写权限。
• 如果你是先建schema和表，再修改schema所有者，ALTER SCHEMA只会修改schema本身的所有者，不会同步修改schema内已存在的users、api_credentials表的所有者，此时administrator对这两张旧表无操作权限，需要补充执行以下语句之一：
  • 批量修改schema内所有表的所有者：

    ALTER TABLE accounts.users OWNER TO administrator;
    ALTER TABLE accounts.api_credentials OWNER TO administrator;
    

  • 直接给administrator授权schema内所有现有表的全量权限：

    GRANT ALL ON ALL TABLES IN SCHEMA accounts TO administrator;
    

额外提示：CREATE ROLE administrator默认创建的角色没有LOGIN属性，无法直接使用该角色登录数据库，只能被其他有登录属性的用户继承。如果需要允许该角色直接登录，创建语句可以调整为CREATE ROLE administrator WITH LOGIN PASSWORD '自定义密码'; #

2. reader只读角色配置方法 #

你已经完成了schema访问权限的配置，接下来需要补充表的查询权限配置即可：

1. 给schema内现有所有表授予只读查询权限：

   GRANT SELECT ON ALL TABLES IN SCHEMA accounts TO reader;
   

2. 配置默认权限，让后续新创建在accounts schema下的表自动给reader开放查询权限，不需要重复授权：

   ALTER DEFAULT PRIVILEGES IN SCHEMA accounts GRANT SELECT ON TABLES TO reader;
   

如果你的表用到了自增主键序列，还需要补充序列的权限配置，否则查询带自增字段的表会报错：

-- 现有序列授权
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA accounts TO reader;
-- 新序列默认授权
ALTER DEFAULT PRIVILEGES IN SCHEMA accounts GRANT SELECT, USAGE ON SEQUENCES TO reader;

3. GRANT CONNECT语句作用说明 #

GRANT CONNECT ON DATABASE mydatabase TO myrole的作用是允许持有myrole角色的用户/角色连接到mydatabase数据库，和登录权限是两个独立的权限体系：

• LOGIN属性是角色本身的属性，拥有该属性的角色可以直接通过账号密码登录PostgreSQL实例
• CONNECT权限是数据库层面的权限，就算角色有LOGIN属性，如果没有对应数据库的CONNECT权限，也无法进入该数据库操作
  角色和用户在PostgreSQL中没有本质差异，仅存在是否有LOGIN属性的区别。如果你希望多个用户继承该角色的所有权限（包括CONNECT权限），直接给对应用户授予该角色即可：

GRANT myrole TO 用户1;
GRANT myrole TO 用户2;

默认创建的角色自带INHERIT属性，被授予的用户会自动继承该角色的所有权限，不需要额外配置。 #

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