Copy SQL

WITH privs AS (SELECT 
    nc.nspname AS schema_name, 
    c.relname AS sequence_name, 
    string_to_array(translate(aclexplode(c.relacl)::text,'()',''),',') AS privileges,
    -- Lisatud veerud tabeli info jaoks
    tbl_ns.nspname AS owned_by_schema,
    tbl.relname AS owned_by_table,
    col.attname AS owned_by_column
FROM pg_class AS c 
INNER JOIN pg_namespace AS nc ON c.relnamespace=nc.oid
INNER JOIN pg_authid AS a ON nc.nspowner=a.oid
-- Ühendame pg_depend tabeliga, et leida seos jadast tabelini
LEFT JOIN pg_depend d ON d.objid = c.oid 
    AND d.classid = 'pg_class'::regclass 
    AND d.refclassid = 'pg_class'::regclass
    AND d.deptype IN ('a', 'i') -- 'a' = auto (serial), 'i' = internal (identity)
-- Ühendame tabeli info saamiseks
LEFT JOIN pg_class tbl ON tbl.oid = d.refobjid
LEFT JOIN pg_namespace tbl_ns ON tbl_ns.oid = tbl.relnamespace
-- Ühendame veeru info saamiseks
LEFT JOIN pg_attribute col ON col.attrelid = tbl.oid AND col.attnum = d.refobjsubid
WHERE (nc.nspname='public' OR a.rolname<>'postgres')
AND c.relkind='S'
),
privs_cleaned AS (SELECT schema_name, sequence_name, privileges[1]::oid AS grantor, privileges[2]::oid AS grantee, privileges[3] AS privilege_type, privileges[4]::boolean AS is_grantable, owned_by_schema,  owned_by_table, owned_by_column
FROM privs),

table_privs AS (SELECT tp.grantee, tp.table_schema, tp.table_name
FROM information_schema.table_privileges AS tp INNER JOIN information_schema.tables AS t
ON t.table_schema=tp.table_schema AND t.table_name=tp.table_name
WHERE t.table_type='BASE TABLE' AND tp.table_schema <>ALL  (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND grantee <> ALL (SELECT tableowner
FROM pg_catalog.pg_tables AS pt
WHERE pt.schemaname=tp.table_schema AND pt.tablename=tp.table_name)
AND tp.privilege_type IN ('INSERT'))

SELECT schema_name, sequence_name, a_grantor.rolname AS grantor, a_grantee.rolname AS grantee, privilege_type, is_grantable, owned_by_schema,  owned_by_table, owned_by_column
FROM privs_cleaned AS pc INNER JOIN pg_authid AS a_grantor ON pc.grantor=a_grantor.oid
INNER JOIN pg_authid AS a_grantee ON pc.grantee=a_grantee.oid
WHERE a_grantee.rolname<>'postgres'
AND a_grantor.rolname<>a_grantee.rolname
AND NOT EXISTS (SELECT *
FROM table_privs
WHERE table_privs.table_schema=pc.owned_by_schema
AND table_privs.table_name=pc.owned_by_table
AND table_privs.grantee=a_grantee.rolname)
ORDER BY schema_name, sequence_name, privilege_type;