From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Luca Ferrari <fluca1978(at)infinito(dot)it> |
Cc: | Pascal Cloup <ptpas059(at)gmail(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: count of databases by role/user |
Date: | 2015-05-11 13:51:14 |
Message-ID: | 23819.1431352274@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Luca Ferrari <fluca1978(at)infinito(dot)it> writes:
> On Mon, May 11, 2015 at 10:46 AM, Pascal Cloup <ptpas059(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Pascal Cloup | 2015-05-11 17:43:31 | Re: count of databases by role/user |
Previous Message | Luca Ferrari | 2015-05-11 09:46:30 | Re: count of databases by role/user |