Forums PostgreSQL.fr

Christof25

Membre

requête SQL User + lock + role

Bonjour

J'essaie d'obtenir une liste d'utilisateur de la manière suivante :

  username  |  lock  |         role
------------+--------+---------------------
 e123456    |   no   | 
 e987654    |   yes  | 
 u246802    |   no   | roleadmin

J'aimerai savoir pour chacun si ce dernier est locké ou pas et de connaître son rôle s'il en a un.
Pour le statut 'lock', c'est la valeur rolcanlogin dans pg_roles. Mais comment afficher yes pour f et no pour t?

J'ai un début de requête :

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)
ORDER BY 1
;

Mais loin d'être complète...
Pourriez-vous m'aider à la construire entièrement, svp?

Merci de votre écoute/aide.

Dernière modification par Christof25 (15/02/2021 17:56:41)

rjuju

Administrateur

Re : requête SQL User + lock + role

Bonjour,

Cela devrait donner quelque chose comme ça :

SELECT r.rolname, CASE WHEN r.rolcanlogin then 'no' else 'yes' end,
string_agg(r2.rolname, ', ')
FROM pg_catalog.pg_roles r
JOIN pg_catalog.pg_auth_members m ON m.roleid = r.oid
JOIN pg_catalog.pg_roles r2 ON m.member = r2.oid
GROUP BY 1, 2
ORDER BY 1;

---

Julien.
https://rjuju.github.io/

Christof25

Membre

Re : requête SQL User + lock + role

Merci Julien.
J'ai repris ton sql pour y apporter quelques optimisations :

SELECT regexp_match(r2.rolname,'^[a-zA-Z0-9]{2}[0-9]{5}$') as user, CASE WHEN r.rolcanlogin then 'no' else 'yes' end as lock, r.rolname
FROM pg_catalog.pg_roles r
JOIN pg_catalog.pg_auth_members m ON m.roleid = r.oid
JOIN pg_catalog.pg_roles r2 ON m.member = r2.oid
ORDER BY 2;

Résultat :

   user    | lock |       rolname
-----------+------+----------------------
 {u246802} | no   | roleadmin
           | yes  | pg_stat_scan_tables
           | yes  | pg_read_all_stats
           | yes  | pg_read_all_settings
           | yes  | readaccess
(5 rows)

Par contre, il me manque des utilisateurs, ceux notamment qui n'ont pas de rôle.
Si je fais :

sql -U $USER-d $BASE-c "\du"

Il m'affiche ceux qu'il me manque, à savoir :

                                     List of roles
 Role name  |                         Attributes                         |  Member of
------------+------------------------------------------------------------+--------------
 cv11111    | Superuser, Create role, Create DB                         +| {}
            | Password valid until infinity                              |
 e222222    | Superuser, Create role, Create DB                         +| {}
            | Password valid until infinity                              |
 e333333    | Superuser, Create role, Create DB                         +| {}
            | Password valid until infinity                              |
 e444444    | Superuser, Create role, Create DB                         +| {}
            | Password valid until infinity                              |
 e555555    | Superuser, Create role, Create DB                         +| {}
            | Password valid until infinity                              |
 e666666    | Superuser, Create role, Create DB                         +| {}
            | Password valid until infinity                              |

Comment les inclure dans ma requête?

Merci

Christof25

Membre

Re : requête SQL User + lock + role

Ah, j'y suis presque ....

SELECT regexp_match(r2.rolname,'^[a-zA-Z0-9]{2}[0-9]{5}$') as user, CASE WHEN r.rolcanlogin then 'no' else 'yes' end as lock, r.rolname
FROM pg_catalog.pg_roles r
LEFT JOIN pg_catalog.pg_auth_members m ON (m.roleid = r.oid)
LEFT JOIN pg_catalog.pg_roles r2 ON m.member = r2.oid
where r.rolname is null or r.rolname like 'role%'
ORDER BY 2;

Je n'ai toujours pas les utilisateurs sans rôle....

   user    | lock | rolname
-----------+------+----------
 {u246802} | no   | roleadmin
           | no   | rolewrite
           | no   | roleread
(3 rows)

dverite

Membre

Re : requête SQL User + lock + role

