[PostgreSQL] routines ( function, procedure ..) 기본 권한 설정과 관리

postgresql 의 경우 객체가 생성될 때 일부 유형의 객체에 대해 기본 권한을 부여한다.
이 중 함수와 프로시저는 생성시 모든 사용자가 실행가능하여 기본 권한 설정을 관리해주어야 한다.

 

표 5.2. Summary of Access Privileges

Object Type	All Privileges	Default PUBLIC Privileges	psql Command
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	none	\dew+
FOREIGN SERVER	U	none	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	none
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE (and table-like objects)	arwdDxt	none	\dp
Table column	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

X = execute 

출처: <https://postgresql.kr/docs/current/ddl-priv.html>

 

 

PostgreSQL 함수 권한 관리 포인트

 

1. 함수 개별 권한 관리

함수 생성시 public 대상으로 함수에 대한 모든 권한을 박탈하고 특정 유저에게만 실행권한 부여 

하나의 트랜잭션으로 생성 및 관리

BEGIN;
  CREATE FUNCTION 함수명(타입 정의) ... SECURITY DEFINER;
  REVOKE ALL ON FUNCTION 함수명(타입 정의) FROM PUBLIC;
  GRANT EXECUTE ON FUNCTION 컬럼명(타입 정의) TO [계정명];
COMMIT;

 

2. 전체 함수 public 권한 제어

public 사용자에 대한 함수 권한 acl 자체를 revoke 하고 필요 계정에만 실행 권한 부여

alter default privileges revoke execute on functions from public; --함수 실행 권한 기본 acl 박탈 
alter default privileges grant execute on functions to group [유저명];

 

 

TEST

-- 테스트유저 생성
CREATE USER user_a WITH PASSWORD '#testuser1!';
CREATE USER user_b WITH PASSWORD '#testuser1!';

--기본적으로 postgresql에서는 database에 대한 connection 권한을 가짐 
--스키마 접근을 위한 usage 권한 부여 
GRANT USAGE ON SCHEMA testdb TO user_a;
GRANT USAGE ON SCHEMA testdb TO user_b;


--테스트 함수 생성 
CREATE OR REPLACE FUNCTION testdb.udf_func_test()
RETURNS SETOF integer
LANGUAGE sql
AS $function$
select 1 as t ;

$function$


--함수 권한 확인

select proname, pg_catalog.array_to_string(p.proacl, ',') AS permissions --null 이면 DEFAULT PRIVILEGES 의미 -> public에 대한 실행권한 가짐
from pg_proc p
where proname like  'udf_func_test%';

 

--함수 권한 확인

 

 

--user_b에만 함수 실행 권한 부여한 후 권한 재확인

grant execute on function  testdb.udf_func_test to user_b


select proname, pg_catalog.array_to_string(p.proacl, ',') AS permissions --null 이면 DEFAULT PRIVILEGES 의미 -> public에 대한 실행권한 가짐
from pg_proc p
where proname like  'udf_func_test%';

 

 

 

--user_a에서 함수 실행   
select testdb.udf_func_test()

함수에 대한 실행 권한을 따로 부여하지않아도 default로 모든 사용자에게 함수 실행권한이 부여되어 실행됨

 

-- 권한 회수 
REVOKE EXECUTE ON FUNCTION testdb.udf_func_test FROM PUBLIC;


--함수 권한 확인

select proname, pg_catalog.array_to_string(p.proacl, ',') AS permissions 
--null 이면 DEFAULT PRIVILEGES 의미 -> public에 대한 실행권한 가짐
from pg_proc p
where proname like  'udf_func_test%';

 

--user_a에서 함수 재실행   
select testdb.udf_func_test()