[PostgreSQL - DBA] 오브젝트 생성 시 기본 권한 자동 부여(alter default privileges)

DBA 프로젝트를 수행하거나, 사이드 프로젝트에서 DB 오브젝트를 관리하거나 할 때

특히 개발 단계에서는 오브젝트의 생성이 빈번하다 보니 권한을 매번 부여하는 것이 귀찮을 때가 있다.

이때 각 스키마별, 권한별, 오브젝트별 기본 권한을 자동으로 부여하게 할 수 있다.

먼저 기본 접근권한이 어떻게 되어 있는지 조회하는 방법이다.

1. psql에 접속하여

2. \ddp 입력

16 버전으로 업글시키고 나서 psql은 귀찮아서 그냥 15쓰는 중..

뭐 내가 준 옵션이 없으니 당연히 아무 것도 없을 거다.

이제 그럼 먼저 1. 계정을 생성 후 권한을 부여하고 2. 그 계정에 "테이블"에 대한 arwdDxt(SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER) 까지 줘보자.

사실 저건 그냥 ALL 권한을 주면 저렇게 7개 권한이 들어간다. 프로젝트 에서는 유저에 TRUNCATE 등의 권한까지는 줄 필요 없으니 확인하고 작업하자.

---

1. 계정 조회

먼저 계정이 무엇이 있는지 조회 해보자.

오늘은 psql로 하고 싶은 날이니 psql로 해보겠다.

\du를 입력하여 계정 리스트를 조회한다.

(또는 수퍼유저 권한이 필요한 SELECT * FROM PG_SHADOW;  이나 수퍼유저 아니라도 볼 수 있는 SELECT * FROM PG_USER; 로도 조회 가능하다)

Role	Desc.
슈퍼유저(SUPERUSER)	USER 생성, 권한을 부여해주는 USER
롤 만들기(CREATE ROLE)	USER가 새로운 ROLE을 정의하는 기능을 생성
DB 만들기(CREATE DB)	USER가 DB를 생성하는 권한을 부여하는 기능
복재(REPLICATION)	USER가 DB를 실시간으로 복사하는 기능

 

DB 설치 후 계정 생성한 것이 없으니 뭐 postgres 밖에 없겠지. 그럼 계정을 임시로 생성해보자.

 

2. 계정 생성

OPTION	DEFAULT	OPTION
SUPERUSER	NOSUPERUSER	해당 USER에게 SUPERUSER권한을 줌
CREATEDB	NOCREATEDB	DATABASE를 생성하는 권한을 정의
CREATEUSER	NOCREATEUSER	스스로 새로운 유저를 생성하는 권한을 정의
INHERIT	NOINHERIT	DATABASE의 권한을 다른 구성원들에게 상속하는 역할
LOGIN	NOLOGIN	USER가 LOGIN하는 권한을 부여
CONNECTION LIMIT connlimit	-1	로그인 시 동시연결을 지원하는 기능
[ENCRYPTED] PASSWORD 'password'	[UNCRYPTED]	'password' 를 입력하고 인증이 필요 없는 경우 옵션 생략이 가능

출처 : https://kwomy.tistory.com/9

 

계정 생성 옵션은 위 표와 같다. 

위의 옵션을 추가 하지 않으면 기본  옵션은 NO 이다.

그럼 아래 명령어로 계정을 생성해보자.

CREATE USER [user_name] [ [WITH] option [ . . . ] ];

난 별다른 옵션을 주지 않았다

\du로 계정을 조회해 보자.

별다른 옵션 없는 temp_user 유저가 생성되었다~

 

...앗 비밀번호를 지정 안했다.

이런 김에 유저 정보 변경을 해보자.

난 비밀번호만 바꾸면 되니.. 문법은 아래와 같다. 

alter user temp_user with password '1234';

짠..

---

이제 생성된 계정에 기본 권한을 부여해보자.

기본 권한을 생성하는 쿼리는 아래와 같다.

ALTER DEFAULT PRIVILEGES [IN SCHEMA [schema]] GRANT [ALL PRIVILEGES] ON TABLES TO [role];

문법에 대해서 궁금하다면.. 항상 들어가는 https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html..(또는 버전은 늦게 나오지만 한글번역된 https://www.postgresql.kr/..) 

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTIONS | ROUTINES }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
    ON SCHEMAS
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTIONS | ROUTINES }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | CREATE | ALL [ PRIVILEGES ] }
    ON SCHEMAS
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

 

설명

ALTER DEFAULT PRIVILEGES 명령은 앞으로 만들어질 객체에 기본적으로 적용할 접근 권한을 미리 지정하는 일을 한다. (이 명령은 기존 이미 있는 객체들에 대한 접근 권한을 바꾸는 일은 하지 않는다. 현재, 스키마, 테이블 (뷰와 외부 테이블 포함), 시퀀스, 함수, 자료형 (도메인 포함) 객체들에 대해서만 사용할 수 있다.

이 접근 권한 설정은 현재 접속한 사용자나, 명령에서 지정한 롤(또는 소속원)이 만들 객체들에 대해서만 적용된다. 접근 권한은 현재 접속한 데이터베이스 소속 모든 객체에 대해서 지정할 수도 있고, 지정한 특정 스키마 안의 모든 객체로 한정할 수도 있다. 스키마별로 지정한 기본 접근 권한은 특정 객체 종류에 지정한 데이터베이스 단위 기본 접근 권한이 있다면 그 권한에 더해진다.

GRANT 명령에서 설명한 것처럼, 어떤 객체에 대한 기본 접근 권한은 일반적으로 그 객체의 소유주에게는 모든 권한을 부여하고, PUBLIC (아무나)에게 몇몇 권한을 부여할 수도 있다. (예, public 스키마에 함수를 만들면, 해당 데이터베이스를 접근하는 모든 사용자는 해당 함수를 실행할 수 있다. - 옮긴이) 이 때, 이런 기본 접근 권한 정책을 바꾸고자 한다면, ALTER DEFAULT PRIVILEGES 명령을 이용하면 된다.

---

그래서 아까 넣으려고 했던 권한이...... 7가지 전체였지 그러면 문법은 아래처럼 되겠군

---

.. 하기 전..에 스키마를 생성을 안했었네.. 

temp_schema 라는 이름으로 스키마를 하나 생성하자..

 생성된 스키마를 조회하는 문법은 \dn 이고, 권한같은 추가 정보도 보고싶으면 +를 붙이면 된다..

---

temp_user 스키마에서 "TABLE"을 생성할 때 마다 모든 권한을 temp_user와 postgres에게 주는 기본 설정을 해보자.

ALTER DEFAULT PRIVILEGES IN SCHEMA temp_user GRANT ALL ON TABLES TO temp_user, postgres;

그후에 기본 권한 설정을 조회(\ddp) 하면~

요렇게 생기는 것을 볼 수 있다.

그럼 이제 temp_schema에서 테이블을 생성하고 권한 조회를 해보자.

temp라는 테이블을 temp_schema에 생성항 후에 권한을 조회해 봤다.

postgres와 temp_user에게 7가지 권한이 모두 부여되 있는 것을 확인할 수 있다~