[Postgresql] 각종 object 조회하기

유저삭제시에 해당 유저가 소유한 객체와 부여받은 모든 권한이 제거되어야하므로

우선 소유한 객체를 확인하는 용도로 쿼리를 정리하였습니다.

 

pg_catalog를 활용한 테이블, 뷰, 시퀀스, 인덱스, 함수, 프로시저, 트리거 등

오브젝트의 스키마, 소유자를 조회하는 쿼리입니다.

select * 
  from ( -- 테이블, 뷰, 시퀀스, 인덱스 ,Mview, toast table, foreign table 등 조회                      
    SELECT r.rolname as owner, n.nspname as schema_nm
         , case when c.relkind = 'r' then 'table' 
   		        when c.relkind = 'v' then 'view'
		        when c.relkind = 'S' then 'sequence'
		        when c.relkind = 'i' then 'index'
		        when c.relkind = 'm' then 'materialized view'
		        when c.relkind = 'c' then 'composite type'
		        when c.relkind = 't' then 'TOAST table'
		        when c.relkind = 'f' then 'foreign table'
		        when c.relkind = 'p' then 'partition table'
		        else ''
	        end AS object_type
         , c.relname              AS object_name
         , '' as tb_nm
      FROM pg_catalog.pg_class c 
         , pg_catalog.pg_namespace n
         , pg_roles r 
     WHERE c.relnamespace = n.oid 
       and c.relowner = r.oid
       --and r.rolname = 'postgres' --소유자
 	   --and c.relkind  = 'r' --오브젝트타입
       --and n.nspname in ('public') -- 스키마 조건
       --and c.relname like 'v_%'    -- object 명 조건
       --and c.relkind in ('r','v') --오브젝트 종류
    union all             
    --함수, 프로시저 조회
   SELECT pr.rolname AS owner,
          pn.nspname AS schema_nm,
          CASE WHEN pg_get_function_result(pp.oid) = 'trigger' THEN 'trigger function'
               WHEN pp.prokind = 'f' THEN 'function'
               WHEN pp.prokind = 'p' THEN 'procedure'
           END AS object_type,
          pp.proname AS object_name
        , '' as tb_nm
     from pg_proc pp
    INNER JOIN pg_namespace pn ON pp.pronamespace = pn.oid
    INNER JOIN pg_language pl ON pp.prolang = pl.oid
     left JOIN pg_roles pr ON pp.proowner = pr.oid -- 소유자 정보 추가
    WHERE pl.lanname NOT IN ('c', 'internal')  -- 내부 언어 및 C 언어 제외
      AND pn.nspname NOT LIKE 'pg_%'       -- 시스템 스키마 제외
      AND pn.nspname <> 'information_schema' -- 정보 스키마 제외
    --AND pn.nspname IN ('testdb')          -- 보고 싶은 스키마 조건
    union all
     --트리거조회 
   SELECT r.rolname as owner,
          n.nspname AS schema_nm,
          'trigger' AS object_type,
          t.tgname AS object_name,
          c.relname AS table_name
     FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
     JOIN pg_catalog.pg_trigger t ON t.tgrelid = c.oid
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner -- 테이블 소유자 조회
    WHERE c.relkind = 'r'                        -- 테이블만 조회
      AND t.tgisinternal = false             -- 내부 트리거 제외
   -- AND n.nspname IN ('testdb')            -- 특정 스키마 조회
      AND c.relname NOT LIKE 'pg_%'          -- 시스템 테이블 제외
	  ) t
	where t.owner = 'user1' -- 소유자 조건
      and t.schema_nm = 'testdb' --스키마조건
      ;

 

더 많은 오브젝트들이 있지만 주로 사용하는 오브젝트 위주로 작성되었습니다.

 

 

오라클만 하다가 PG 하려니 오브젝트 하나 찾는것도 참 번거롭네요.

DBA_OBJECTS 하나면 되는 것들인데...