postgresql18.3 로컬 주소DB 구축 (2) - 계정 생성

PostgreSQL의 Role 과 User

PostgreSQL에서는 내부적으로 Role만 존재합니다.

CREATE USER는 사실 CREATE ROLE ... WITH LOGIN의 단순 별칭(alias) 입니다.

다시 말하면, PostgreSQL에서는 Role(역할)과 User(계정)가 사실상 같은 개념이기 때문에 이름을 동일하게 사용할 수 있습니다.

실제로 PostgreSQL 내부적으로는 User라는 별도의 객체가 존재하지 않고, LOGIN 권한이 있는 Role을 우리가 편의상 User라고 부르는 것뿐입니다.

 

아래 두 구문은 완전히 동일합니다.

CREATE USER juso_user WITH PASSWORD 'secret';
CREATE ROLE juso_user WITH LOGIN PASSWORD 'secret';

 

pg_roles 시스템 카탈로그를 조회하면 user든 role이든 모두 같은 테이블에 저장된 것을 확인할 수 있습니다.

SELECT rolname, rolcanlogin, rolsuper
FROM pg_roles
WHERE rolname LIKE 'juso%';

---

Schema, Role, Table 권한설정

1. juso 스키마 생성

# juso 스키마가 존재하면 그냥 지나가고 스키마의 소유주(Owner)를 juso_admin으로 지정

CREATE SCHEMA IF NOT EXISTS juso AUTHORIZATION juso_admin;

 

2. 권한 그룹(Role) 생성 (직접 로그인은 못하게 설정)

CREATE ROLE ro_readonly NOLOGIN;
CREATE ROLE ro_readwrite NOLOGIN;
CREATE ROLE ro_admin NOLOGIN;

 

3. 스키마 권한 할당 (juso 스키마 기준)

GRANT USAGE ON SCHEMA juso TO ro_readonly, ro_readwrite;
GRANT ALL ON SCHEMA juso TO ro_admin;

 

4. 세부 테이블 권한 설정 -- ReadOnly: 검색(SELECT)만 가능

• GRANT : 현재 존재하는 객체들 권한 (지금 바로 쓸 수 있게)
• ALTER DEFAULT PRIVILEGES: 미래에 생성될 객체들 권한 (나중에 만들어져도 자동으로)

# ro_readonly: 검색(SELECT)만 가능

GRANT SELECT ON ALL TABLES IN SCHEMA juso TO ro_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA juso GRANT SELECT ON TABLES TO ro_readonly;

 

# ro_readwrite: 서비스 운영에 필요한 모든 DML 가능

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA juso TO ro_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA juso TO ro_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA juso GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ro_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA juso GRANT USAGE, SELECT ON SEQUENCES TO ro_readwrite;

 

# ro_admin: : 데이터 적재 및 구조 변경 (임포터/DBA용)

GRANT ALL ON ALL TABLES IN SCHEMA juso TO ro_admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA juso TO ro_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA juso GRANT ALL ON TABLES TO ro_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA juso GRANT ALL ON SEQUENCES TO ro_admin;

 

5. 계정 생성

CREATE USER juso_guest WITH LOGIN PASSWORD 'guest_pass' IN ROLE juso_readonly;
CREATE USER juso_user WITH LOGIN PASSWORD 'user_pass' IN ROLE juso_readwrite;
CREATE USER juso_dba  WITH LOGIN PASSWORD 'dba_pass' IN ROLE juso_admin;

 

6. Search Path 설정 (선택 사항)

• juso_user가 매번 juso.table_name이라고 치지 않고 그냥 table_name만 쳐도 됩니다.

ALTER ROLE juso_guest SET search_path TO juso, public;
ALTER ROLE juso_user  SET search_path TO juso, public;
ALTER ROLE juso_dba   SET search_path TO juso, public;