how can I create role groups in postgresql

Question

I can create a role in postgresql.

• CREATE ROLE myname WITH LOGIN PASSWORD 'pass';

and I can set privilages on a database schema for this user.

• GRANT USAGE ON SCHEMA public TO myname;

and select privilages to a user.

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

But I have so many users in my database. I do not want to set these privilages to all of my users. Actually I want to create role groupnames:

• viewer
• editor
• admin

And viewer will be select privilages on all tables, editor will be select, insert and update privilages on all tables.

my users will be in these groups.

How can I do this?

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

after that just grant group to user:

GRANT editor TO your_user;

and so on

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.

and

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

Answer 2

For this very reason it is advisable to use "groups", that is roles (usually with NOLOGIN) to which you add the users (by granting the role to them).

In your case:

CREATE ROLE viewer;
GRANT <whatever> TO viewer;
GRANT viewer TO myname;

Then myname will enjoy all the privileges granted to viewer, and you don't have to mess around with granting and revoking privileges to every user.