PostgreSQL Authorization

1. Database Roles

PostgreSQL은 “role”을 사용하여 데이터베이스 접근 권한을 관리한다.

다음의 CREATE ROLE 구문으로 role을 생성 할 수 있으며, role의 속성은 ALTER ROLE로 수정할 수 있다. 

CREATE ROLE **name** [ [ WITH ] **option** [...] ]

 

다음은 option에 대한 설명이다.

1) 권한 관련 옵션 

SUPERUSER | NOSUPERUSER (Default: NOSUPERUSER)

Database superuser는 로그인 권한을 제외한 모든 권한 검사를 무시한다.

따라서 superuser 권한을 사용할 때는 주의하도록 한다. 

superuser권한을 가진 role은 다음의 구문으로 생성 가능하다.

CREATE ROLE name SUPERUSER;

 

CREAETDB | NOCREATEDB (Default: NOCREATEDB)

Database를 생성하기 위해서는 CREATEDB 권한이 있어야 한다. 

CREATEDB 권한을 가진 role은 다음의 구문으로 생성 가능하다.

CREATE ROLE name CREATEDB;

 

CREATEROLE |  NOCREATEROLE (Default:  NOCREATEDB)

Role을 생성하기 위해서는 CREATEROLE 권한이 있어야 한다. 

CREATEROLE 권한은 다른 role을 alter(변경) 및 drop(삭제)할 수 있을 뿐만 아니라 구성원의 권한을 부여(grant)하거나 취소(revoke) 할 수 있다. 그

러나 superuser의 role을 생성, 변경, 삭제 또는 변경하려면 superuser 상태여야 한다. 

CREATEROLE 권한을 가진 role은 다음의 구문으로 생성 가능하다. 

CREATE ROLE name CREATEROLE;

 

INHERIT |  NOINHERIT (Default: INHERIT)

해당 role이 갖는 하위 member role들이 해당 role의 권한에 대해 상속을 자동으로 허용할지 여부를 결정한다.

CREATE ROLE name INHERIT;

 

ADMIN role_name

WITH ADMIN OPTION 부여 여부를 결정한다.

CREATE ROLE name WITH ADMIN role_name;

 

2) LOGIN 및 패스워드 관련 옵션 

LOGIN | NOLOGIN (Default: NOLOGIN)
LOGIN 옵션을 할당 받은 role만이 database 연결 할 수 있다.

LOGIN 속성을 가진 role은 "database user"와 동일하게 간주 될 수 있다.

LOGIN 권한을 가진 role은 다음의 구문으로 생성 가능하다.

CREATE ROLE name LOGIN; CREATE USER name; (CREATE USER는 LOGIN이 기본으로 설정되며, LOGIN을 제외하고 CREATE USER는 CREATE ROLE과  동일하다.)   agens=# create user u_test; CREATE ROLE agens=# create role r_test; CREATE ROLE   rolname | rolcanlogin ========+============== u_test  | t --------+-------------- r_test  | f

 

CONNECTION LIMIT connlimit (Default: no limit)

해당 role을 통해서 얼마나 많은 connection을 생성할 수 있는지 여부를 지정한다.

CREATE ROLE name CONNECTION LIMIT 100;

 

PASSWORD password

client 인증 방식을 사용하려면 database에 연결할 때 사용자의 password를 제공해야 한다.

password 방식과 md5 인증 방식이 있다.

database 암호는 운영체제 암호와 별개이다.

CREATE ROLE name PASSWORD 'string';

 

ENCRYPTED | UNENCRYPTED

설정한 password를 system catalog에 암호화하여 저장할지 여부를 지정한다.

명시하지 않을 경우 password_encryption(default: on) 파라미터 설정을 따른다. 

CREATE ROLE name PASSWORD 'string';

 

VALID UNTIL 'timestamp'

해당 role의 패스퉈드 유효 날짜와 시간을 지정한다.

명시하지 않을 경우 동일 패스워드를 사용할 수 있다.

CREATE ROLE name VALID UNTIL 'timestamp';

 

3) Group Membership 옵션

IN ROLE role_name

한 개 이상의 기존 role에 새로운 멤버를 추가하도록 설정한다. (GRANT 명령을 통해 가능)

create role test03 with in role test;     -- result agens=# \du                                    List of roles  Role name |                         Attributes                         | Member of -----------+------------------------------------------------------------+-----------  agens     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  test      | Cannot login                                               | {}  test03    | Cannot login                                               | *{test}*

ROLE role_name

해당 신규 role의 멤버로 자동적으로 추가되도록 기존 한 개 이상의 role지정한다. (새로운 role을 group으로 만드는 효과와 같음)

create role test04 with role test;     -- result agens=# \du                                    List of roles  Role name |                         Attributes                         | Member of -----------+------------------------------------------------------------+-----------  agens     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  test      | Cannot login                                               | *{test04}*  test04    | Cannot login                                               | {}

 

