Last Updated on 9월 20, 2026 by Jade(정현호)
안녕하세요 이번글은 PostgreSQL에서 계정 관리 및 권한에 대한 내용을 정리해보려고 합니다.
PostgreSQL은 데이터베이스 안에 스키마라는 계층이 한 단계 더 있는 구조입니다. 이 구조 때문에 다른 RDBMS에 비해 권한을 제어하고 설정할 수 있는 방식이 더 다양하고, 스키마와 오브젝트의 소유권이 서로 얽히는 조합도 여러 가지로 나올 수 있습니다.
그만큼 실제로 부여된 권한이 어느 단계에서 어떻게 걸려 있는지 파악하는 데도 다양한 경우의 수가 존재합니다. 이 글에서는 유저와 Role 생성부터 시작해서, 데이터베이스와 스키마 구조에서 나오는 다양한 권한 설정 방식과 권한 파악 시나리오들을 정리합니다.
Contents
유저 생성과 삭제
PostgreSQL에서는 User와 Role 이 사실 같은 개념입니다. 차이가 있다면 로그인이 가능하다면 User, 불가능하다면 Role 로 보면 됩니다. PostgreSQL 인스턴스(클러스터) 전체에 공통적으로 존재하는 Role이며, MongoDB와 달리 데이터베이스마다 별도로 존재하지 않습니다.
다음과 같이 login 옵션을 통해서 접속 가능한 유저로 생성을 합니다.
create user testapp with login password 'testapp';
PostgreSQL에서의 유저는 접속 권한을 가진 Role 이기 때문입니다.
이미 생성된 Role에 로그인 권한을 삭제하려면 다음과 같이 수행합니다.
ALTER ROLE <role name></role> WITH NOLOGIN;
유저 또는 Role 조회시는 \du 메타 명령어나 pg_roles 뷰, pg_shadow 뷰를 사용하면 됩니다. pg_shadow 는 PostgreSQL 8.1 이전에 존재하던 catalog를 에뮬레이션하기 위해 backward compatibility 목적으로 남아 있는 view 입니다.
select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil |
----------+----------+-------------+----------+---------+--------------+----------+----------+
postgres | 10 | t | t | t | t | ******** | |
testapp | 16942 | f | f | f | f | ******** | |
select usename,usesuper, usecreatedb from pg_shadow;
usename | usesuper | usecreatedb
----------+----------+-------------
postgres | t | t
testapp | f | f
# Role list 또는 유저 목록
\du
List of roles
Role name | Attributes | Description
----------------+------------------------------------------------------------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
testapp | |
유저(Role) 생성시 사용 가능한 OPTION은 다음과 같습니다.
- SUPERUSER | NOSUPERUSER : Superuser 여부. 기본값은 NOSUPERUSER
- CREATEDB | NOCREATEDB : DB생성 권한 부여 여부. 기본값은 권한 없음
- CREATEUSER | NOCREATEUSER : User생성 권한 부여 여부. 기본값은 권한 없음
- PASSWORD 'password' : Password 설정
유저 삭제는 다음과 같이 수행합니다.
drop user testapp;
ERROR: role "testapp" cannot be dropped because some objects depend on it 이 에러는 testapp Role이 아직 여러 객체의 Owner이거나 권한 설정의 기준 Role로 사용되고 있어서 바로 "DROP ROLE testapp" 가 안 된다는 뜻입니다.
이럴 경우 다음과 같이 REASSIGN OWNED와 DROP OWNED 명령어를 사용하여 해당 Role이 소유한 객체와 권한을 정리한 후에 DROP ROLE을 수행해야 합니다.
REASSIGN OWNED BY svc TO postgres; DROP OWNED BY testapp;
권한의 단계
PostgreSQL에서는 유저가 테이블을 엑세스하기 위해서는 여러 단계의 권한이 필요합니다.
테이블을 사용하기 위해서는 먼저 데이터베이스 수준의 권한을 가져야하며, 그 다음 스키마 수준의 권한을 가져야 합니다. 그 다음 스키마내의 테이블이나 뷰, 시퀀스 등에 대한 권한을 가져야 합니다.
DB User
↓
Database Level Privileges
↓
Schema Level Privileges
↓
Table/View/Sequence/Function Level Privileges
여기서 또 중요한 점은 PostgreSQL의 계정(Role/User)은 인스턴스, 즉 클러스터 전체에서 공통으로 존재하고 관리된다는 것입니다.
반면 권한은 Database, Schema, Table 등의 객체 단위로 부여되며, 동일한 계정이라도 Database마다 서로 다른 권한을 가질 수 있습니다.
따라서 계정은 클러스터 공통이지만, 실제 객체 권한은 Database별로 별도로 관리되고 조회됩니다. 이에 대한 내용은 아래에서 자세히 다루도록 하겠습니다.
데이터베이스 생성
PostgreSQL에서는 데이터베이스에 대한 접속 권한인 CONNECT 권한은 PUBLIC으로 설정되어있어서 기본적으로 데이터베이스는 접속이 가능하고 별도의 권한 부여가 필요는 없습니다. 즉, 기본적으로 누구나 데이터베이스에 접속할 수 있습니다.
그 외에도 CREATE 와 TEMPORARY(또는 TEMP) 권한이 있습니다. TEMPORARY(또는 TEMP) 권한도 CONNECT와 마찬가지로 기본이 PUBLIC이라 별도의 권한 부여 없이 누구나 사용할 수 있습니다.
- CREATE: 해당 Database 안에 새로운 Schema 생성, Publication 생성,신뢰된(trusted) 익스텐션 설치 권한(기본적으로 PUBLIC에 부여되지 않아 별도의 GRANT가 필요합니다)
- TEMPORARY(또는 TEMP): 해당 Database에서 Temporary Table 생성 가능
다음과 같이 template0 템플릿 데이터베이스를 이용해서 Encoding과 lc_collate 옵션을 각각 설정하여 생성합니다. template0 를 지정하지 않으면 기본적으로 template1이 되고 template1 은 initdb 명령어로 클러스터를 생성시에 지정된 Encoding과 lc_collate 만 사용가능합니다.
관련된 내용은 다음 포스팅을 참조합니다.
예시
create database <데이터베이스명> encoding 'UTF8' lc_collate 'C' template template0;
데이터베이스를 생성합니다.
CREATE DATABASE test TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';
데이터베이스 목록은 \l 또는 pg_database 시스템 테이블을 통해서 조회할 수 있습니다.
\l -- 또는 \list
데이터베이스에 대해서 PUBLIC에 CONNECT 권한이 부여되어 있기 때문에 모든 데이터베이스에 접속할 수 있고, 반대로 데이터베이스 레벨에서 접근을 제어하기 위해서는 다음과 같이 PUBLIC 권한을 회수합니다.
REVOKE CONNECT ON DATABASE test333 FROM PUBLIC; \c test333 connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: permission denied for database "test333" DETAIL: User does not have CONNECT privilege. Previous connection kept
PUBLIC에서 회수하고 해당 데이터베이스를 사용할 실제 사용자에게만(특정 사용자에게만) 권한 부여는 다음과 같이 수행합니다.
GRANT CONNECT, TEMPORARY ON DATABASE test333 TO testapp;
스키마
PostgreSQL에서 스키마는 매우 중요한 개념입니다. 스키마는 테이블, 인덱스, 함수, 뷰, 시퀀스 등의 오브젝트를 논리적으로 구분하고 관리하기 위한 공간입니다. 스키마 생성 시 AUTHORIZATION 옵션을 사용해 소유자를 지정할 수 있으며, 별도로 지정하지 않으면 스키마를 생성한 유저가 소유자가 됩니다.
스키마 권한은 주로 CREATE와 USAGE로 구성됩니다.
CREATE 권한은 해당 스키마 안에 테이블, 함수, 시퀀스 등의 오브젝트를 생성할 수 있는 권한이고, USAGE 권한은 해당 스키마 내부 오브젝트의 이름을 참조할 수 있도록 허용하는 권한입니다.
다만 USAGE 권한만으로 테이블을 SELECT, INSERT, UPDATE, DELETE할 수 있는 것은 아니며, 실제 오브젝트 접근을 위해서는 해당 테이블이나 함수 등에 대한 개별 권한이 추가로 필요합니다. ALL은 스키마에 대해 CREATE와 USAGE 권한을 모두 부여하는 의미입니다.
스키마 생성
다음은 스키마 관련 명령어 예시입니다.
-- 생성 명령어를 수행한 유저의 소유로 스키마를 생성 CREATE SCHEMA testdb; -- app1 계정의 소유자로 해서 db1 스키마를 생성 CREATE SCHEMA db1 AUTHORIZATION app1; -- app2 계정의 소유자로 해서 db2 스키마를 생성 CREATE SCHEMA db2 AUTHORIZATION app2; -- 스키마 리스트 및 정보 확인 \dn+
생성된 스키마에 대해서 다른 유저에게 소유권을 변경할 수 있습니다.
ALTER SCHEMA 스키마이름 OWNER TO 새소유자이름;
스키마의 소유자는 해당 스키마에 대해 CREATE, USAGE 권한을 가집니다. 스키마를 생성후 확인해보도록 하겠습니다.
-- 먼저 생성한 데이터베이스로 접속합니다.
\c test
-- 스키마를 생성합니다.
CREATE SCHEMA db1;
\dn+
List of schemas
Name | Owner | Access privileges | Description
--------+-------------------+----------------------------------------+------------------------
db1 | postgres | |
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
생성한 스키마에 대해서 권한은 다음과 같이 부여할 수 있습니다.
-- db1 스키마내에서 오브젝트 생성 권한
GRANT CREATE ON SCHEMA db1 TO testapp;
-- db1 스키마내에서 오브젝트 사용 권한
GRANT USAGE ON SCHEMA db1 TO testapp;
\dn+
Name | Owner | Access privileges | Description
--------+-------------------+----------------------------------------+------------------------
db1 | postgres | postgres=UC/postgres +|
| | testapp=UC/postgres |
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
-- db1 스키마내에서 모든 권한
-- GRANT ALL ON SCHEMA db1 TO testapp;
CREATE 와 USAGE 권한을 부여하고 \dn+ 명령어 조회하면 UC 권한이 표시되는데 U는 USAGE 권한을 의미하고, C는 CREATE 권한을 의미합니다. "GRANT ALL ON SCHEMA" 로 권한을 부여해도 동일하게 UC 로 표시됩니다.
권한 회수는 다음과 같이 수행할 수 있습니다.
-- 권한 회수 예시 revoke create on schema <스키마명> from <롤명>; revoke usage on schema <스키마명> from <롤명>; revoke all on schema <스키마명> from <롤명>;
스키마 삭제는 다음과 같이 수행할 수 있습니다.
DROP SCHEMA 스키마이름;
Public 스키마
스키마 목록을 보면 public 스키마가 데이터베이스내에 기본적으로 포함되어 있습니다. public 스키마는 데이터베이스 생성 시 자동으로 생성되는 스키마 중 하나 입니다.
14버전까지는 create 권한이 없어도 모든 유저가 public 스키마 내에 오브젝트를 생성할 수 있었습니다. 하지만 15버전 부터는 보안 강화를 위해서 데이터베이스 소유자만 public 스키마에 오브젝트를 생성할 수 있도록 변경되었습니다.
set role testapp;
CREATE TABLE public.test_public (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE public.test_public (
^
만약 환경이 14버전 이하라면 다음의 명령어를 이용해서 public 권한을 회수하는 것이 PUBLIC 스키마에 대한 권한 관리 및 운영 측면에서 바람직할 수 있습니다.
revoke all on schema public from public;
search_path
search_path는 PostgreSQL에서 여러 스키마가 있을때 어느 스키마의 오브젝트에 대해서 access 하기 위한 경로를 의미합니다.
이 경로는 다음과 같이 별도로 스키마명을 붙이지 않고 사용할 때와 \dt+ 와 같이 테이블 또는 오브젝트 정보를 조회할 때도 사용됩니다.
스키마명을 붙이지 않고 조회했을때 search_path 설정한 스키마 순서에 따라 테이블을 검색합니다.
search_path 는 기본값이 "$user",public 입니다. 여기서 "$user"는 현재 접속한 데이터베이스 유저와 동일한 이름의 스키마를 의미합니다. 따라서 데이터베이스 접속 유저와 동일한 이름의 스키마(존재할 경우)를 검색하고 그 다음 public 스키마를 검색한다.
search_path 가 설정되지 않은 상태에서는 테이블을 조회한다면 다음과 같이 조회합니다.
select * from db1.table1;
다음과 같이 search_path 를 설정시에는 스키마명을 붙이지 않고 가능합니다.
SET search_path to db1,db2,public; select * from table1;
set search_path to 명령어는 세션 단위의 명령어로 해당 세션에만 유효하며 재접속시 다시 명령어를 수행합니다.
다음의 명령어를 통해서 현재 설정된 search_path 정보를 확인할 수 있습니다.
show search_path;
데이터베이스 레벨 또는 유저레벨로 설정할 수 있습니다.
alter database <데이터베이스명> set search_path='스키마명1, 스키마명2';
유저의 속성 값으로 설정할수 있습니다.
ALTER USER 사용자명 SET search_path = 스키마1, 스키마2, ...;
위와 같이 유저 속성에서 설정하는 것이 사용 측면에서 편리합니다.
SET search_path to db1,db2,public;
만약 search_path 설정된 스키마에는 테이블이 없을 경우 다음과 같이 \dt+ 명령어를 수행하면 결과가 나오지 않게 됩니다.
psql> \dt+ Did not find any relations.
스키마와 테이블 소유자의 관계성 이해
Oracle과 달리 PostgreSQL에서는 스키마내에서 테이블을 생성한 유저가 해당 테이블의 소유자가 됩니다. 오라클은 테이블을 생성한 유저와 무관하게 스키마의 소유자가 테이블의 소유자가 되는 시스템입니다.
예를 들어서 오라클에서는 APP 스키마내에서 SYS 또는 DBA 유저가 테이블을 생성하더라도, 해당 테이블의 소유자는 APP 스키마가 소유자가 됩니다. 하지만 PostgreSQL은 테이블을 생성한 유저가 테이블의 소유자가 되는 시스템입니다.
PostgreSQL에서는 testapp 유저가 테이블을 생성하면 testapp 유저가 해당 테이블의 소유자가 되고, postgres 유저가 생성하면 postgres 유저가 해당 테이블의 소유자가 됩니다.
다음과 같이 testapp 스키마내에 postgres(관리자 계정)이 테이블을 생성을 한다면 다음과같이 문제점이 발생할 수 있습니다.
\c test
set role postgres;
-- 스키마 소유권은 testapp
create schema testapp_schema authorization testapp;
-- 테이블 소유권은 postgres
create table testapp_schema.test_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
\dt+ testapp_schema.test_table
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
----------------+------------+-------+----------+-------------+---------------+---------+-------------
testapp_schema | test_table | table | postgres | permanent | heap | 0 bytes |
set role testapp;
select * from testapp_schema.test_table;
ERROR: permission denied for table test_table <!-- 권한 에러 발생
그래서 스키마 및 테이블에 대해서 생성 및 소유권에 대한 운영 정책에 대한 디자인을 명확하게 수립할 필요가 있습니다. 소유권 변경은 ALTER TABLE 명령어를 사용하여 수행할 수 있습니다.
set role postgres; alter table testapp_schema.test_table owner to testapp; set role testapp; select * from testapp_schema.test_table; id | name ----+------ (0 rows)
테이블 또는 다른 종류의 객체의 소유권에 대한 부분은 관리자 계정으로 진행할 경우에도 문제가 발생할 수 있는 케이스가 있습니다.
예를 들어 여러 DBA가 업무를 수행하는 환경에서 DBA는 각각 관리자 권한을 부여받은 개인계정을 사용한다고 가정해봅니다.
A라는 DBA가 자신의 dba1 이라는 관리자 권한의 개인계정으로 테이블 생성합니다.
SELECT current_user;
current_user
--------------
dba1
(1 row)
create table test_schema.test_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
시간이 지나서 A라는 DBA가 퇴사 또는 보직변경 등으로 dba1 계정을 삭제해야하는 경우가 발생하여 계정을 삭제를 진행합니다.
다음과 같이 다른 관리자 계정에서 dba1 계정을 삭제를 진행합니다.
SELECT current_user; current_user ------------- postgres (1 row) drop user dba1; ERROR: role "dba1" cannot be dropped because some objects depend on it DETAIL: owner of sequence test_schema.test_table_id_seq owner of table test_schema.test_table
위와 같이 계정은 dba1이 소유한 테이블,시퀀스 등의 객체가 남아 있으면 Role(계정)을 삭제할 수 없습니다. "REASSIGN OWNED BY" 명령어를 사용하면 새로운 DBA 및 관리자 계정으로 소유권을 이전할수는 있습니다.
다만 다중 데이터베이스 환경인 경우 어떤 데이터베이스에 객체가 생성되어있는지 확인이 어렵거나 여러 데이터베이스에 있을 경우 여러 데이터베이스에 각각 접속하여 "REASSIGN OWNED BY" 명령어를 수행해서 소유권을 이전해야 계정(Role)을 삭제할 수 있습니다.
이러한 상황은 관리적 비용이 커짐과 함께 불필요한 관리적인 요소가 동반될 수 있습니다. 그러므로 PostgreSQL 에서는 스키마와 객체 생성에 대한 소유권 설계에 대해서 조금 더 세밀하게 디자인해야 할 필요성이 있습니다.
위의 사례를 방지하거나 효율적으로 운영할 수 있는 운영 정책이나 방법은 다양하게 있을 수 있습니다.
다음과 같이 항상 "set role" 명령어를 통해 특정 대표 관리자 계정(role)으로 역할 전환 후에 오브젝트 생성을 하는 방식도 한가지 방법이 될 수 있습니다.
SELECT current_user;
current_user
--------------
dba1
(1 row)
-- role 변경
set role postgres;
SET
-- 테이블 생성
create table test_schema.test_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE
set search_path to test_schema;
-- 테이블 생성 정보 확인
\dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
-------------+------------+-------+----------+-------------+---------------+---------+-------------
test_schema | test_table | table | postgres | permanent | heap | 0 bytes |
(1 row)
임시성 스키마는 삭제하도록 하겠습니다.
drop schema testapp_schema cascade;
오브젝트 권한과 DEFAULT 권한
다음 단계로 오브젝트 레벨 권한과 DEFAULT 권한에 대해서 알아보겠습니다.
오브젝트별 권한
PostgreSQL에는 여러 종류의 오브젝트가 있으며 그에 따라서 오브젝트 별로 권한을 부여할 수 있습니다. 오브젝트 레벨의 권한은 테이블(및 뷰), 시퀀스, 함수(및 프로시저) 단위로 부여할 수 있습니다.
이때 개별 오브젝트 하나씩 권한을 부여할 수도 있고, 스키마내에 존재하는 오브젝트 유형별로 한꺼번에 권한을 부여할 수도 있습니다.
오브젝트 단위로 롤에 권한을 부여하는 방법은 다음과 같습니다. 뷰 권한은 테이블 권한 부여 명령어에 포함되고, 프로시저 권한은 함수 권한 부여 명령어에 포함됩니다.
구문
grant <all | 권한> on table <테이블명> to <롤명>; grant <all | 권한> on sequence <시퀀스명> to <롤명>; grant <all | 권한> on function <함수명> to <롤명>;
오브젝트 유형별 부여가능한 권한은 다음과 같습니다.
- 테이블 권한: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN
- 시퀀스 권한: USAGE, SELECT
- 함수 권한: EXECUTE
오브젝트 레벨에서의 권한 부여는 개별 오브젝트 단위로 권한을 부여할 수도 있고, 스키마 단위로 권한을 부여할 수도 있습니다.
예시
-- 특정 테이블에 조회 권한 부여 GRANT SELECT ON TABLE <스키마명>.<테이블명> TO <롤명>; -- 특정 테이블에 DML 권한 부여 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE <스키마명>.<테이블명> TO <롤명>; -- 특정 테이블에 전체 권한 부여 GRANT ALL PRIVILEGES ON TABLE <스키마명>.<테이블명> TO <롤명>; -- 스키마 내 현재 존재하는 모든 테이블에 조회 권한 부여 GRANT SELECT ON ALL TABLES IN SCHEMA <스키마명> TO <롤명>; -- 스키마 내 현재 존재하는 모든 테이블에 DML 권한 부여 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <스키마명> TO <롤명>; -- 스키마 내 현재 존재하는 모든 테이블에 전체 권한 부여 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <스키마명> TO <롤명>; -- 특정 시퀀스에 USAGE 권한 부여 GRANT USAGE ON SEQUENCE <스키마명>.<시퀀스명> TO <롤명>; -- 특정 시퀀스에 전체 권한 부여 GRANT ALL PRIVILEGES ON SEQUENCE <스키마명>.<시퀀스명> TO <롤명>; -- 스키마 내 현재 존재하는 모든 시퀀스에 USAGE 권한 부여 GRANT USAGE ON ALL SEQUENCES IN SCHEMA <스키마명> TO <롤명>; -- 스키마 내 현재 존재하는 모든 시퀀스에 전체 권한 부여 GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <스키마명> TO <롤명>; -- 특정 함수에 실행 권한 부여 GRANT EXECUTE ON FUNCTION <스키마명>.<함수명>(<인자타입>) TO <롤명>; -- 특정 프로시저에 실행 권한 부여 GRANT EXECUTE ON PROCEDURE <스키마명>.<프로시저명>(<인자타입>) TO <롤명>; -- 스키마 내 현재 존재하는 모든 함수에 실행 권한 부여 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <스키마명> TO <롤명>; -- 스키마 내 현재 존재하는 모든 프로시저에 실행 권한 부여 GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA <스키마명> TO <롤명>; -- 함수와 프로시저를 모두 포함한 모든 routine에 실행 권한 부여 GRANT EXECUTE ON ALL ROUTINES IN SCHEMA <스키마명> TO <롤명>;
MAINTAIN 권한은 PostgreSQL 17부터 새롭게 추가된 권한으로, VACUUM, ANALYZE, REINDEX, CLUSTER, REFRESH MATERIALIZED VIEW, LOCK TABLE 등의 유지보수 작업을 수행할 수 있도록 합니다.
이전 버전에서는 이러한 작업을 수행하려면 주로 해당 객체의 Owner 또는 Superuser 권한이 필요했지만, PostgreSQL 17부터는 객체의 소유권을 부여하지 않고도 MAINTAIN 권한만 별도로 부여하여 유지보수 작업을 수행하도록 권한을 분리할 수 있습니다.
위와 같이 오브젝트 단위로 권한을 부여하였다면 다음 생성될 오브젝트에도 동일하게 권한을 부여하기 위해서 DEFAULT 권한을 설정해두는 것이 좋습니다.
DEFAULT 권한
DEFAULT 권한은 앞으로 생성될 오브젝트에 대해 자동으로 적용되는 기본 권한을 의미합니다. DEFAULT 권한을 설정해두면 오브젝트 생성할 때마다 별도로 권한을 부여할 필요가 없으므로 권한 관리 업무가 매우 수월해집니다. 그래서 사실상 권한 부여와 DEFAULT 권한 설정은 함께 진행하게 됩니다.
구문
alter default privileges in schema <스키마명> grant <권한> on <오브젝트유형> to <롤명/유저명>;
설정된 DEFAULT 권한은 \ddp 명령어로 확인할 수 있습니다.
\ddp
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------------+-------+-----------------------
postgres | db1 | table | testapp=arwd/postgres
엑세스 권한은 <권한을 부여받은 롤/또는 계정명>=<권한>/권한을 부여한 유저> 형식으로 표기됩니다.
X는 EXECUTE, U는 USAGE , a(append) 는 INSERT, r(read)은 SELECT, w(write)는 UPDATE, d(delete) 는 DELETE, t는 TRUNCATE, D는 REFERENCES, x는 TRIGGER 권한을 의미합니다.
권한 관리
권한 부여 방식은 크게 권한 자체를 직접 유저에게 부여하는 방식과, 롤(Role)에 권한을 부여한 후 유저에게 롤을 부여하는 방식으로 나눌 수 있습니다.
유저에 권한을 직접 부여하기
권한을 부여 하고 관리하는 방식중에서 권한을 유저에게 직접 부여하는 방식부터 살펴보도록 하겠습니다.
권한 부여 및 default 권한 설정
생성한 유저 testapp 유저에 대해서 권한을 부여하고 확인하는 과정을 진행하도록 하겠습니다.
현재 데이터베이스 및 스키마 권한 상황입니다.
\c test
\dn+
List of schemas
Name | Owner | Access privileges | Description
--------+-------------------+----------------------------------------+------------------------
db1 | postgres | postgres=UC/postgres +|
| | testapp=UC/postgres |
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
다음은 db1 스키마 내의 모든 테이블에 대해 SELECT, INSERT, UPDATE, DELETE 권한을 부여하는 예제입니다.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA db1 TO testapp;
테스트 테이블 생성합니다.
CREATE TABLE db1.test_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
다만 이후에 새로 생성되는 테이블에 대해서는 권한이 자동으로 부여되지 않으므로, 다음과 같이 에러가 발생합니다. 그래서 권한 부여와 DEFAULT PRIVILEGES는 함께 설정해주어야 합니다.
set role testapp; select * from db1.test_table ; ERROR: permission denied for table test_table
다음과 같이 의도한 권한을 기본값으로 설정해주어야 합니다.
set role postgres; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA db1 TO testapp; ALTER DEFAULT PRIVILEGES IN SCHEMA db1 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testapp;
테스트 테이블 생성합니다.
set role postgres;
CREATE TABLE db1.test_table2 (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
testapp 유저로 조회를 시도합니다. 정상적으로 조회되는 것을 확인할 수 있습니다.
set role testapp; select * from db1.test_table2 ; id | name ----+------ (0 rows) select * from db1.test_table; id | name ----+------ (0 rows)
권한 부여 내역 조회 및 카탈로그 조회의 한계
직접 권한을 부여하면 다음과 같은 명령어들로 권한 내역을 확인할 수 있습니다.
-- 특정 유저가 가진 권한 조회
\du testapp
List of roles
Role name | Attributes
-----------+------------
testapp |
\dp testapp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)
-- 특정 테이블의 권한 조회
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------------+-------+----------------------------+-------------------+----------
db1 | test_table | table | postgres=arwdDxtm/postgres+| |
| | | testapp=arwd/postgres | |
다만, 이러한 명령어들로는 유저가 실제로 어떤 권한을 가지고 있는지, 특히 상속된 권한이나 기본값으로 설정된 권한까지 포함한 실효 권한을 한눈에 파악하기 어렵습니다.
그래서 이처럼 직접 권한을 부여하는 방식으로 진행시 psql 메타 명령어나 단순 카탈로그 조회만으로는 상세 권한 현황을 한눈에 파악하기 어렵다는 결정적인 단점이 드러납니다.
더욱이 스키마 생성 시 스키마 Owner를 일반 유저로 지정해 두었거나 소유권이 교차된 경우에는 구조 파악이 더더욱 난해해집니다.
\dn+나 \z 같은 CLI 메타 명령어는 권한의 '범위'를 한눈에 보여주지 못합니다. 권한이 DB, 스키마, 테이블 중 어느 레벨에서 부여되었는지 파악하기 어렵고, 스키마 오너십까지 얽히면 카탈로그 조회가 더욱 난해해집니다.
결국 개별 유저의 정확한 실효 권한을 확인하기 위해서는 다음과 같이 수많은 시스템 딕셔너리 뷰와 aclexplode() 같은 특수 함수를 동원하여 여러 번 쿼리를 따로 돌려야만 하는 복잡함이 발생하게 됩니다.
-- 1. 유저 계정 기본 속성 및 시스템 권한 조회
SELECT
rolname,
rolsuper,
rolinherit,
rolcreaterole,
rolcreatedb,
rolcanlogin,
rolreplication,
rolbypassrls
FROM pg_roles
WHERE rolname = 'testapp';
-- 2. 데이터베이스(Database) 레벨 접근 권한 문자열 조회
SELECT
datname,
datacl
FROM pg_database
WHERE datacl::text LIKE '%testapp=%'
ORDER BY datname;
-- 3. 데이터베이스 레벨 상세 부여 권한 분해 조회
SELECT
d.datname,
acl.grantee::regrole AS grantee_name,
acl.privilege_type,
acl.is_grantable
FROM pg_database d
CROSS JOIN LATERAL aclexplode(d.datacl) acl
WHERE acl.grantee = (SELECT oid FROM pg_roles WHERE rolname = 'testapp')
ORDER BY d.datname, acl.privilege_type;
-- 4. 스키마(Schema) 레벨 권한 내역 유무 조회
SELECT
nspname,
nspacl
FROM pg_namespace
WHERE nspacl IS NOT NULL
ORDER BY nspname;
-- 5. 특정 유저에게 직접 부여된 스키마 상세 권한 조회
SELECT
n.nspname AS schema_name,
acl.privilege_type,
acl.is_grantable
FROM pg_namespace n
CROSS JOIN LATERAL aclexplode(n.nspacl) acl
WHERE acl.grantee = (SELECT oid FROM pg_roles WHERE rolname = 'testapp')
ORDER BY n.nspname, acl.privilege_type;
-- 6. 특정 유저에게 직접 부여된 테이블/뷰(Table/View) 상세 권한 조회
SELECT
n.nspname AS schema_name,
c.relname AS object_name,
c.relkind,
acl.privilege_type,
acl.is_grantable
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL aclexplode(c.relacl) acl
WHERE acl.grantee = (SELECT oid FROM pg_roles WHERE rolname = 'testapp')
ORDER BY n.nspname, c.relname, acl.privilege_type;
보시다시피 단순한 권한 하나 조회하는 데만 카탈로그 뷰를 몇 번씩 쪼개서 조율해야 하는 번거로움이 발생합니다.
그렇다면 이렇게 복잡하고 파편화된 '유저 직접 권한 부여 방식'이 실제 DB 운영 현장에서 어떤 혼란을 만들어내는지, 자주 마주치는 대표적인 케이스들을 정리해 보겠습니다.
직접 권한 부여 방식과 7가지 시나리오
7가지의 시나리오로 직접 권한 부여 방식의 다양성 및 복잡성을 확인해 보겠습니다.
시나리오 1. 스키마/객체 일체형 소유 패턴: 스키마를 만들면서 owner를 일반유저 설정
set role postgres;
CREATE SCHEMA schema_case1 AUTHORIZATION testapp;
-- 일반 유저로 전환 및 테이블 생성, 데이터 입력
set role testapp;
create table schema_case1.tb_case1 (
id serial primary key,
name text
);
insert into schema_case1.tb_case1 (name) values ('test_name');
-- 조회(일반유저)
SELECT * FROM schema_case1.tb_case1;
id | name
----+-----------
1 | test_name
시나리오 2. 관리자 소유 및 스키마 레벨 권한 위임 패턴: 스키마의 소유권은 관리자이고 테이블과 같은 오브젝트는 관리자 계정으로 생성(소유), 스키마 레벨에서 권한을 일반 유저에게 부여
set role postgres;
CREATE SCHEMA schema_case2 AUTHORIZATION postgres;
create table schema_case2.tb_case2 (
id serial primary key,
name text
);
insert into schema_case2.tb_case2 (name) values ('test_name');
-- 일반 유저에게 권한 부여
GRANT USAGE ON SCHEMA schema_case2 TO testapp;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_case2 TO testapp;
alter default privileges in schema schema_case2 grant select on tables to testapp;
-- 일반 유저로 변경 및 조회
set role testapp;
SELECT * FROM schema_case2.tb_case2;
id | name
----+-----------
1 | test_name
시나리오 3. 사용자 객체 생성 권한 위임 패턴: 스키마 오너는 관리자(postgres)지만, testapp 유저에게 CREATE 권한을 주어 testapp 유저가 직접 테이블을 생성하는 구조 (소유자 불일치)
set role postgres;
CREATE SCHEMA schema_case3 AUTHORIZATION postgres;
GRANT USAGE,CREATE ON SCHEMA schema_case3 TO testapp;
-- 일반 유저로 전환 및 테이블 생성, 데이터 입력
set role testapp;
create table schema_case3.tb_case3 (
id serial primary key,
name text
);
insert into schema_case3.tb_case3 (name) values ('test_name');
-- 조회(일반유저)
SELECT * FROM schema_case3.tb_case3;
id | name
----+-----------
1 | test_name
-- 소유자 불일치 확인 및 테스트
-- 스키마 오너: postgres / 테이블 오너: testapp
SET ROLE postgres;
SELECT * FROM schema_case3.tb_case3;
id | name
----+-----------
1 | test_name
시나리오 4. 스키마 사용자 소유 및 관리자 객체 생성 패턴: 스키마 오너는 testapp 유저인데, 내부 테이블은 관리자(postgres)가 만들어서 소유권이 교차하는 구조
SET ROLE postgres;
-- 스키마 오너를 testapp로 지정
CREATE SCHEMA schema_case4 AUTHORIZATION testapp;
-- [2] 테이블은 postgres가 생성
create table schema_case4.tb_case4 (
id serial primary key,
name text
);
insert into schema_case4.tb_case4 (name) values ('test_name');
-- 권한 테스트, 일반 유저로 전환
SET ROLE testapp;
-- 스키마 오너인 testapp가 정작 자기 스키마 안의 tb_case4 조회가 안 됨! (관리자가 GRANT 안 줌)
SELECT * FROM schema_case4.tb_case4;
ERROR: permission denied for table tb_case4
시나리오 5. 관리자 중앙 집중 관리 및 객체별 권한 지정 패턴: 관리자가 스키마와 여러 테이블을 관리하며, testapp 유저에게 특정 테이블만 권한부여
SET ROLE postgres; -- [1] 스키마 및 2개의 테이블 생성 CREATE SCHEMA schema_case5 AUTHORIZATION postgres; CREATE TABLE schema_case5.tb_allowed (id INT, val TEXT); CREATE TABLE schema_case5.tb_restricted (id INT, val TEXT); INSERT INTO schema_case5.tb_allowed VALUES (5, 'Allowed'); INSERT INTO schema_case5.tb_restricted VALUES (5, 'Restricted'); -- [2] 스키마 USAGE 및 tb_allowed에 대해서만 SELECT 부여 GRANT USAGE ON SCHEMA schema_case5 TO testapp; GRANT SELECT ON schema_case5.tb_allowed TO testapp; -- [3] 권한 테스트 SET ROLE testapp; SELECT * FROM schema_case5.tb_allowed; -- 성공 id | val ----+--------- 5 | Allowed SELECT * FROM schema_case5.tb_restricted; -- 실패 (Permission Denied) ERROR: permission denied for table tb_restricted
시나리오 6. PUBLIC 롤을 이용한 전체 유저 일괄 권한 패턴: 개별 유저에게 권한을 준 적이 없는데 PUBLIC에 권한이 걸려있어 신규 유저까지 자동으로 조회가 되는 구조
PG에서 PUBLIC은 모든 유저가 자동으로 속하는 기본 그룹입니다. 개별 유저에게 직접 GRANT를 주지 않았어도 DB에 존재하는 모든 유저(신규 생성 유저 포함)가 즉시 해당 테이블을 조회/조작할 수 있게 됩니다. 유저 권한에 대해서 딕셔너리 조회시(특정 사용자에게 직접 부여된 권한만 조회시) 조회가 되는 요건 중 하나입니다.
SET ROLE postgres; -- 스키마 및 테이블 생성 CREATE SCHEMA schema_case6 AUTHORIZATION postgres; CREATE TABLE schema_case6.tb_public_open (id INT, val TEXT); INSERT INTO schema_case6.tb_public_open VALUES (6, 'Open to Everyone'); -- PUBLIC 전역 그룹에 권한 부여 GRANT USAGE ON SCHEMA schema_case6 TO PUBLIC; GRANT SELECT ON schema_case6.tb_public_open TO PUBLIC; -- 권한 테스트: 명시적으로 GRANT 받은 적 없는 testapp 모두 조회 성공 SET ROLE testapp; SELECT * FROM schema_case6.tb_public_open; -- 성공 id | val ----+------------------ 6 | Open to Everyone
위 예제들을 적용한 뒤 아래 스캔 쿼리를 돌려보시면, 각 스키마/테이블별로 오너십 관계와 일반 유저 testapp의 실효 접근 권한이 어떻게 다르게 나타나는지 한눈에 검증하실 수 있습니다.
SELECT
n.nspname AS schema_name,
pg_get_userbyid(n.nspowner) AS schema_owner,
c.relname AS table_name,
pg_get_userbyid(c.relowner) AS table_owner,
-- testapp 계정 기준 실효 권한 검증 (True / False)
has_schema_privilege('testapp', n.oid, 'USAGE') AS testapp_schema_usage,
has_table_privilege('testapp', c.oid, 'SELECT') AS testapp_can_select,
-- 오너십 및 권한 패턴 진단
CASE
WHEN pg_get_userbyid(n.nspowner) = 'testapp' AND pg_get_userbyid(c.relowner) = 'testapp' THEN '1번: 스키마/객체 유저 일체형'
WHEN pg_get_userbyid(n.nspowner) = 'postgres' AND pg_get_userbyid(c.relowner) = 'postgres' THEN '2/5/6번: 관리자 소유 형태'
WHEN pg_get_userbyid(n.nspowner) = 'postgres' AND pg_get_userbyid(c.relowner) = 'testapp' THEN '3번: 스키마(Admin) / 객체(User)'
WHEN pg_get_userbyid(n.nspowner) = 'testapp' AND pg_get_userbyid(c.relowner) = 'postgres' THEN '4번: 스키마(User) / 객체(Admin)'
ELSE '기타 오너십 교차 형태'
END AS pattern_type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE 'schema_case%'
AND c.relkind = 'r'
ORDER BY n.nspname, c.relname;
schema_name | schema_owner | table_name | table_owner | testapp_schema_usage | testapp_can_select | pattern_type
--------------+--------------+----------------+-------------+----------------------+--------------------+---------------------------------
schema_case1 | testapp | tb_case1 | testapp | t | t | 1번: 스키마/객체 유저 일체형
schema_case2 | postgres | tb_case2 | postgres | t | t | 2/5/6번: 관리자 소유 형태
schema_case3 | postgres | tb_case3 | testapp | t | t | 3번: 스키마(Admin) / 객체(User)
schema_case4 | testapp | tb_case4 | postgres | t | f | 4번: 스키마(User) / 객체(Admin)
schema_case5 | postgres | tb_allowed | postgres | t | t | 2/5/6번: 관리자 소유 형태
schema_case5 | postgres | tb_restricted | postgres | t | f | 2/5/6번: 관리자 소유 형태
schema_case6 | postgres | tb_public_open | postgres | t | t | 2/5/6번: 관리자 소유 형태
[참고] has_table_privilege()와 has_schema_privilege()는 PostgreSQL에서 "특정 유저가 특정 스키마나 테이블에 대해 실제로 동작 가능한 권한을 가지고 있는지"를 true / false (Boolean) 값으로 바로 알려주는 내장 함수(Access Privilege Inquiry Function)입니다.
PostgreSQL의 카탈로그 딕셔너리(pg_class.relacl, pg_namespace.nspacl 등)는 권한 정보를 복잡한 문자열 배열(ACL) 형태로 갖고 있어서 그냥 조회하면 해독하기가 매우 까다롭습니다.
이 함수들은 그 까다로운 과정(유저 직접 권한, PUBLIC 그룹 권한, 스키마/오브젝트 소유권, 슈퍼유저 여부 등)을 내부적으로 모두 계산한 최종 '실효 권한' 결과만 딱 알려주는 일종의 권한 판별 숏컷(Shortcut) 역할을 합니다.
왜 카탈로그(pg_class 등)를 직접 조인하는 대신 이 함수를 쓸까요? 모든 조건을 카탈로그 SQL 쿼리로 짜려면 조건절이 수십 줄이 넘어가지만, 이 내장 함수는 내부 엔진이 모든 조건 계산을 끝낸 최종 'true/false'만 깔끔하게 리턴해 줍니다.
시나리오 7. 여러 database 환경에서의 권한 직접 부여시 조회 상황
이번에는 앞에 6개의 시나리오와 달리 구성적인 부분과 연관된 부분으로 여러 데이터베이스가 있는 환경에서의 상황입니다.
3개의 데이터베이스를 만들고 데이터베이스 마다 스키마를 만들고 권한을 부여해보도록 하겠습니다.
데이터베이스 생성합니다.
CREATE DATABASE test1 TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C'; CREATE DATABASE test2 TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C'; CREATE DATABASE test3 TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';
그다음 데이터베이스 마다 스키마와 테이블 생성, 권한 부여를 진행하겠습니다.
set role postgres;
\c test1
CREATE SCHEMA schema_1 AUTHORIZATION postgres;
create table schema_1.tb_case1 (
id serial primary key,
name text
);
insert into schema_1.tb_case1 (name) values ('test_name');
-- 일반 유저에게 권한 부여
GRANT USAGE ON SCHEMA schema_1 TO testapp;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_1 TO testapp;
alter default privileges in schema schema_1 grant select on tables to testapp;
\c test2
CREATE SCHEMA schema_2 AUTHORIZATION postgres;
create table schema_2.tb_case1 (
id serial primary key,
name text
);
insert into schema_2.tb_case1 (name) values ('test_name');
-- 일반 유저에게 권한 부여
GRANT USAGE ON SCHEMA schema_2 TO testapp;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_2 TO testapp;
alter default privileges in schema schema_2 grant select on tables to testapp;
\c test3
CREATE SCHEMA schema_3 AUTHORIZATION postgres;
create table schema_3.tb_case1 (
id serial primary key,
name text
);
insert into schema_3.tb_case1 (name) values ('test_name');
-- 일반 유저에게 권한 부여
GRANT USAGE ON SCHEMA schema_3 TO testapp;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_3 TO testapp;
alter default privileges in schema schema_3 grant select on tables to testapp;
이와 같이 여러 데이터베이스에서 직접 권한을 부여하였을 경우 어떻게 조회해야 할까요?
DBA(관리자)가 기본적으로 접속을 하는 postgres 나 업무적인 주요 데이터베이스에 접속해서 pg_namespace 딕셔너리나 \du+ 명령어를 통해서 권한 부여 내역을 확인할 수는 없습니다.
여러 데이터베이스 환경에서 권한을 직접 부여하는 경우, 데이터베이스마다 권한 부여 내역을 확인하기 위해서는 해당 데이터베이스에 접속하여 별도로 조회해야 합니다. 이는 관리의 복잡성을 증가시키며, 특히 다수의 데이터베이스와 유저가 존재하는 환경에서는 권한 관리가 매우 어려워질 수 있습니다.
만약 담당하고 있던 또는 팀에서 관리하고 있던 데이터베이스가 아닌 다른 부서를 통해 DB 인스턴스를 인수인계 받았을 경우 권한에 대해서 어떻게 파악해야 할까요? 위의 경우는 각 데이터베이스마다 접속하여 권한 부여 내역을 확인해야 합니다. 시간과 비용이 많이 소요되는 점도 중요하긴하나 여러 데이터베이스 또는 1개 이상의 데이터베이스 환경에서 확인하는 과정에서 권한 설정내역에 대한 확인이 누락이 발생할 수도 있습니다.
여러 데이터베이스를 사용하고 있고 권한을 부여하는 상황이 되더라도 Role 로 권한을 부여하였다면 어떻게 되었을까요?
Role 로 권한을 부여하였더라도, 세부 권한 부여 내역은 물론 데이터베이스에 접속하여 확인해야 하지만 사용자가 가지고 있는 Role에 대한 정보 그리고 잘 정리된 Role의 네이밍 규칙을 통해서 Role이 어떤 데이터베이스와 연관되어 있는지 관리적인 부분에서 쉽게 파악할 수 있습니다.(해당 내용은 아래 Role 에서 설명)
운영 환경에서는 서비스 요구사항과 보안 정책등에 따라 이런 7가지 외에도 훨씬 더 다양하고 예외적인 형태로 권한이 직접 부여되는 케이스들이 얼마든지 존재할 수 있습니다. (예: 컬럼 단위 권한, 특정 함수 우회 권한, 사용자별 변칙 Default ACL 등)
문제는 이처럼 유저 계정에 권한을 직접 부여하는 방식(Direct Grant)을 지속 할수록, 새로운 예외 상황이 발생할 때마다 이를 감사하기 위한 카탈로그 조회 쿼리의 복잡성과 예외 조건절 역시 무한정 늘어난다는 점입니다.
따라서 개별 유저에게 권한을 직접 GRANT하는 방식보다 다음에 알아볼 Role(역할) 기반의 권한 체계로 전환하여, 유저는 해당 Role을 상속받도록 구조를 전환하는 것만이 관리의 복잡성을 줄이고 보안 정책을 일관되게 적용할 수 있는 방법입니다.
Role을 통한 권한 관리
Role은 접속 권한과 데이터베이스 내의 테이블, 시퀀스, 함수 및 스키마에 대한 권한을 제어하기 위한 단위입니다. PostgreSQL에서는 다른 RDBMS와 달리 User와 Role의 구분이 없고 모두 Role로 통일되어 있습니다.
- LOGIN 옵션이 있는 Role = 유저(User) 역할
- LOGIN 옵션이 없는 Role = Role(권한) 역할
다양한 오브젝트 및 스키마에 대한 권한을 롤(Role)에 부여한 후에 해당 롤을 유저에게 부여하면 효율적으로 권한 관리를 할 수 있습니다. 각 개별 권한을 유저에게 직접 부여하였을때는 권한을 조회하기도 어려운 측면이 크기 때문에 Role 로 권한을 부여하는 것이 관리적인 측면에서도 좋습니다.
Role 생성 및 부여
글에서는 role에 대해서 조금더 명확성을 가지기 위해서 rl 이라는 prefix를 사용고 스키마명을 포함하여 다음과 같이 role을 생성 하도록 하겠습니다.
-- 생성 예제 create role <롤명>; set role postgres; -- 읽기/쓰기 용도로 생성 create role rl_test_dml; -- 읽기 전용 용도로 생성 create role rl_test_sel;
Role 도 유저와 동일하게 생성 및 삭제와 같은 관리는 인스턴스(클러스터) 전역에서 이루어지게 됩니다. 다만 Role에 권한 부여 및 조회와 같은 관리는 데이터베이스 단위로 이루어지게 됩니다.
따라서 Role을 생성한 후에는 해당 Role에 권한을 부여할 데이터베이스에 접속하여 권한을 부여해야 합니다.
Role 목록은 유저와 동일하게 \du 와 pg_roles 뷰를 통해서 조회할 수 있습니다.
\du+
List of roles
Role name | Attributes | Description
----------------+------------------------------------------------------------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
rl_test_dml | Cannot login |
rl_test_sel | Cannot login |
testapp | |
-- 로그인 가능 Role 조회, 즉 계정으로 사용 가능한 Role 조회
select rolname, rolcanlogin from pg_roles where rolcanlogin='t';
rolname | rolcanlogin
----------+-------------
postgres | t
testapp | t
-- Role 조회
select rolname, rolcanlogin from pg_roles where rolcanlogin='f';
rolname | rolcanlogin
--------------------------+-------------
.....
rl_test_dml | f
rl_test_sel | f
생성된 Role에 권한을 부여하도록 하겠습니다.
GRANT USAGE ON SCHEMA db1 TO rl_test_sel; GRANT SELECT on all tables in schema db1 to rl_test_sel; GRANT USAGE ON SCHEMA db1 TO rl_test_dml; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA db1 TO rl_test_dml; GRANT USAGE ON ALL SEQUENCES IN SCHEMA db1 TO rl_test_dml;
앞으로 새로 생성되는 오브젝트에 대해서 자동으로 권한을 부여하기 위해서 DEFAULT 권한도 Role에 부여하도록 하겠습니다.
alter default privileges in schema db1 grant select on tables to rl_test_sel; alter default privileges in schema db1 grant select, insert, update, delete on tables to rl_test_dml; alter default privileges in schema db1 grant usage on sequences to rl_test_dml;
이제 생성된 Role을 유저에게 부여하도록 하겠습니다.
GRANT rl_test_sel TO testapp; GRANT rl_test_dml TO testapp;
Role 부여 상황 및 Role에 부여된 권한은 다음과 같이 확인할 수 있습니다.
특정 유저에게 부여된 Role 확인
SELECT
member_role.rolname AS user_name,
granted_role.rolname AS granted_role
FROM pg_auth_members am
JOIN pg_roles granted_role
ON granted_role.oid = am.roleid
JOIN pg_roles member_role
ON member_role.oid = am.member
WHERE member_role.rolname = 'testapp'
ORDER BY granted_role.rolname;
user_name | granted_role
-----------+----------------
testapp | rl_test_dml
testapp | rl_test_sel
특정 Role에 부여된 권한 확인
-- 조회할 대상 Role 지정
WITH target_role AS (
SELECT oid, rolname
FROM pg_roles
WHERE rolname = 'rl_test_dml'
)
-- 1. Database 레벨 권한 조회
SELECT
'DATABASE' AS object_type,
NULL::text AS schema_name,
d.datname AS object_name,
acl.privilege_type,
acl.is_grantable
FROM pg_database d
CROSS JOIN LATERAL aclexplode(d.datacl) acl
JOIN target_role r
ON acl.grantee = r.oid
UNION ALL
-- 2. Schema 레벨 권한 조회
SELECT
'SCHEMA',
n.nspname,
n.nspname,
acl.privilege_type,
acl.is_grantable
FROM pg_namespace n
CROSS JOIN LATERAL aclexplode(n.nspacl) acl
JOIN target_role r
ON acl.grantee = r.oid
UNION ALL
-- 3. Table / View / Materialized View / Foreign Table 권한 조회
SELECT
'TABLE',
n.nspname,
c.relname,
acl.privilege_type,
acl.is_grantable
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
CROSS JOIN LATERAL aclexplode(c.relacl) acl
JOIN target_role r
ON acl.grantee = r.oid
WHERE c.relkind IN ('r', 'p', 'v', 'm', 'f')
UNION ALL
-- 4. Sequence 권한 조회
SELECT
'SEQUENCE',
n.nspname,
c.relname,
acl.privilege_type,
acl.is_grantable
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
CROSS JOIN LATERAL aclexplode(c.relacl) acl
JOIN target_role r
ON acl.grantee = r.oid
WHERE c.relkind = 'S'
UNION ALL
-- 5. Function / Procedure 권한 조회
SELECT
CASE
WHEN p.prokind = 'p' THEN 'PROCEDURE'
ELSE 'FUNCTION'
END,
n.nspname,
p.proname,
acl.privilege_type,
acl.is_grantable
FROM pg_proc p
JOIN pg_namespace n
ON n.oid = p.pronamespace
CROSS JOIN LATERAL aclexplode(p.proacl) acl
JOIN target_role r
ON acl.grantee = r.oid
-- 객체 유형 / Schema / 객체명 / 권한 순으로 정렬
ORDER BY
object_type,
schema_name,
object_name,
privilege_type;
만약 앞의 케이스처럼 데이터베이스가 여러개 존재하고 사용하는 경우라면 다음의 예시와 같이 네이밍 규칙을 통해서 Role이 어떤 데이터베이스와 연관되어 있는지 쉽게 파악하게 할 수도 있습니다.
-- 생성 예제 create role rl_db1_test_schema_sel; create role rl_db1_test_schema_dml;
위의 Role은 다음과 같은 네이밍 규칙으로 생성된 것입니다.
- rl: role 을 의미하는 prefix
- db1: 데이터베이스명
- test_schema: 스키마명
- sel 또는 dml: Role에 부여된 권한의 종류(유형)
위의 예시와 같이 Role 이름에 DB 인스턴스내 구성된 내역이나 권한 디자인에 따라서 네이밍 규칙을 정하고 생성한다면 Role 이름으로도 어떤 데이터베이스나 어떤 스키마에 권한이 연관되어 있는지 등을 유추하거나 쉽게 확인이 가능할 것입니다.
각 유저에게 부여된 권한은 메타 명령어 \drg 로 확인할 수 있습니다.
\drg
List of role grants
Role name | Member of | Options | Grantor
-----------+------------------------+--------------+----------
testapp | rl_db1_test_schema_sel | INHERIT, SET | postgres
PostgreSQL 15 버전까지는 \du 또는 \dg 명령어에서 Role membership 정보를 Member of 컬럼으로 확인할 수 있었으나, PostgreSQL 16부터는 해당 컬럼이 제거되고 Role membership 상세 정보를 확인하기 위한 \drg 명령어가 새롭게 추가되었습니다.
Role 삭제
Role 을 삭제하기 위해서는 먼저 Role에 부여된 권한을 회수하고 삭제를 진행해야 합니다. Role에 부여된 권한을 회수하지 않고 삭제를 진행하면, Role이 소유하고 있는 오브젝트나 권한 때문에 삭제가 실패하게 됩니다.
drop role rl_test_dml; ERROR: role "rl_test_dml" cannot be dropped because some objects depend on it DETAIL: privileges for schema db1 privileges for sequence db1.test_table_id_seq privileges for table db1.test_table privileges for default privileges on new relations belonging to role postgres in schema db1 privileges for sequence db1.test_table2_id_seq privileges for table db1.test_table2 privileges for default privileges on new sequences belonging to role postgres in schema db1
이런 경우 일반적인 방법으로 부여된 권한을 직접 revoke 를 통해 권한을 회수하는 것이 일반적인 방법입니다.
다른 방법으로는 Role이 소유한 오브젝트와 권한을 한꺼번에 회수하는 방법으로 "DROP OWNED BY" 명령어를 사용할 수 있습니다.
단, "DROP OWNED BY" 주의해야할 점은 "DROP OWNED BY" 를 진행하는 Role이 객체의 Owner인 경우 해당 객체가 삭제가 되므로 주의해야합니다.
다른 Owner의 객체에 대해서 권한을 부여한 Role 이라면 "DROP OWNED BY" 를 진행해도 해당 객체는 삭제되지 않고 권한만 회수됩니다.
DROP OWNED BY rl_test_dml;
그 다음 삭제를 진행합니다.
DROP ROLE rl_test_dml;
객체를 소유하고 있는 Role 또는 유저를 삭제할 때는 "REASSIGN OWNED BY" 명령어를 사용하여 소유권을 다른 유저에게 이전한 후 삭제하는 방법도 있습니다. 이 명령어는 객체 소유자 유저를 삭제할 때 주로 사용됩니다.
객체를 소유한 유저를 삭제하려면 먼저 생성한 객체를 삭제하거나 다른 유저에게 소유권을 이전해야 합니다. 이때 "REASSIGN OWNED BY" 명령어를 사용하여 객체 소유권을 다른 유저에게 이전한 후 삭제하면 됩니다.
[참고] "DROP OWNED BY" 는 진행하는 Role이 소유한 객체를 삭제하고, 그 Role에 직접 부여된 권한도 회수합니다. 그래서 단순 권한 Role이라면 편하지만, 객체 Owner인 경우에는 매우 조심해야 합니다.
drop role rl_testapp_dml; ERROR: role "rl_testapp_dml" cannot be dropped because some objects depend on it DETAIL: privileges for schema db1 privileges for sequence db1.test_table_id_seq privileges for table db1.test_table privileges for default privileges on new relations belonging to role postgres in schema db1 privileges for sequence db1.test_table2_id_seq privileges for table db1.test_table2 privileges for default privileges on new sequences belonging to role postgres in schema db1 DROP OWNED BY rl_testapp_dml; DROP OWNED BY rl_testapp_sel; DROP ROLE rl_testapp_dml; DROP ROLE rl_testapp_sel;
Conclusion
PostgreSQL의 권한 체계는 Role과 User가 사실상 같은 개념이라는 점, 그리고 접근을 위해서는 Database → Schema → Object 순서로 각 단계의 권한을 모두 통과해야 한다는 점에서 출발합니다.
이 구조를 제대로 이해하지 못하면 USAGE 권한을 부여했는데 SELECT는 안 되거나, DEFAULT PRIVILEGES를 설정했는데 이전에 만든 테이블에는 적용되지 않는 상황을 자주 겪게 됩니다.
Oracle과 달리 테이블의 소유자는 스키마 소유자가 아니라 실제로 테이블을 생성한 유저가 되기 때문에, 관리자가 대신 테이블을 만들어주면 소유권이 교차되어 스키마 소유자가 정작 자신의 스키마 안 테이블에 접근하지 못하는 경우도 발생할 수 있습니다. PostgreSQL 15부터는 public 스키마에 대한 기본 CREATE 권한도 사라졌기 때문에 이 부분도 유의가 필요합니다.
유저에게 권한을 직접 부여하는 방식은 유저 하나의 실효 권한을 확인하는 데도 pg_roles, pg_database, pg_namespace, pg_class 등 여러 카탈로그를 aclexplode()와 함께 조회해야 하고, \dn+나 \dp 같은 메타 명령어만으로는 권한이 어느 레벨에서 부여된 것인지 파악하기 어렵습니다.
7가지 시나리오를 통해 스키마와 오브젝트의 소유권이 서로 교차되는 경우, PUBLIC을 통해 의도치 않게 권한이 부여되는 경우, 여러 데이터베이스에 권한이 흩어져 있는 경우를 각각 확인해 보았고, 데이터베이스가 여러 개인 환경에서는 권한을 확인하기 위해 매번 각 데이터베이스에 접속해야 하는 어려움도 있었습니다.
이런 점들을 고려하면 유저에게 권한을 직접 부여하기보다는 Role 단위로 권한을 부여하고 유저는 해당 Role을 상속받도록 구성하는 방식이 관리 측면에서 더 나은 방법이라고 생각됩니다.
Role에 네이밍 규칙을 적용해두면 어떤 Role이 어떤 데이터베이스나 스키마와 연관되어 있는지 파악하기도 쉬워집니다. 다만 Role을 삭제할 때는 DROP OWNED BY와 REASSIGN OWNED BY의 동작 차이를 정확히 이해하고 사용해야 하며, 권한을 확인할 때도 카탈로그를 직접 조회하기보다는 has_table_privilege(), has_schema_privilege()와 같은 내장 함수를 활용하는 것이 더 효율적으로 조회할 수 있는 방법입니다.
Reference
- GRANT 구문 전체 레퍼런스
- 권한 종류 설명(5.8 Privileges, CREATE/USAGE/CONNECT/TEMPORARY 등 객체별 정의)
- CREATE ROLE
- CREATE SCHEMA
- ALTER DEFAULT PRIVILEGES
- DROP OWNED
- REASSIGN OWNED
- pg_roles 뷰
연관된 다른글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
