utilisateurs et droits
Les valeurs par défaut font qu'il n'est pas possible de se connecter sur un rôle défini. Il ne faudra pas oublier de positionner l'option LOGIN pour un rôle destiné à permettre la connexion. La fonction CREATE USER peut être utilisée à la place de CREATE ROLE car la seule différence est qu'elle active le mode LOGIN par défaut.
Il existe également d'autres options qui, pour la plupart, ne sont là que pour des raisons de compatibilité avec les versions antérieures.
CREATE ROLE luc LOGIN PASSWORD 'toto' VALID UNTIL '2021-10-31 00:00:00';
CREATE ROLE devel ROLE luc;
CREATE ROLE jean IN ROLE devel;
SELECT rolname FROM pg_roles; rolname ------------- postgres luc jean devel (4 lignes)
DROP ROLE luc, jean, devel;
La table pg_roles contient les informations sur les différents rôles, ainsi il est possible de les consulter par :
SELECT * FROM pg_roles;
La possibilité de détruire simultanément plusieurs rôles est une extension de le nome SQL.
ALTER ROLE luc PASSWORD 'titi' VALID UNTIL 'TOMORROW 19:30';
ALTER ROLE jean RENAME TO jeanne;
ALTER ROLE jeanne IN DATABASE test_postgres SET search_path TO le_schema, public;
Il est possible d'attribuer des paramètres à un rôle dans une base de donnée particulière comme illustré dans ce dernier exemple. Ceci se fait par la directive IN DATABASE db_name. Dans le cas où aucune base de données n'est visée le paramètre portera sur l'ensemble des bases de données du cluster.
clusterdispose de son fichier de configuration pg_hba.conf.
Le fichier de configuration pg_hba.conf est défini dans postgres.conf.
Un autre blocage pour les connexions réseau peut être présent dans le fichier postgres.conf, car il contient la directive listen_addresses indiquant quelles sont les adresses à écouter. La valeur listen_addresses = '*' validera toutes les interfaces réseau. Il est également possible de modifier l'adresse du port d'écoute qui par défaut est 5432, ainsi que le nombre maximal de connexions simultanées.
clusteret de l'utilisateur visé il est possible de préciser le type d'authentification.
Le fichier pg_hba.conf est parcouru progressivement pour chaque type d'accès, chaque utilisateur et chaque cluster. Le premier en correspondance interrompra le parcours et le mode d'accès correspondant sera utilisé. Il est donc possible de configurer finement les droits pour chaque utilisateur.
Le mode trust ne devrait pas être utilisé en dehors des connections locales : socket UNIX et localhost.
Lors de la définition d'un mot de passe, si le système reconnaît une chaîne de caractères conforme à l'un de ses algorithmes
de chiffrement, celle-ci sera insérée telle qu'elle est fournie dans colonne rolpassword de la table
pg_authid, dans le cas contraire elle sera hachée
par l'algorithme précisé par la variable
password_encryption.
# conn bdd user adresse_CIDR auth
local all all trust
host all all 127.0.0.1/32 trust
host all all 10.0.0.0/24 password
host all all all scram-sha-256localhost
Le mot clef all permet de dire que tous les éléments sont concernés. Il peut se substituer aux connexions, aux bases de données et aux utilisateurs.
En fonction du type d'objet visé, la syntaxe de ALTER sera différente. Par exemple pour modifier le propriétaire d'une table il faudra utiliser ALTER TABLE ma_table OWNER TO mon_utilisateur;.
GRANT user_source TO user_cible
REVOKE user_source FROM user_cible
La modification explicite du rôle par SET ROLE user_source entraînera que l'identité effectivement utilisée sera user_source tandis que la modification implicite conservera l'identité user_cible. Ceci se constate facilement en créant un élément qui appartiendra au rôle l'ayant créé.
Le mode INHERIT est positionné par défaut lors de la création d'un rôle.
Les héritages de rôles sont stockés dans la table pg_auth_members sous forme d'OID.
GRANT droits ON type_objet nom_objet TO rôle
GRANT SELECT ON TABLE ma_table TO role1;
REVOKE droits ON type_objet nom_objet FROM rôle
REVOKE SELECT ON TABLE ma_table FROM role1;
| nom | description |
|---|---|
| SELECT | Autorise SELECT et COPY TO sur la table ou la colonne de table indiquée. |
| INSERT | Autorise INSERT et COPY FROM sur la table ou la colonne de table indiquée. |
| UPDATE | Autorise UPDATE sur la table ou la colonne de table indiquée. Sera aussi nécessaire pour SELECT FOR UPDATE et SELECT FOR SHARE |
| DELETE | Autorise DELETE sur la table ou la colonne de table indiquée. |
| TRUNCATE | Autorise TRUNCATE sur la table indiquée. |
| REFERENCES | Autorise une table ou une colonne de table à servir de référence pour une contrainte de clef étrangère |
| TRIGGER | Autorise la création d'un déclencheur sur la table indiquée |
| CONNECT | Autorise l'utilisateur à se connecter à la base indiquée |
| EXECUTE | Autorise l'utilisation de la fonction indiquée et l'utilisation de tout opérateur défini sur cette fonction |
| nom | description |
|---|---|
| CREATE | Sur bases de données : autorise la création de nouveaux schémas Sur schémas : autorise la création de nouvelles table, vues, ... Sur tablespaces : autorise la création de tables, d'index et de fichiers temporaires |
| TEMPORARY | Autorise la création de tables temporaires dans de la base de données |
| USAGE | sur langages procéduraux : autorise l'utilisation du langage pour la création de fonctions sur schémas : autorise l'accès aux objets contenus dans le schéma indiqué sur séquences : autorise l'utilisation des fonctions currval et nextval |
| ALL PRIVILEGES | Donne tous les droits disponibles en une seule opération |
Le droit d'écriture dans un schéma ne donne pas le droit de lecture sur celui-ci. Ainsi un utilisitateur disposant de CREATE mais pas de USAGE peut créer une table sans pouvoir consulter les données écrites par la suite. C'est le principe de la boîte aux lettres.
CREATE TABLE test (id int); CREATE TABLE GRANT ALL ON TABLE test to luc WITH GRANT OPTION; GRANT -- reconnexion sur "db" en utilisateur "luc" \connect db luc -- la délégation de droits par "luc" sur la table "test" est possible GRANT ALL ON TABLE test to toi; GRANT
Le mécanisme de propagation des droits est également accessible pour les rôles par l'utilisation de la commande suivante :
GRANT user_source TO user_cible WITH ADMIN OPTION; GRANT ROLE
Cette affectation permettra à user_cible de transmettre les droits sur user_source à un autre utilisateur.
ALTER DEFAULT PRIVILEGES [ FOR ROLE rôle ] [ IN SCHEMA schéma ] ( GRANT | REVOKE ) ...
ALTER DEFAULT PRIVILEGES FOR ROLE luc IN SCHEMA geo_france
GRANT SELECT ON TABLES TO jeanne;
ALTER DEFAULT PRIVILEGESLes droits par défaut sont enregistrés dans la table système pg_default_acl.
REVOKE CONNECT ON DATABASE test_postgres FROM PUBLIC; REVOKE
ALTER TABLE nom_table ENABLE ROW LEVEL SECURITY
CREATE POLICY nom ON nom_table [ FOR mode ]
[ TO role ] USING (fonc_bool)u:
ALTER TABLE villes ENABLE ROW LEVEL SECURITY;
ALTER TABLE
CREATE POLICY pas_de_ville_en_u ON villes FOR SELECT
TO luc USING (nom !~* '^u');
CREATE POLICYDROP policy pas_de_ville_en_u ON villes; DROP POLICY
Si l'utilisateur n'est pas précisé c'est le rôle PUBLIC qui sera visé.
Si le mode n'est pas précisé tous les droits seront visés (équivalent de FOR ALL).
Bien qu'il ne soit pas nécessaire d'intervenir directement sur les tables systèmes, on peut constater que les ACL sont écrites sous forme d'un tableau à une dimension contenant des indications de la forme {"lespostgres=arwdDxt/postgres","toto=r/postgres"}. La table pg_shdepend maintient les dépendances entre les divers objets de la base de données, et pour pouvoir supprimer, par exemple, un rôle il faudra s'assurer que celui-ci n'est plus présent dans cette relation.
Les tables destinées au stockage des rôles (pg_authid, pg_auth_members...) sont placées dans
l'espace global et donc dans le tablespace
pg_global.
rôle1_visé=droits/attributeur,rôle2_visé=droits/attributeur,...
| symbole | description |
|---|---|
| r | SELECT ("lecture") |
| w | UPDATE ("écriture") |
| a | INSERT ("ajout") |
| d | DELETE |
| D | TRUNCATE |
| x | REFERENCES |
| t | TRIGGER |
| X | EXECUTE |
| U | USAGE |
| C | CREATE |
| c | CONNECT |
| T | TEMPORARY |
Par exemple la table villes ayant les droits de lecture sur PUBLIC et tous les droits sur role1 attribués par postgres présentera dans la colonne relacl de la table pg_class le contenu suivant :
SELECT relacl,relname FROM pg_class WHERE relname = 'villes';
relacl | relname
--------------------------------------+---------
{=r/postgres,role1=arwdDxt/postgres} | villes
(1 ligne)
SELECT * FROM pg_roles;
\dg+
\dp
\ddp
SELECT u.rolname as user, r.rolname as "role" FROM pg_roles u JOIN pg_auth_members m ON (member = u.oid) JOIN pg_roles r ON (roleid=r.oid) ORDER BY user;
SELECT grantee, privilege_type, table_schema, table_name, column_name FROM information_schema.column_privileges WHERE grantee = 'PUBLIC' ORDER BY table_schema, table_name;
SELECT current_role; SELECT session_user;
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.table_privileges
WHERE grantee IN (
SELECT DISTINCT rolname FROM (
WITH RECURSIVE geth(i, m) AS (
SELECT oid, oid FROM pg_roles WHERE rolname = 'luc'
UNION ALL
SELECT roleid, member FROM geth AS g
JOIN pg_auth_members ON member = i
)
SELECT * FROM geth) AS rec
JOIN pg_roles AS ol ON ol.oid = rec.i)
ORDER BY table_schema, table_name;
Le concept des rôles comprends les concepts des utilisateurs et des groupes. Dans les versions de PostgreSQL antérieures à la 8.1, les utilisateurs et les groupes étaient des types d'entité distincts mais, maintenant, ce ne sont que des rôles. Tout rôle peut agir comme un utilisateur, un groupe ou les deux.
Il est parfois utile de maintenir une correspondance entre utilisateurs du système d'exploitation et rôles sur la base de données, mais ceci n'a rien d'obligatoire. Sous Unix, par défaut un utilisateur postgres est créé ainsi qu'un compte postgres administrateur de la base de données et des fichiers liés à son installation.
Un rôle possédant plusieurs rôles est un groupe de rôles.