How to set roles and users with correct permissions on Postgres? [closed]

My current database uat has as owner postgres. But now I would like to set roles and users with the correct permissions. Something simple like:

roles: dba, developer, application

users: dba1, dev1, dev2, app1

Where dba can do anything on the database for administration purposes; developer can makes SELECT, UPDATE and INSERT, usual developer operations; app1 the same operations as the developer role, for now.

How can I make this correctly ? And who should be the owner of my production table in this new setup ?

UPDATE:

------------------------
-- INITIAL SETUP
------------------------

-- create uat database
create database uat;

-- dba role
create role dba with superuser createdb createrole nologin replication bypassrls;

-- dev role
create role dev with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- app role
create role app with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- alter postgres password
alter role postgres encrypted password '';

-- create users

create user dev1 login inherit encrypted password '' in role dev;
create user dev2 login inherit encrypted password '' in role dev;
create user dev3 login inherit encrypted password '' in role dev;
create user dev4 login inherit encrypted password '' in role dev;

create user webapp encrypted password '' in role app;

-- grant privileges to dba role
grant all privileges on all tables in schema public to dba;

------------------------
-- RESTORE 
------------------------
psql -U postgres -d uat -h localhost -W -p 5432 < uat.sql 

But when I logged in as user dev1 for example, I can’t do anything:

select * from guru_basket_security;

Listing access for this table:

    z    
                                                              Access privileges
     Schema |                         Name                         |   Type   |     Access privileges     | Column privileges | Policies 
    --------+------------------------------------------------------+----------+---------------------------+-------------------+----------
     public | guru_basket_security                                 | table    |                           |                   | 
     public | identifier                                           | table    | postgres=arwdDxt/postgres |                   | 

I got these errors:

ERROR:  permission denied for relation guru_basket_security
ERROR:  permission denied for relation identifier

The concept of groups & users has largely been done away with in PostgreSQL, with the terms USER and GROUP may be interchangeably used with ROLE. See documentation.

Suffice to say that what you want to do is create your “group” role first like this:

CREATE ROLE devs NOLOGIN;

Then grant all desired permissions to devs. Now create the users with IN ROLE devs clause like this:

CREATE ROLE jsmith LOGIN INHERIT IN ROLE devs ENCRYPTED PASSWORD '...' ...;
CREATE ROLE sjones LOGIN INHERIT IN ROLE devs ENCRYPTED PASSWORD '...' ...;
...

This will allow “real” users to login & inherit all the same permissions but no one can explicitly become devs (or the “group” role).

This is how I manage to solve my problem:

------------------------
-- INITIAL SETUP
------------------------

-- create uat database
create database uat;

-- dba role
create role dba with superuser createdb createrole nologin replication bypassrls;

-- dev role
create role dev with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- app role
create role app with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- alter postgres password
alter role postgres encrypted password '';

-- create users
create user dev1 login inherit encrypted password '' in role dev;
create user dev2 login inherit encrypted password '' in role dev;
create user dev3 login inherit encrypted password '' in role dev;

create user dba login inherit encrypted password '' in role dba;

create user app1 encrypted password '' in role app;

grant usage on schema public to dba;
grant usage on schema public to dev;
grant usage on schema public to app;

alter default privileges in schema public grant all on tables to dba;
alter default privileges in schema public grant all on tables to dev;
alter default privileges in schema public grant all on tables to app;

alter default privileges in schema public grant all on sequences to dba;
alter default privileges in schema public grant all on sequences to dev;
alter default privileges in schema public grant all on sequences to app;

revoke connect on database uat from public;

grant connect on database uat to dba;
grant connect on database uat to dev;
grant connect on database uat to app;

------------------------
-- RESTORE 
------------------------

psql -U postgres -d uat -h localhost -W -p 5432 < uat.clean.sql 

------------------------
-- REVOKE AND GRANT
------------------------
psql -U postgres -d uat -h localhost -W -p 5432

-- connect into uat
c uat

grant usage on schema public to dba; 
grant select on all tables in schema public to dba; 
grant select on all sequences in schema public to dba;

grant usage on schema public to dev; 
grant select on all tables in schema public to dev; 
grant select on all sequences in schema public to dev;

grant usage on schema public to app; 
grant select on all tables in schema public to app; 
grant select on all sequences in schema public to app;  

-- grant privileges
grant all privileges on all tables in schema public to dba;
grant all privileges on all tables in schema public to dev;
grant all privileges on all tables in schema public to app;