LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon