PostgreSQL 테이블 권한 관리하기

같이 작업할 개발자가 늘어나고, 상호작용하는 서버가 늘어나면서 점점 DB 권한을 관리해야 할 필요성이 느껴진다.
DB의 모든 권한을 주었을 때 서버가 탈취되거나 실수로 날리는 것을 상상하면 너무 끔찍하기 때문에 최소한의 보험은 해야겠다는 생각을 하게되었다.

계정 생성

CREATE USER <계정명> PASSWORD '<비밀번호>';

슈퍼유저를 만들고 싶으면 다음과 같이 하면 됨

CREATE USER <계정명> PASSWORD '<비밀번호>' SUPERUSER;

계정 수정

계정명 수정

ALTER USER user_name RENAME TO new_name;

비밀번호 수정

ALTER USER user_name WITH PASSWORD 'strongpassword';

스키마에 읽기 권한 주기

GRANT USAGE ON SCHEMA public TO <계정명>;

테이블에 읽기 권한 주기

GRANT SELECT ON table_name TO username;

SELECT와 관련하여 권한을 주려면 다음과 같이 하면 된다.
만약 모든 테이블에 조회가 가능하려면 다음과 같이 해야한다.

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

미래에 추가될 테이블에 자동으로 Read 권한까지 주려면 다음 줄을 추가로 입력하자

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO username;

권한 조회

테이블 권한 조회

select * from information_schema.role_table_grants where grantee = '<계정명>';

- Grantor :  권한을 부여한 role
- Grantee :  권한을 부여받은 role
- Table_schema : schema명
- Table_name : table명
- Privilege_type : 권한의 유형
- Is_grantable :  권한을 줄 수 있을 경우 YES, 아닐 경우 NO

스키마 권한 조회

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege('<계정명>', "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege('<계정명>', "name", 'USAGE') AS "usage"
    FROM "names";

이렇게 하면 조회가 가능하다.

생성 권한 추가

GRANT INSERT ON table_name TO username;

이렇게 하면 되지만 함정카드가 숨어있다. sequence를 조회 및 업데이트해야 하기 때문에 이에 대한 권한도 추가해야 한다. 필자는 귀찮아서 그냥 sequence는 모든 권한을 부여했다.

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO username;

앞선 두 줄 권한은 해당 스키마를 덤프 받기 위해서도 필요하다.

수정 및 삭제 권한 추가

GRANT UPDATE, DELETE ON table_name TO username;