Luca Ferrari <fluca1978@infinito.it> writes: > On Mon, May 11, 2015 at 10:46 AM, Pascal Cloup <ptpas059@gmail.com> wrote: >> How to know, programmaticaly, the number of databases/objects depending on a >> user?
> For databases I would join pg_database and pg_authid, for objects > pg_class and pg_authid.
Another way is to count the number of dependencies on that userid:
regression=# create user joe; CREATE ROLE regression=# select count(*) from pg_shdepend where refobjid = (select oid from pg_roles where rolname = 'joe') and refclassid = 'pg_authid'::regclass; count ------- 0 (1 row)
regression=# \c - joe You are now connected to database "regression" as user "joe". regression=> create table foo(f1 int primary key); CREATE TABLE regression=> select count(*) from pg_shdepend where refobjid = (select oid from pg_roles where rolname = 'joe') and refclassid = 'pg_authid'::regclass; count ------- 1 (1 row)
This works for all types of ownable objects, and I think it also will have entries for GRANTed permissions.
