redshift 전반적인 권한 확인할때 유용한 쿼리
WITH grantor AS
(
SELECT usesysid,
usename
FROM pg_user), schemas AS
(
SELECT oid,
nspname,
nspacl,
nspowner
FROM pg_namespace), grantee AS (
(
SELECT pg_user.usesysid AS usesysid,
0 AS grosysid,
pg_user.usename AS usename
FROM pg_user
UNION ALL
SELECT 0 AS usesysid,
pg_group.grosysid AS grosysid,
pg_group.groname AS usename
FROM pg_group)
UNION ALL
SELECT 0 AS usesysid,
0 AS grosysid,
'PUBLIC'::name AS usename)
SELECT privs.schemaname,
privs.objname,
privs.objtype,
privs.objowner,
privs.grantor,
privs.grantee
FROM (
-- Functions grants
SELECT sc.nspname,
textin(regprocedureout(c.oid::regprocedure)),
'Function',
pg_get_userbyid(c.proowner),
g.usename,
u.usename
FROM pg_proc c
join schemas sc
ON c.pronamespace=sc.oid,
grantor g,
grantee u
WHERE EXISTS
(
SELECT 1
WHERE aclcontains(c.proacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'EXECUTE',FALSE)))
UNION ALL
-- Language grants
SELECT NULL,
c.lanname,
'Language',
NULL,
g.usename,
u.usename
FROM pg_language c,
grantor g,
grantee u
WHERE EXISTS
(
SELECT 1
WHERE aclcontains(c.lanacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'USAGE',FALSE)))
UNION ALL
--Tables grants
SELECT sc.nspname,
c.relname,
CASE c.relkind
WHEN 'r' THEN 'Table'
WHEN 'v' THEN 'View'
END,
pg_get_userbyid(c.relowner),
g.usename,
u.usename
FROM pg_class c
join schemas sc
ON c.relnamespace=sc.oid,
grantor g,
grantee u
WHERE EXISTS
(
SELECT 1
WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'SELECT',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'DELETE',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'INSERT',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'UPDATE',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.relacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'REFERENCES',FALSE)))
UNION ALL
-- Schema grants
SELECT NULL,
c.nspname,
'Schema',
pg_get_userbyid(c.nspowner),
g.usename,
u.usename
FROM pg_namespace c,
grantor g,
grantee u
WHERE EXISTS
(
SELECT 1
WHERE aclcontains(c.nspacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'USAGE',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.nspacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'CREATE',FALSE)))
UNION ALL
-- Database grants
SELECT NULL,
c.datname,
'Database',
pg_get_userbyid(c.datdba),
g.usename,
u.usename
FROM pg_database c,
grantor g,
grantee u
WHERE EXISTS
(
SELECT 1
WHERE aclcontains(c.datacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'CREATE',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.datacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'TEMP',FALSE)))
UNION ALL
--Default ACL grants
SELECT sc.nspname,
decode(c.defaclobjtype,
'r','Tables',
'f','Functions'),
'Default ACL '
||decode(c.defaclnamespace,
0,'User',
'Schema'),
pg_get_userbyid(c.defacluser),
g.usename,
u.usename
FROM pg_default_acl c
left join schemas sc
ON c.defaclnamespace=sc.oid,
grantor g,
grantee u
WHERE EXISTS
(
SELECT 1
WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'SELECT',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'DELETE',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'INSERT',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'UPDATE',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'REFERENCES',FALSE))
UNION ALL
SELECT 1
WHERE aclcontains(c.defaclacl, makeaclitem(u.usesysid,u.grosysid,g.usesysid,'EXECUTE',FALSE)))
UNION ALL
--Default ACL grants with empty acl
SELECT sc.nspname,
decode(c.defaclobjtype,
'r','Tables',
'f','Functions'),
'Default ACL '
||decode(c.defaclnamespace,
0,'User',
'Schema'),
pg_get_userbyid(c.defacluser),
NULL,
decode(c.defaclobjtype,
'r','Regrant privileges on tables to owner',
'f','Regrant privileges on Functions to owner and PUBLIC')
FROM pg_default_acl c
left join schemas sc
ON c.defaclnamespace=sc.oid
WHERE EXISTS
(
SELECT 1
WHERE defaclacl='{}'::aclitem[]) ) privs("schemaname","objname","objtype","objowner","grantor","grantee")
WHERE schemaname = 'testdb';
