How to List PostgreSQL Users and Permission

dbtut

6 years ago

In this article I will share a few scripts in postgres databases that I think will help you manage your users.

The easiest way to list users is to run the following command.

But it will not give you detailed information.

\du

\du

With the help of the following script you can see which databases the users have pemission to connect to.

select pgu.usename as user_name,
       (select string_agg(pgd.datname, ',' order by pgd.datname) 
        from pg_database pgd 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name
from pg_user pgu
order by pgu.usename;

select pgu.usename as user_name,

(select string_agg(pgd.datname, ',' order by pgd.datname)

from pg_database pgd

where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name

from pg_user pgu

order by pgu.usename;

With the help of the following script, you can also list by database basis.

select pgd.datname as database_name,
       (select string_agg(pgu.usename, ',' order by pgu.usename) 
        from pg_user pgu 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as user_name
from pg_database pgd
order by pgd.datname;

select pgd.datname as database_name,

(select string_agg(pgu.usename, ',' order by pgu.usename)

from pg_user pgu

where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as user_name

from pg_database pgd

order by pgd.datname;

In the above script, you can also query other permissions by typing one of the following permission types instead of connect.

SELECT,
INSERT,
UPDATE,
DELETE,
TRUNCATE,
REFERENCES,
TRIGGER,
CREATE,
TEMPORARY or TEMP,
EXECUTE,
USAGE

SELECT,

INSERT,

UPDATE,

DELETE,

TRUNCATE,

REFERENCES,

TRIGGER,

CREATE,

TEMPORARY or TEMP,

EXECUTE,

USAGE

You can see the details of the user permissions in the database with the help of the following script.

SELECT grantee
      ,table_catalog
      ,table_schema
      ,table_name
      ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE grantee != 'postgres'
GROUP BY grantee, table_catalog, table_schema, table_name;

SELECT grantee

,table_catalog

,table_schema

,table_name

,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges

FROM information_schema.role_table_grants

WHERE grantee != 'postgres'

GROUP BY grantee, table_catalog, table_schema, table_name;

And if you want, you can revoke all the privileges from a user with the command below.

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name;

1	REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name;