Créer des utilisateurs et attribuer des privilèges dans PostgreSQL
Commandes psql pour créer bases et utilisateurs, changer un mot de passe, attribuer des rôles et des privilèges.
Cette fiche rassemble les commandes pour ouvrir une session PostgreSQL, créer des bases et des utilisateurs, modifier un mot de passe et attribuer des privilèges.
Ouvrir une session psql
sudo -u postgres psql postgres
ou, en deux étapes :
sudo su postgres
psql
Créer une base et un utilisateur
CREATE DATABASE database_name;
CREATE USER my_username WITH PASSWORD 'my_password';
GRANT ALL PRIVILEGES ON DATABASE "database_name" to my_username;
Variante interactive depuis le shell :
createuser --interactive --pwprompt
Modifier un mot de passe
Depuis psql :
\password postgres
ou avec une instruction SQL :
alter user <username> with encrypted password '<password>';
Attribuer des droits à un utilisateur
ALTER USER user1 CREATEDB;
ALTER USER user1 CREATEROLE;
Créer un rôle dba et un utilisateur associé
create role dba with nosuperuser createdb createrole nologin replication bypassrls;
grant usage on schema public to dba;
alter default privileges in schema public grant all on tables to dba;
alter default privileges in schema public grant all on sequences to dba;
grant connect on database myDatabase to dba;
grant select on all tables in schema public to dba;
grant select on all sequences in schema public to dba;
grant all privileges on all tables in schema public to dba;
create user dba_user login inherit encrypted password '<strong_password>' in role dba;
Options de CREATE ROLE et ALTER ROLE
| Option | Effet |
|---|---|
SUPERUSER / NOSUPERUSER |
Droits de superutilisateur |
CREATEDB / NOCREATEDB |
Création de bases de données |
CREATEROLE / NOCREATEROLE |
Création de rôles |
CREATEUSER / NOCREATEUSER |
Forme obsolète de SUPERUSER / NOSUPERUSER |
INHERIT / NOINHERIT |
Héritage des privilèges des rôles dont le rôle est membre |
LOGIN / NOLOGIN |
Autorisation de connexion |
REPLICATION / NOREPLICATION |
Connexion en mode réplication |
BYPASSRLS / NOBYPASSRLS |
Contournement des politiques de sécurité au niveau des lignes (RLS) |
CONNECTION LIMIT connlimit |
Nombre maximal de connexions simultanées |
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' |
Mot de passe du rôle |
VALID UNTIL 'timestamp' |
Date de fin de validité du mot de passe |
En cas d'échec de connexion
Si la connexion est refusée (par exemple « password authentication failed for user postgres »), vérifier le fichier d'authentification pg_hba.conf. Sur une ancienne version 8.4 le chemin était /etc/postgresql/8.4/main/pg_hba.conf ; adapter le chemin à la version installée.
Remarques
- Pour restreindre l'accès à une base, révoquer le droit
CONNECTaccordé àPUBLIC(REVOKE CONNECT ON DATABASE database_name FROM PUBLIC;), puis l'accorder au nouvel utilisateur. - Un compte de supervision n'a pas besoin du statut de superutilisateur : préférer les rôles prédéfinis (par exemple
pg_monitor) ou des droits ciblés. - Les attributs
REPLICATIONetBYPASSRLSsont des droits étendus : les accorder uniquement si nécessaire.