Find all sequences and other schema objects owned not by certain user

Question

How can I find all sequences (and more widely - all objects in given schema), which are owned not by certain user, on PostgreSQL (10-15)?

I found such query:

SELECT relname, relacl 
FROM pg_class pgc
WHERE relkind = 'S'
AND relacl is not null
AND relnamespace IN (
    SELECT oid
    FROM pg_namespace
    WHERE nspname NOT LIKE 'pg_%'
    AND nspname != 'information_schema'
)
and relname = 'my_table_id_seq';

But any further attempts to work with a relacl column fails with errors, f.e.:

array_to_string(array(relacl))

gives an error:

[42601] ERROR: syntax error at or near "relacl"

Also I've found a explodeacl function, which returns rowset of aclitem[], but I cannot get how to use it.

Thanks in advance!

score 1 · Accepted Answer · answered Apr 10 '23 at 13:14

relacl stores the access control list of an object, not the owner. The owner of a row in pg_class is stored in relowner. So to find all sequences not owned by a specific user, use:

SELECT relname, relacl 
FROM pg_class 
WHERE relkind = 'S'
AND relowner <> 'that_user'::regrole --<< "not owed by "that_user"
AND relnamespace IN (
    SELECT oid
    FROM pg_namespace
    WHERE nspname NOT LIKE 'pg_%'
    AND nspname != 'information_schema'
)
and relname = 'my_table_id_seq';

Find all sequences and other schema objects owned not by certain user

1 Answers1