Thread

1. List users privileges for whole cluster/all databases in the cluster

   Jana Mihalidesová <mihi.jana@seznam.cz> — 2023-10-12T13:32:34Z

   Hi,
   
   
   
   I try to find out some view, select or something what show me the privileges
   for the user across the whole postgresql cluster. The username/user is 
   global for whole cluster not individual database, so I would like to know 
   the privileges for the user in all databases in the cluster using one view, 
   select.
   I know how to list user's privileges in the individual database, but for all
   databases...
   
   
   
   
   Thanks for the advice.
   
    Jana

2. Re: List users privileges for whole cluster/all databases in the cluster

   Priancka Chatz <pc9926@gmail.com> — 2023-10-12T14:05:23Z

   You can use pg_dumpall -g to get user information for the cluster.
   
   Regards,
   Priyanka
   
   On Thu, 12 Oct 2023 at 3:46 PM, Jana Mihalidesová <Mihi.Jana@seznam.cz>
   wrote:
   
   > Hi,
   >
   > I try to find out some view, select or something what show me the
   > privileges for the user across the whole postgresql cluster. The
   > username/user is global for whole cluster not individual database, so I
   > would like to know the privileges for the user in all databases in the
   > cluster using one view, select.
   > I know how to list user's privileges in the individual database, but for
   > all databases...
   >
   > Thanks for the advice.
   >  Jana
   >
   

3. Re: List users privileges for whole cluster/all databases in the cluster

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-10-12T14:25:02Z

   =?utf-8?q?Jana_Mihalidesov=C3=A1?= <Mihi.Jana@seznam.cz> writes:
   > I try to find out some view, select or something what show me the privileges
   > for the user across the whole postgresql cluster. The username/user is
   > global for whole cluster not individual database, so I would like to know 
   > the privileges for the user in all databases in the cluster using one view,
   > select.
   
   This is not possible, because a session can only see the catalogs for its
   own database (plus the shared catalogs, but those only describe shared
   objects).
   
   			regards, tom lane
   
   
   
   

4. Re: List users privileges for whole cluster/all databases in the cluster

   Dominique Devienne <ddevienne@gmail.com> — 2023-10-12T14:49:50Z

   On Thu, Oct 12, 2023 at 3:42 PM Jana Mihalidesová <Mihi.Jana@seznam.cz>
   wrote:
   
   > I try to find out some view, select or something what show me the
   > privileges for the user across the whole postgresql cluster. The
   > username/user is global for whole cluster not individual database, so I
   > would like to know the privileges for the user in all databases in the
   > cluster using one view, select.
   > I know how to list user's privileges in the individual database, but for
   > all databases...
   >
   
   As Tom already mentioned, this is per-DB. So you have to aggregate the
   privileges yourself,
   i.e. connect to any DB, lookup all databases the user can connect to, then
   connect to each DB
   in turn (possibly in parallel using several concurrent connections) to get
   the privs in that DB.
   This presumes the user doing the lookup can connect to at least the DBs
   that user's has access to.
   
   The query below should get you started on the first part. Just move the
   can-CONNECT test
   to the WHERE-clause instead of the SELECT-clause, and change session_user.
   --DD
   
   SELECT datname, datdba::regrole::text as owner,
          has_database_privilege(session_user, datname, 'CONNECT') as
   can_connect,
          has_database_privilege(session_user, datname, 'CREATE')  as
   can_create
     FROM pg_database
    WHERE datistemplate = false