Postgres Utenti

esportare utenti

pg_dumpall --globals-only -U postgres -f roles.sql

oppure

pg_dumpall --roles-only -U postgres -f roles.sql

superuser

create role pgadmin with encrypted password 'XXXYYYYZZZZZ';
alter role pgadmin with login;
grant all privileges on database postgres to pgadmin;
alter role pgadmin with createdb;
alter role pgadmin with createrole;
alter role pgadmin with superuser;

utenti sola lettura

create group pasticcio_ro;
grant connect on database pasticci_db to pasticcio_ro;
grant usage on schema pasticci_schema to pasticcio_ro;
grant select on all tables in schema pasticci_schema to pasticcio_ro;
alter default privileges in schema pasticci_schema grant select on tables to group pasticcio_ro;

create role ciccio_pasticcio with login encrypted password 'XXXYYYYZZZZZ';
alter group pasticcio_ro add user ciccio_pasticcio;
alter role ciccio_pasticcio set search_path='pasticci_schema';

utenti proprietari

create role pasticcio_app with login encrypted password 'XXXYYYYZZZZZ';
grant all privileges on database pasticci_db to pasticcio_app;
alter user pasticcio_app set search_path='pasticci_schema';