From: | Willy-Bas Loos <willybas(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | users per database |
Date: | 2014-11-25 19:09:14 |
Message-ID: | CAHnozThFJ4zto3Am2fV5QjK4HNoo8EtfKSqgnDOuiDo1VS82og@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I'm moving a database out of an existing cluster, and was wondering which
users i need to take with it.
So i made 2 little queries that show the users that have rights in the
database, maybe they wil come in handy for someone else too.
--show owners of objects in this database
select pg_get_userbyid(c.relowner), count(*)
from pg_class c
group by 1
order by 2 desc;
--show all users that have rights in this db (except column rights):
with a as (
select unnest(c.relacl)::text as priv
from pg_catalog.pg_class c
where relacl notnull
)
select substring(priv, 1, position('=' in priv)-1), count(*)
from a
where substring(priv, 1, position('=' in priv)-1) != ''
group by 1
order by 2 desc;
Cheers,
--
Willy-Bas Loos
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2014-11-25 20:41:12 | SQL functions and triggers? |
Previous Message | Bill Moran | 2014-11-25 16:54:20 | Re: Best filesystem for a high load db |