4) 기타 옵션

REPLICATION | NOREPLICATION (Default: NOREPLICATION)

streaming replication을 할 수 있는 권한이 부여되어야 한다. 

streaming replication에 사용되는 role에는 LOGIN권한이 있어야 한다. 

REPLICATION LOGIN 권한을 가진 role은 다음의 구문으로 생성 가능하다. 

CREATE ROLE name REPLICATION LOGIN;

2. Role Membership

권한 관리를 쉽게 하기 위해 사용자를 그룹화하는 것이 편리하다.

그룹화를 한 후 그룹 전체에 권한을 부여하거나 회수 할 수 있다. 

그룹에 대한 role을 생성한 후 GRANT 및 REVOKE 명령으로 구성원을 추가 및 제거할 수 있다. 

Group Role에 member role 추가 및 제거

GRANT **group_role** TO **role1**, ...; REVOKE **group_role** FROM **role1**, ...;

 

Member role들이 group role의 권한을 사용하는 방법이다.

1) member role에 대한 set role 설정(임시적인 방법)

- 현재 세션의 member role이 해당 group role의 권한들을 사용하도록 한다.

- member role세션이 접속 된 상태에서 group role의 속성이 변경된다면 해당 속성은 set 적용이 불가하다.

- 현재 세션인 member role에 직접적으로 부여된 권한은 사용이 불가하다.

SET ROLE group_role; SET ROLE NONE; or RESET ROLE; - 원래 member role의 자신 권한 설정으로 원복

 

2) member role에 대한 INHERIT 속성 설정 

CREATE ROLE parent_group_role NOINGERIT; CREATE ROLE group_role NOINHERIT; CREATE ROLE test LOGIN INGERIT;     GRANT parent_group_role TO group_role ; GRANT group_role TO test;

3. PRIVILEGE

다음은 Database object 접근 권한인 privilege에 대한 설명이다. 

 

1) Privilege 부여 대상 Objects

- table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedural language, schema, tablespace

2) Privilege 부여 및 회수 방법

• GRANT

object에 대한 권한 부여

Group role에 member 추가

•  REVOKE

object에 대한 권한 회수

Group role에 member 제외

3) privilege 의 종류

PrivilegeDescription

SELECT	Table, View, Sequence 데이터 조회 권한 COPY(copy data between a file and a table)하기 위한 권한 Update 또는 Delete 시 존재하는 컬럼 값들을 참조하기 위한 권한 Sequence의 currval 함수 사용을 위한 권한 Large Object에 대한 read를 위한 권한
INSERT	Table 삽입 권한 (컬럼 단위 지정 시 해당 컬럼 삽입 권한, 다른 컬럼 default 설정 시 해당 default 값 삽입) COPY 받기 위한 권한
UPDATE	Table 데이터 갱신 권한 Update 할 대상 row를 추출하기 위해 SELECT 권한 보유 필요 Select .. for update, select ... for share 수행을 위해 필요 (SELECT 권한 역시 필요) Sequence의 nextval과 setval 함수 사용을 위해 필요 Large Object에 대한 wirte 및 truncate를 위해 필요
DELETE	Table 데이터 삭제 권한 Delete 할 대상 row를 추출하기 위해 SELECT 권한 보유 필요
TRUNCATE	Table에 대한 Truncate 수행 권한
REFFERENCES	Foreign key 제약 조건을 만들기 위한 권한
TRIGGER	Table의 trigger 생성 관련 (CREATE TRIGGER)
CREATE	For Database : 데이터베이스 내에 새로운 스키마를 생성할 수 있는 권한 For Schema: 스키마 안에 Object를 생성할 수 있는 권한 For Tablespace: 테이블스페이스 안에 object를 생성할 수 있는 권한 데이터베이스에 대한 default tablespace를 지정할 수 있는 권한
CONNECT	Login role이 데이터베이스에 connection 할 수 있는 권한 pg_hba.conf에 의해서 제한되며 connection 발생 시 체크됨
TEMPORARY	Temporary Table생성 권한
EXECUTE	Function 과 연산자 사용 권한
USAGE	For Procedural Language : Function을 만들기 위해 사용되는 특정 language에 대한 사용 권한 For Schema : 스키마 안의 object 사용 권한 (최초 catalog 정보만 확인 가능) * 해당 object에 대한 실제 접근/사용을 위해서는 object level에서의 별도 권한 부여 필요 For Sequence : currval 와 nextval 함수를 사용하기 위한 권한 For Type and domain : 테이블, 함수 등의 Object 생성 시 type과 domain에 대한 사용권한 For Foreign-data Wrappers : Foreign-data Wrapper를 사용한 새로운 서버 생성 권한 For Server : 서버를 통해 Foreign Table를 만들기 위한 권한 서버와 연관된 User Mapping 등을 생성/변경/삭제하기 위한 권한
ALL PRIVILEGES	한번에 부여 가능한 모든 권한 부여 (PRIVILEGES 키워드 생략 가능)

 

