postgresql
Gestion des rôles

Syntaxe

• 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

Créer un utilisateur avec un mot de passe

En règle générale, évitez d'utiliser le rôle de base de données par défaut (souvent postgres ) dans votre application. Vous devriez plutôt créer un utilisateur avec des niveaux de privilèges inférieurs. Ici nous en faisons un appelé niceusername et lui donner un mot de passe very-strong-password

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

Le problème est que les requêtes saisies dans la console psql sont enregistrées dans un fichier d'historique .psql_history dans le répertoire .psql_history de l'utilisateur et peuvent également être consignées dans le journal du serveur de base de données PostgreSQL, exposant ainsi le mot de passe.

Pour éviter cela, utilisez la commande \password pour définir le mot de passe utilisateur. Si l'utilisateur qui émet la commande est un superutilisateur, le mot de passe actuel ne sera pas demandé. (Doit être un superutilisateur pour modifier les mots de passe des superutilisateurs)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Créer un rôle et une base de données correspondante

Pour prendre en charge une application donnée, vous créez souvent un nouveau rôle et une nouvelle base de données.

Les commandes shell à exécuter seraient les suivantes:

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

$ createdb -O blogger blogger

Cela suppose que pg_hba.conf a été correctement configuré, ce qui ressemble probablement à ceci:

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

Accorder et révoquer des privilèges.

Supposons que nous ayons trois utilisateurs:

1. L'administrateur de la base de données> admin
2. L'application avec un accès complet pour ses données> read_write
3. L'accès en lecture seule> read_only

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

Avec les requêtes ci-dessus, les utilisateurs non approuvés ne peuvent plus se connecter à la base de données.

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

La série de requêtes suivante révoque tous les privilèges des utilisateurs non authentifiés et fournit un ensemble limité de privilèges pour l'utilisateur 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;

Modifier le chemin de recherche par défaut de l'utilisateur

Avec les commandes ci-dessous, le chemin de recherche par défaut de l'utilisateur peut être défini.

1. Vérifiez le chemin de recherche avant de définir le schéma par défaut.

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. Définissez search_path avec alter user commande alter user pour ajouter un nouveau schéma 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. Vérifiez le résultat après l'exécution.

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)

Alternative:

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

Accordez des privilèges d'accès aux objets créés ultérieurement.

Supposons que nous ayons three users :

1. L'administrateur de la base de données> admin
2. L'application avec un accès complet pour ses données> read_write
3. L'accès en lecture seule> read_only

Avec les requêtes ci-dessous, vous pouvez définir des privilèges d'accès sur les objets créés ultérieurement dans un schéma spécifié.

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;

Vous pouvez également définir des privilèges d'accès sur les objets créés ultérieurement par l'utilisateur spécifié.

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

Créer un utilisateur en lecture seule

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;