sql - 如何在 postgresql 中创建角色组

Question

我可以在 postgresql 中创建一个角色。

• 使用登录密码 'pass' 创建角色 myname;

我可以为该用户设置数据库模式的权限。

• GRANT USAGE ON SCHEMA public TO myname;

并为用户选择权限。

• GRANT SELECT ON ALL TABLES IN SCHEMA public TO myname;

但是我的数据库中有这么多用户。我不想为我的所有用户设置这些权限。其实我想创建角色组名:

• 观众
• 编辑
• 管理员

和viewer 将选择所有表的权限，editor 将对所有表具有选择、插入和更新权限。

我的用户将在这些组中。

我该怎么做？

Answer 1

CREATE ROLE viewer;
CREATE ROLE editor;
CREATE ROLE admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE ON TABLES TO editor;
GRANT some_other_privs_to_admin_group

之后只需将组授予用户:

GRANT editor TO your_user;

等等

https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html https://www.postgresql.org/docs/current/static/sql-createrole.html

CREATE ROLE adds a new role to a PostgreSQL database cluster. A role is an entity that can own database objects and have database privileges; a role can be considered a “user”, a “group”, or both depending on how it is used.

和

A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges