PostgreSQL Access Privileges

PosrgreSQL version: 14.1

Access Privileges

Postgresql은 Access privilieges으로 object(table, sequence, schema, database 등)에 대한 접근 권한을 관리하고 있다. 아래는 /l으로 데이터베이스에 대한 access privileges를 확인한 것이다.

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | hyunlang | UTF8     | C       | C     |

Privileges

privilege는 어떨때 필요할까? 보통 생성한 object에 대한 모든 권한(SELECT, INSERT, UPDATE, DELETE 외에도 다양한 권한이 있음)은 owner 또는 superuser에게 있다. 그 외의 사용자가 접근하기 위해서는 적절한 privilege를 설정함으로써 권한을 부여해주어야 한다.

실습

owner가 아닌 다른 role로 접근하려면 권한이 어떻게 필요한지 직접 확인해보자.

현재 role(=user)에는 superuser인 hyunlang과 테스트용으로 만든 testuser가 있다.

 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 hyunlang  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |                                                            | {}

그리고 hyunlang 계정으로 test_1이라는 데이터베이스를 생성해주었고 Access Privileges에는 아무것도 설정되어있지 않다.

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 test_1    | hyunlang | UTF8     | C       | C     |

이 상태에서 먼저 owner의 계정으로 test_1에 접속하면 잘 접속이 된다. 하지만 testuser로 접근을 시도하면 다음과 같이 premission 관련 에러가 발생한다.

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  permission denied for database "test_1"
DETAIL:  User does not have CONNECT privilege.

접속을 할 수 있도록 설정해 준 후 확인해보자.

postgres=# grant connect on database test_1 to testuser;
GRANT

\l로 Access privileges를 확인해보면 testuser에 c로 나타난 connect 권한이 추가된 것을 볼 수 있다.

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 test_1    | hyunlang | UTF8     | C       | C     | testuser=c/hyunlang

test_1=# \c test_1 testuser
You are now connected to database "test_1" as user "testuser".
test_1=>

나는 이미 만들어 놓은 테이블들이 있고 onwer를 보면 hyunlang으로 되어있는 것과 testuser로 되어있는 것이 있다.

test_1=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | dept | table | testuser
 public | emp  | table | hyunlang

testuser의 상태에서 hyunlang이 소유한 emp 테이블에는 어떠한 권한도 없기 때문에 어떠한 조회도 불가능하며 testuser가 소유한 table만 자유롭게 사용할 수 있다. hyunlang은 superuser이므로 모든 테이블의 데이터를 조회할 수 있다.

test_1=> select * from emp;
ERROR:  permission denied for table emp

보통 database의 owner가 table의 owner일테니 testuser가 소유한 테이블은 이제 없다고 생각하고 진행하겠다.

더 다양한 권한을 부여하기 위해서는 문서를 참고하면 되고 권한을 해제하고 싶을때는 REVOKE를 쓴다.
먼저 superuser인 hyunlang으로 로그인 후 privileges로 testuser에 모든 권한을 설정해주었다.

postgres=# grant all privileges on database test_1 to testuser;
GRANT

postgres=# \l
test_1    | hyunlang | UTF8     | C       | C     | testuser=CTc/hyunlang

acccess privilege에서 C는 CREATE로 데이터베이스에 스키마를 생성할수 있는 권한이고 T는 TEMPORARY로 데이터베이스에 임시 테이블을 생성할수 있는 권한이라고 하는데 구체적으로 의미하는 바는 아직 잘 모르겠다.

자 이제 데이터베이스에 모든 권한을 부여받았으니 거기에 있는 테이블도 마음껏 사용할 수 있을까?

정답은..🙅🏻‍♀️ !!

사실 처음에는 이정도로 하면 되겠지 하고 확인해봤는데 테이블에서도 testuser에 대한 권한을 부여해주어야했다.

test_1=> select * from emp;
ERROR:  permission denied for table emp

permission 에러가 났으니 다시 superuser로 접속해서 권한을 주자.

test_1=# grant select on table emp to testuser;
GRANT

테이블의 Access privileges는 다음과 같이 확인할 수 있다.

test_1=> \dp
                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
 public | dept | table |                           |                   |
 public | emp  | table | hyunlang=arwdDxt/hyunlang+|                   |
        |      |       | testuser=r/hyunlang       |                   |

select에 대한 권한만 부여받았으니 다른 작업은 할 수 없고 필요에 따라 권한을 부여하자.

끝

결론은 owner가 아닌 다른 role에서 데이터베이스나 테이블을 사용하고자 할때는 GRANT로 권한을 부여해주어야 하고 만약 권한을 모두 다른 user에게 넘기고 싶다면 그냥 소유자를 바꿔주자. 또한 database에 권한이 주어졌더라도(데이터베이스의 소유자라도) table에 권한이 다르다면 permission 문제가 발생할 수 있다.

권한 신경 안쓰다가 permission error에 혼꾸녕나고 정리해봄

---

참고

https://www.postgresql.org/docs/13/ddl-priv.html
https://www.postgresql.org/docs/9.1/sql-revoke.html
https://www.postgresql.org/docs/13/sql-grant.html
https://shanepark.tistory.com/186
https://kb.objectrocket.com/postgresql/postgres-access-privileges-1289
https://www.postgresdba.com/bbs/board.php?bo_table=B12&wr_id=46