4) Grant의 사용 

GRANT *privilege* ON *object* TO **role_specification** [ *WITH ADMIN OPTION* ]

* WITH GRANT OPTION : 권한을 부여 받은 role이 또 다른 role에게 부여 받은 권한을 적용하는 옵션

 

5) Revoke의 사용

REVOKE *privilege* ON *object* FROM **role_specification**

6) Example

- 모든 user가 test table에 insert할 수 있는 권한 부여

GRANT INSERT ON test TO *PUBLIC*;

- 모든 user가 test table에 insert 할 수 있는 권한 회수

REVOKE INSERT ON test FROM *PUBLIC*;

- 모든 user가 v_test view에 모든 권한을 사용할 수 있도록 all privilege 권한 부여

GRANT *ALL PRIVILEGES* ON v_test TO PUBLIC;

- test role이 특정 object(Database, schema, object)에 접근할 수 있도록 권한 부여

GRANT CONNECTION DATABASE test_db TO test; GRANT USAGE ON SCHEMA test_schema TO test; GRANT SELECT ON test_schema_test_table TO test;

7) privilege 조회

\dp 명령어로 privilege 정보를 조회할 수 있다.

=> \dp test                               Access privileges  Schema |  Name   | Type  |   Access privileges   | Column access privileges --------+---------+-------+-----------------------+--------------------------  public | test    | table | =arwdDxt/agens        |

- 권한 표기 설명

rolename=xxxx -- privileges granted to a role         =xxxx -- privileges granted to PUBLIC               r -- SELECT ("read")             w -- UPDATE ("write")             a -- INSERT ("append")             d -- DELETE             D -- TRUNCATE             x -- REFERENCES             t -- TRIGGER             X -- EXECUTE             U -- USAGE             C -- CREATE             c -- CONNECT             T -- TEMPORARY       arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)             * -- grant option for preceding privilege           /yyyy -- role that granted this privilege

4. Security level

1) database base level

CREATE DATABASE test;
REVOKE ALL ON DATABASE test FROM public;
GRANT CONNECT ON DATABASE test TO public;

2) schema base level

GRANT USAGE ON SCHEMA test to test01, test02;

3) table base level

GRANT ALL ON <table name> to <role>;

4) column base level

GRANT SELECT (f1) ON test1 TO test1_role; 
GRANT USAGE ON SCHEMA public TO test_role;
SET ROLE test_role;
SELECT f1 FROM public.test1;

5) row base level (참고: https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html)

CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);

• Row Security Policy (https://www.postgresql.org/docs/10/static/ddl-rowsecurity.html)

GRANT를 통해 사용 가능한 SQL-standard privilege system 외에도, table에는 일반 조회로 리턴되거나 데이터 수정 명령에 의해 삽입, 갱신 또는 삭제 될 수있는 행을 사용자별로 제한하는 row security policies가 있을 수 있다.

이 기능은 Row-Level Security이라고도 한다.

아래의 명령어로 row level로 보안을 설정 할 수 있다.

ALTER TABLE ... ENABLE ROW LEVEL SECURITY;

Table에 대한 정책이 존재하지 않는다면 default-deny 정책이 사용되며, 보여지는 row가 없거나 수정할 수 없다.Row security policy에 의해 허가를 받아야만 table에 row 선택 또는 row 수정을 위한 접근이 가능하다. (테이블의 owner는 row security policy에 영향을 받지 않는다.)

TRUNCATE와 REFFERENCES와 같은 table 전체에 적용되는 동작은 row security에 포함되지 않는다.

Row security policy은 command, role 또는 둘 다에 적용될 수 있다. 정책은 모든 명령 또는 SELECT, INSERT, UPDATE 또는 DELETE에 적용되도록 지정할 수 있다. 특정 정책에 여러 role을 할당 할 수 있으며, 일반적인 role membership 및 상속 규칙이 적용된다.

정책에 따라 표시 또는 수정 할 수 있는 row를 지정하려면 boolean 결과를 반환하는 표현식이 필요하다. 표현식이 true를 리턴하지 않는 행은 처리되지 않는다. 가시적인 행과 수정이 허용된 행에 대해 독립적인 제어를 제공하기 위해 별도의 표현식을 지정할 수 있다.

BYPASSRLS 특성을 가진 Superuser와와 role은 테이블에 액세스 할 때 항상 row security system을 우회한다. 일반적으로 table의 owner는 row security를 무시하지만, "ALTER TABLE ... FORCE ROW LEVEL SECURITY"로 row security의 대상이 될 수 있다.