Forums PostgreSQL.fr

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

WHERE  r.rolname = ':p1'

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;

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

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;

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

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
;

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
;

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
;

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

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

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