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.
regards, tom lane
