Postgres manages the permissions through the concept of “roles.” With roles, there’s no distinction between users and groups. Roles are more flexible than the traditional Unix system of users and groups. For example, a role can act as a member of another role, inheriting its permissions.

User management within PostgreSQL is always be tricky to understand since it’s like combination of Oracle and SQLSERVER Databases. If users needs to have access to PostgreSQL databases we need to create the user and configure pg_hba.conf file
with client-side connections details and communication with the server.

Where as Roles, it can represent groups of users in the PostgreSQL ecosystem as well. PostgreSQL establishes the capacity for roles to assign privileges to database objects they own, enabling access and actions to those objects. Roles have the ability to grant membership to another role for managing Database, Table, and Column level privileges and restrictions.

When it comes to real world, we will be having different set of requirements like read-only, read-write, pii-read-only and developer users. In order to manage these users we need to create roles with restrictions so users will be differentiate.

1. Create Schema in the Database

CREATE SCHEMA DBADEEDS; 

2. We create roles to manage above schema in the PostgreSQL Database.

READ-WRITE

drop role rl_dbadeeds_rw;
create role rl_dbadeeds_rw;
GRANT USAGE ON SCHEMA dbadeeds to rl_dbadeeds_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbadeeds GRANT SELECT ON TABLES TO rl_dbadeeds_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbadeeds GRANT SELECT ON SEQUENCES TO rl_dbadeeds_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbadeeds GRANT UPDATE,DELETE,INSERT ON TABLES TO rl_dbadeeds_rw;
GRANT INSERT,UPDATE,SELECT,DELETE ON ALL TABLES IN SCHEMA dbadeeds TO rl_dbadeeds_rw;

READ-ONLY

drop role rl_dbadeeds_ro;
create role rl_dbadeeds_ro;
GRANT USAGE ON SCHEMA dbadeeds to rl_dbadeeds_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbadeeds GRANT SELECT ON TABLES TO rl_dbadeeds_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbadeeds GRANT SELECT ON SEQUENCES TO rl_dbadeeds_ro;

Developer Role

drop role rl_dbadeeds_developer;
create role rl_dbadeeds_developer;
grant rl_dbadeeds_rw to rl_dbadeeds_developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbadeeds GRANT ALL ON TABLES TO rl_dbadeeds_developer;
GRANT USAGE ON SCHEMA dbadeeds to rl_dbadeeds_developer;
GRANT ALL ON SCHEMA dbadeeds TO rl_dbadeeds_developer;

3. Now you can grants above roles to users

grant rl_dbadeeds_developer to developer-users; 
grant rl_dbadeeds_rw to app-users;
grant rl_dbadeeds_ro to adhoc-users;