[PostgreSQL] 오브젝트, 데이터베이스, 스키마, ACL 등 각 종 권한 조회 스크립트

테이블, 시퀀스, 함수, 트리거 등 각종 오브젝트에 대한 권한 조회입니다

 

-- 유저 grant role 조회
select grantor::regrole::varchar(100) as grantor
, member::regrole::varchar(100)  as grantee
, string_agg(roleid::regrole ||  (case when admin_option = true then '(★)' else '' end),', ' order by roleid::regrole,admin_option) as grant_roles -- admin_option: is_grantable
from pg_catalog.pg_auth_members pam 
where member::regrole::varchar(100)  = 'user1' --특정 유저 grant role 조회
group by grantor, member;


--데이터베이스 권한 조회
select datname, datacl from pg_database;

--스키마 권한 조회  
with "schemas"("schema_name") as (
select n.nspname 
from pg_catalog.pg_namespace n
where n.nspname !~ '^pg_'
and n.nspname <> 'information_schema'
) 
select schema_name
, s.usename as role
, pg_catalog.has_schema_privilege(s.usename, schema_name, 'CREATE') AS "create"
, pg_catalog.has_schema_privilege(s.usename, schema_name, 'USAGE') AS "usage"
from schemas t
join pg_user s
on 1=1
where s.usename = 'user1';  

--테이블 권한조회
SELECT 
grantee, 
table_schema, 
table_name, 
privilege_type
FROM 
information_schema.role_table_grants
where grantee = 'user1';

--함수, 트리거함수 및 프로시저 권한 조회
SELECT
n.nspname AS schema_name,
p.proname AS procedure_name,
r.rolname AS role_name,
case when pg_get_function_result(p.oid) = 'trigger' THEN 'trigger function'
when p.prokind = 'p' then 'Procedure' when p.prokind = 'f' then 'function' end as object_type,
CASE 
WHEN has_function_privilege(r.rolname, p.oid, 'EXECUTE') THEN 'EXECUTE'
ELSE 'NO PRIVILEGE'
END AS privilege
FROM
pg_catalog.pg_proc p
JOIN
pg_catalog.pg_namespace n ON n.oid = p.pronamespace
JOIN
pg_catalog.pg_roles r ON has_function_privilege(r.rolname, p.oid, 'EXECUTE')
WHERE
n.nspname NOT IN ('pg_catalog', 'information_schema')  -- 특정 스키마 제외 (선택 사항)
and r.rolname = 'user1';

-- 시퀀스 권한 조회

SELECT
r.rolname AS grantee,
s.schemaname,
s.sequencename,
has_sequence_privilege(r.rolname, s.schemaname || '.' || s.sequencename, 'USAGE') AS usage_privilege,
has_sequence_privilege(r.rolname, s.schemaname || '.' || s.sequencename, 'SELECT') AS select_privilege,
has_sequence_privilege(r.rolname, s.schemaname || '.' || s.sequencename, 'UPDATE') AS update_privilege
FROM
pg_catalog.pg_sequences s
JOIN
pg_catalog.pg_roles r ON 1=1--has_sequence_privilege(r.rolname, s.schemaname || '.' || s.sequencename, 'USAGE')
WHERE
r.rolname = 'user1'; 

--default acl 조회
select * from pg_default_acl;

 
 유저를 삭제하려면 모든 권한이 제거되어야하는데
해당 권한을 제거하기위해 꽤 많은 정보를 봐야하네요
이게 다가 아닐지도...,,