How to set roles and users with correct permissions on Postgres? 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 &lt; 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 &amp; 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 &amp; inherit all the same permissions but no one can explicitly become devs (or the "group" role).