Re: count of databases by role/user

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