Обсуждение: count of databases by role/user
Hello,
How to know, programmaticaly, the number of databases/objects depending on a user?
I use PosgreeSql 9.4 on Windows and program in C# using Npgsql.
Thanks in advacne,
Best regards
On Mon, May 11, 2015 at 10:46 AM, Pascal Cloup <ptpas059@gmail.com> wrote: > Hello, > > 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. Hope this helps. Luca
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
Hello,
thank you to Tom and Luca, this is what i was looking for.
I will use something like:
select * from pg_database where datdba=(select oid from pg_authid where rolname='role_name');
Best regards
2015-05-11 15:51 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
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