Sample Header Ad - 728x90

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

0 votes
1 answer
686 views
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: 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!
Asked by lospejos (109 rep)
Apr 10, 2023, 01:08 PM
Last activity: Apr 10, 2023, 01:14 PM