How to List PostgreSQL Users and Permission

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.

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

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

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

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

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

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. 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;
    ———————————————————————————————————————
    psql (12) returns the following error:
    ———————————————————————————————————————
    LINE 2:        (select string_agg(pgu.usename, ‘,’ order by pgu.usen.

Leave a Reply

Your email address will not be published. Required fields are marked *