Query to list Users with "nologin" privilege in Postgres-CodePudding

How can I get a list of users in Postgres that do not have privilege to login?

I am using the following query to list the users and the groups they are part of

     SELECT usename AS role_name,
            CASE
                WHEN usesuper AND usecreatedb THEN
                    CAST('superuser, create database' AS pg_catalog.text)
                WHEN usesuper THEN
                    CAST('superuser' AS pg_catalog.text)
                WHEN usecreatedb THEN
                    CAST('create database' AS pg_catalog.text)
                ELSE
                    CAST('' AS pg_catalog.text)
            END role_attributes
        FROM pg_catalog.pg_user
        ORDER BY role_name desc;

which I believe is same as running \du in psql.

CodePudding user response：

I figured the above query lists only users that can login, but we can find that login info from a column named rolcanlogin in pg_catalog.pg_roles

CodePudding user response：

The underlying sql from \du is the query below. That should provide you with the information which users have privileges to login and which do not.

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  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 memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;