postgresql
Gestione dei ruoli

Sintassi

• CREATE ROLE name [ [ WITH ] option [ ... ] ]

• CREATE USER name [ [ WITH ] option [ ... ] ]

• where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid

Crea un utente con una password

In generale, dovresti evitare di utilizzare il ruolo predefinito del database (spesso postgres ) nella tua applicazione. Dovresti invece creare un utente con livelli inferiori di privilegi. Qui ne facciamo uno chiamato niceusername e gli diamo una password very-strong-password

CREATE ROLE niceusername with PASSWORD 'very-strong-password' LOGIN;

Il problema è che le query digitate nella console psql vengono salvate in un file di cronologia .psql_history nella home directory dell'utente e possono anche essere registrate nel log del server database PostgreSQL, esponendo così la password.

Per evitare ciò, utilizzare il comando \password per impostare la password dell'utente. Se l'utente che ha emesso il comando è un superutente, la password corrente non verrà richiesta. (Deve essere superutente per modificare le password dei superutenti)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Crea il ruolo e il database corrispondente

Per supportare una determinata applicazione, spesso crei un nuovo ruolo e un nuovo database per farlo corrispondere.

I comandi della shell da eseguire sarebbero questi:

$ createuser -P blogger
Enter password for the new role: ********
Enter it again: ********

$ createdb -O blogger blogger

Questo presuppone che pg_hba.conf sia stato configurato correttamente, che probabilmente assomiglia a questo:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    sameuser        all             localhost               md5
local   sameuser        all                                     md5

Concedi e revoca i privilegi.

Supponiamo di avere tre utenti:

1. L'amministratore del database> admin
2. L'applicazione con un accesso completo per i suoi dati> read_write
3. L'accesso in sola lettura> read_only

--ACCESS DB
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
GRANT  CONNECT ON DATABASE nova  TO user;

Con le query di cui sopra, gli utenti non fidati non possono più connettersi al database.

--ACCESS SCHEMA
REVOKE ALL     ON SCHEMA public FROM PUBLIC;
GRANT  USAGE   ON SCHEMA public  TO user;

La prossima serie di query revoca tutti i privilegi agli utenti non autenticati e fornisce un insieme limitato di privilegi per l'utente read_write .

--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;
GRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin ;

--ACCESS SEQUENCES
REVOKE ALL   ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only; -- allows the use of CURRVAL
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of NEXTVAL and SETVAL
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of CURRVAL and NEXTVAL
GRANT ALL    ON ALL SEQUENCES IN SCHEMA public TO admin;

Modificare il percorso di ricerca predefinito dell'utente

Con i seguenti comandi, è possibile impostare il percorso di ricerca predefinito dell'utente.

1. Controlla il percorso di ricerca prima di impostare lo schema predefinito.

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

2. Imposta search_path con alter user comando alter user per aggiungere un nuovo schema my_schema

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter user user1 set search_path='my_schema, "$user", public';
ALTER ROLE

3. Controlla il risultato dopo l'esecuzione.

postgres=# \c postgres user1
Password for user user1: 
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Alternativa:

postgres=# set role user1;
postgres=# show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Concedi i privilegi di accesso agli oggetti creati in futuro.

Supponiamo di avere three users :

1. L'amministratore del database> admin
2. L'applicazione con un accesso completo per i suoi dati> read_write
3. L'accesso in sola lettura> read_only

Con le query sottostanti, è possibile impostare i privilegi di accesso sugli oggetti creati in futuro nello schema specificato.

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT                      ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL                         ON TABLES TO admin;

Oppure, è possibile impostare i privilegi di accesso sugli oggetti creati in futuro dall'utente specificato.

ALTER DEFAULT PRIVILEGES FOR ROLE admin GRANT SELECT  ON TABLES TO read_only;

Crea utente di sola lettura

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;

GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;