SELECT
namespace AS schemaname,
item AS object,
pu.groname AS groupname,
DECODE(
charindex (
'r',
split_part (
split_part (
array_to_string (relacl, '|'),
pu.groname,
2
),
'/',
1
)
),
0,
0,
1
) AS
SELECT
,
DECODE(
charindex (
'w',
split_part (
split_part (
array_to_string (relacl, '|'),
pu.groname,
2
),
'/',
1
)
),
0,
0,
1
) AS
UPDATE
,
DECODE(
charindex (
'a',
split_part (
split_part (
array_to_string (relacl, '|'),
pu.groname,
2
),
'/',
1
)
),
0,
0,
1
) AS
INSERT,
DECODE(
charindex (
'd',
split_part (
split_part (
array_to_string (relacl, '|'),
pu.groname,
2
),
'/',
1
)
),
0,
0,
1
) AS
DELETE
FROM
(SELECT
use.usename AS SUBJECT,
nsp.nspname AS namespace,
c.relname AS item,
c.relkind AS TYPE,
use2.usename AS OWNER,
c.relacl
FROM
pg_user USE
CROSS JOIN pg_class c
LEFT JOIN pg_namespace nsp
ON (c.relnamespace = nsp.oid)
LEFT JOIN pg_user use2
ON (c.relowner = use2.usesysid)
WHERE c.relowner = use.usesysid
AND item = 'fa_analystbyregion'
AND nsp.nspname NOT IN (
'pg_catalog',
'pg_toast',
'information_schema'
))
JOIN pg_group pu
ON array_to_string (relacl, '|') LIKE '%' || pu.groname || '%';