Forums PostgreSQL.fr

Christof25

Membre

Select .... très sélectif

Bonjour

Dans cette requête, j'extrais les utilisateurs dont le rôle commence par role (roleadmin, roleread et rolewrite dans mon cas)

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)
WHERE r1.rolname like 'role%'
ORDER BY 1

Mais dans la liste, j'aimerais ne garder que ceux dont le matricule ne commence par une lettre, les 3 positions peuvent des chiffres ou lettres et les 3 dernières ne sont que des chiffres.
Sous Oracle, j'avais cela comme expression :

(REGEXP_LIKE(USERNAME,'^[a-zA-Z]{1}[a-zA-Z0-9]{3}[0-9]{3}$');

Sauriez-vous comment l'intégrer dans ma requête?

Merci

gleu

Administrateur

Re : Select .... très sélectif

A priori, vous aurez besoin de cette doc : https://docs.postgresql.fr/13/functions … SIX-REGEXP

---

Guillaume.

Christof25

Membre

Re : Select .... très sélectif

Yes, merci, c'est ce qu'il me fallait.

Cela marche bien :

SELECT regexp_match(r.rolname,'^[a-zA-Z]{1}[a-zA-Z0-9]{3}[0-9]{3}$') 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)
WHERE r1.rolname like 'role%'
ORDER BY 1
;

J'obtiens cela :

 psql -U $USER-d $BASE -f ListUser.sql
 username  |   role
-----------+----------
 {u999999} | roleadmin
(1 row)

Mais si je change mon filtre (les 3 dernières positions doivent être des lettres), cela me ressort quand même un enregistrement.... Normalement, cela doit être à zéro...

SELECT regexp_match(r.rolname,'^[a-zA-Z]{1}[a-zA-Z0-9]{3}[a-zA-Z]{3}$') as username,r1.rolname as "role"

J'obtiens une ligne à moitié vide alors que cela doit être à zéro...

 username |   role
----------+----------
          | roleadmin
(1 row)

Il manque un truc?

ruizsebastien

Membre

Re : Select .... très sélectif

ignorez mon message.
ce n'était pas pertinent.

Dernière modification par ruizsebastien (06/02/2021 11:52:17)

---

Cordialement,

Sébastien.

gleu

Administrateur

Re : Select .... très sélectif

regexp_match ne trouve pas de correspondance, donc il renvoit NULL. Le résultat est conforme à ce que vous demandez d'après votre requête SQL.

---

Guillaume.

Christof25

Membre

Re : Select .... très sélectif

Bonjour
J'ai un autre besoin.
A partir de cette même requête, je recherche maintenant tous les utilisateurs n'ayant aucun rôle.
Le but est de sécuriser un maximum ma base pour différencier les besoins de chacun et de les locker en cas de doute.

SELECT regexp_match(r.rolname,'^[a-zA-Z]{1}[a-zA-Z0-9]{3}[0-9]{3}$') 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)
WHERE r1.rolname is null
ORDER BY 1;

Mon compte devrait sortir :

psql -U $USER -d $BASE -c "\du* u999999";
            List of roles
 Role name | Attributes | Member of
-----------+------------+------------
 u999999   |            | {}

Merci

frost242

Administrateur

Re : Select .... très sélectif

Normalement, en transformant les JOIN en LEFT JOIN ? Avec la jointure interne (soit JOIN ou INNER JOIN si on aime taper sur son clavier), on ne fait ressortir que les lignes qui correspondent strictement à la jointure, donc pour lesquels il y a une entrée dans pg_auth_members.

---

Thomas Reiss

Christof25

Membre

Re : Select .... très sélectif

Normalement, en transformant les JOIN en LEFT JOIN ? Avec la jointure interne (soit JOIN ou INNER JOIN si on aime taper sur son clavier), on ne fait ressortir que les lignes qui correspondent strictement à la jointure, donc pour lesquels il y a une entrée dans pg_auth_members.

A tout hasard, avez-vous un utilitaire pour construire vos requêtes? Depuis ce matin, je n'arrive pas au résultat désiré....

J'ai bien tenté cela :

SELECT regexp_match(r.rolname,'^[u]{1}[a-zA-Z0-9]{3}[0-9]{2}[3]{1}$') 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 (r1.oid is null)
ORDER BY 1
;

Je galère...

gleu

Administrateur

Re : Select .... très sélectif

Si vous expliquiez ce que vous vouliez obtenir, on pourrait plus facilement vous aider.

---

Guillaume.

Christof25

Membre

Re : Select .... très sélectif

Je pensais l'avoir fait depuis le début de ce post...
Mais il est vrai que de refaire un point n'est pas inutile ;-)

A la base, je cherchais des utilisateurs ayant un rôle spécifique (commençant par role...) :

SELECT regexp_match(r.rolname,'^[a-zA-Z]{1}[a-zA-Z0-9]{3}[0-9]{3}$') 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)
WHERE r1.rolname like 'role%'
ORDER BY 1
;

Résultat :

 username  |   role
-----------+----------
 {u999999} | roleadmin

Maintenant, je recherche uniquement des utilisateurs SANS AUCUN rôle.
pour tester, j'ai recréé mon utilisateur u999999 sans rôle et j'essaie de l'extraire dans une liste

 username  
-----------
 {u999999}

Faut-il reprendre la 1ère requête pour la modifier ou la réécrire totalement?
Et comment...
Merci de votre aide

Christof25

Membre

Re : Select .... très sélectif

Re
Un ami m'a aidé entre temps.
Voilà ce que je cherchais ;-)

SELECT regexp_match(r.rolname,'^[a-zA-Z0-9]{2}[0-9]{5}$') as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid) where r1.rolname is null
ORDER BY 1
;