users per database

[Willy-Bas Loos <willybas@xxxxxxxxx>]

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