Dans postgres les utilisateurs sont potentiellement membre de rôles mais ils sont aussi eux-mêmes des rôles.
Apparemment il y a une confusion ici. Là vous mettez "user" dans la colonne de gauche (au sens membre d'un groupe) alors que dans la requête de Julien c'est les "groupes" qui sont à gauche et les "membres du groupe" à droite, donc ça a l'air inversé.

Autre chose, le fait de vider la colonne gauche avec le regexp_match est assez étrange. Ca donne des lignes dont on ne sait pas à quel "rolname" elles se réfèrent. A quoi elles servent? Si les users qui ne s'écrivent pas '^[a-zA-Z0-9]{2}[0-9]{5}$' doivent être éliminés, c'est dans la clause WHERE qu'il faut le faire, via WHERE  r2.rolname !~ '^[a-zA-Z0-9]{2}[0-9]{5}$'

Autre chose, vous avez enlevé le GROUP BY et le string_agg qui permet d'avoir une ligne par r.rolname et ça change tout à la structure.

Si les utilisateurs à sortir peuvent êtres membres de 0, 1 ou N rôles, quelle est la structure voulue pour le résultat?
Pour les requêtes non triviales, il faut partir de la structure du résultat pour déterminer comment les écrire.

---

@DanielVerite
http://blog-postgresql.verite.pro/

Christof25

Membre

Re : requête SQL User + lock + role

Merci dverite
En effet, pour Postgre, tout est mélangé entre utilisateur et rôle....
Dans mon cas, chaque utilisateur n'aura qu'un seul rôle. C'est pour cela que j'ai enlevé string_agg
Merci pour la clause Where.
Je vais continuer à plancher dessus.

Christof25

Membre

Re : requête SQL User + lock + role

Je pense que les jointures déclarées dans la requêtes excluent d'office les utilisateurs sans rôle, c'est pour cela que je n'arrive pas à les afficher.
j'ai peut-être une idée. Dans le select sur les 3 colonnes à afficher, je vais inclure un ARRAY de la sorte :

SELECT
      r.rolname as user,
      CASE WHEN r.rolcanlogin then 'no' else 'yes' end as LOCK,
      ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as role
FROM pg_catalog.pg_roles r
WHERE  r.rolname ~ '^[a-zA-Z0-9]{2}[0-9]{5}$'
ORDER BY 1;

J'obtiens cela :

  user   | lock |    role
---------+------+------------
 cv11111 | no   | {}
 e222222 | no   | {}
 e333333 | no   | {}
 e444444 | no   | {}
 e555555 | no   | {}
 e666666 | no   | {}
 u246802 | no   | {roleadmin}

Comment savez-vous s'il est possible de supprimer les {} du résultat?

rjuju

Administrateur

Re : requête SQL User + lock + role

Remplacez le ARRAY(...) par des LEFT JOIN.

---

Julien.
https://rjuju.github.io/

rjuju

Administrateur

Re : requête SQL User + lock + role

Dans mon cas, chaque utilisateur n'aura qu'un seul rôle. C'est pour cela que j'ai enlevé string_agg

J'imagine que vous n'avez pas modifié les catalogues systèmes pour ajouter une contrainte permettant de garantir cette propriété.  Il est du coup intéressant de se poser la question du comportement attendu de la requête pour le jour où vous aurez un role membre de plusieurs autre role, que cela soit volontaire ou pas.

---

Julien.
https://rjuju.github.io/

Christof25

Membre

Re : requête SQL User + lock + role

Remplacez le ARRAY(...) par des LEFT JOIN.

Je ne comprends pas....

rjuju

Administrateur

Re : requête SQL User + lock + role

Les accolades sont le format de sortie d'un tableau.  Si vous ne voulez pas d'un tableau mais un champ texte simple, il faut supprimer le ARRAY(subselect), et obtenir l'information en utilisant des jointures (qui sont actuellement dans le subselect, ou sinon dans la première requête que j'avais présentée) avec les autres tables, en précisant LEFT JOIN pour garder les rôles qui ne sont pas membres d'un autre rôle.

---

Julien.
https://rjuju.github.io/

dverite

Membre

Re : requête SQL User + lock + role

Par rapport à la requête en #7, si vous voulez garder l'idée du tableau et que seul le 1er élément compte, il est extrayable facilement avec (array(select ....))[1]. Si le tableau est vide ça va produire un NULL (et non une erreur c'est ça qui est important).

Par rapport à la situation où le compte serait membre de N rôles (même si dans votre cas ce n'est pas censé arriver), N-1 seraient donc filtrés avec comme critère d'être ou pas en position 1 dans le tableau, ce qui est plus ou moins aléatoire.

Une variante qui évite de perdre les N-1 rôles sans casser la structure principale et l'idée de la sous-requête serait d'utiliser string_agg avec un GROUP dans la sous-requête. C'est à-dire au lieu de tout le ARRAY(...) as role:

        (SELECT string_agg(b.rolname, ',')
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid GROUP BY r.oid) as role

---

@DanielVerite
http://blog-postgresql.verite.pro/

rjuju

Administrateur

Re : requête SQL User + lock + role

Effectivement ces 2 approches sont possibles.  Il me semble cependant important de préciser qu'il est généralement bon d'éviter des sous requêtes dans le SELECT, pour raisons de performances, et donc de voir comment obtenir la même information mais de manière plus performante.

---

Julien.
https://rjuju.github.io/

Christof25

Membre

Re : requête SQL User + lock + role

Re salut à tous

dernière sollicitation car y a un truc qui m'échappe avec ma requête quand je ne veux avoir qu'un seul utilisateur passé en paramètre...
J'ai essayé avec cela :

SELECT COUNT(*) = 1 AS user_exist FROM pg_user WHERE usename = :'p1' \gset

\if :user_exist

\gset
SELECT
      CONCAT('PUM_RECUP:',r.rolname) as user,
      CASE WHEN r.rolcanlogin then 'non' else 'oui' end as LOCK,
      ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        LEFT JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as role
FROM pg_catalog.pg_roles r
WHERE  r.rolname ~ '^":p1"$'
ORDER BY 1;
\else
\echo Warning ! User :p1 not exist on database
\endif

Ça me sort 0 ligne....

J'ai aussé essayé avec cela dans la clause WHERE

WHERE  r.rolname = ':p1'

une idée?

Merci

rjuju

Administrateur

Re : requête SQL User + lock + role

Vous avez un \gset en trop à priori, de plus

WHERE  r.rolname = ':p1'

est erroné, il faut utiliser

WHERE  r.rolname = :'p1'

---

Julien.
https://rjuju.github.io/

Christof25

Membre

Re : requête SQL User + lock + role

Vous avez un \gset en trop à priori, de plus

WHERE  r.rolname = ':p1'

est erroné, il faut utiliser

WHERE  r.rolname = :'p1'

Merci beaucoup!