PostgreSQL 역할 및 권한 관리

소개

이 랩에서는 PostgreSQL 역할 및 권한 관리를 탐색합니다. 주요 목표는 특정 속성을 가진 역할을 생성하고 데이터베이스 권한을 관리하는 방법을 배우는 것입니다.

먼저 NOLOGIN 속성을 가진 reporting_role이라는 역할을 생성하여 데이터베이스에 직접 연결하는 것을 방지합니다. 그런 다음 역할 생성을 확인합니다. 후속 단계에서는 데이터베이스 권한 부여 및 테스트, 사용자 전환을 통한 액세스 확인, 역할에서 권한 취소를 다룹니다.

역할 생성

이 단계에서는 PostgreSQL 에서 역할을 생성하는 방법을 배웁니다. 역할은 데이터베이스 액세스 및 권한을 관리하는 데 사용됩니다.

PostgreSQL 서버에 연결:

터미널을 열고 postgres 사용자로 psql 명령줄 도구를 사용하여 PostgreSQL 서버에 연결합니다.
```
sudo -u postgres psql
```
다음과 유사한 프롬프트가 표시되어야 합니다.
```
postgres=#
```
reporting_role 역할 생성:

이제 CREATE ROLE 명령을 사용하여 reporting_role을 생성합니다. 데이터베이스에 직접 연결하는 데 사용할 수 없음을 의미하는 NOLOGIN 속성을 지정합니다. 이 역할은 다른 역할이나 사용자에게 권한을 부여하는 데만 사용됩니다.
```
CREATE ROLE reporting_role NOLOGIN;
```
이 명령은 역할을 생성합니다. 다음과 같은 출력이 표시되어야 합니다.
```
CREATE ROLE
```
역할 생성 확인:

역할이 생성되었는지 확인하려면 \du 명령 (\list users의 약어) 을 사용할 수 있습니다. 이 명령은 데이터베이스의 모든 역할을 나열합니다.
```
\du
```
역할 목록에서 reporting_role을 볼 수 있어야 합니다. 출력은 다음과 유사하게 표시됩니다.
```
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------------------+-----------
labex | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
reporting_role | Cannot login | {}
```
psql 쉘을 종료합니다.
```
\q
```

데이터베이스 권한 부여

이 단계에서는 역할에 데이터베이스 권한을 부여하는 방법을 배웁니다. 이전 단계에서 생성한 reporting_role을 기반으로 합니다.

PostgreSQL 서버에 연결:

postgres 사용자로 psql 명령줄 도구를 사용하여 PostgreSQL 서버에 연결합니다.
```
sudo -u postgres psql
```
데이터베이스 생성:

reporting_role이 액세스할 reporting_db라는 데이터베이스를 생성해 보겠습니다.
```
CREATE DATABASE reporting_db;
```
다음과 같은 출력이 표시되어야 합니다.
```
CREATE DATABASE
```
reporting_db 데이터베이스에 연결:

새로 생성된 데이터베이스에 연결합니다.
```
\c reporting_db
```
프롬프트가 reporting_db=#로 변경되어야 합니다.
테이블 생성:

reporting_db 데이터베이스 내에 sales_data라는 간단한 테이블을 생성해 보겠습니다.
```
CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product VARCHAR(255),
    amount DECIMAL
);
```
다음과 같은 출력이 표시되어야 합니다.
```
CREATE TABLE
```
sales_data 테이블에 reporting_role로 SELECT 권한 부여:

이제 reporting_role에 sales_data 테이블에 대한 SELECT 권한을 부여합니다. 이를 통해 reporting_role의 멤버인 모든 역할 또는 사용자는 sales_data 테이블에서 데이터를 읽을 수 있습니다.
```
GRANT SELECT ON sales_data TO reporting_role;
```
다음과 같은 출력이 표시되어야 합니다.
```
GRANT
```
psql 쉘을 종료합니다.
```
\q
```

사용자 생성 및 권한 테스트

이 단계에서는 사용자를 생성하고 reporting_role에 멤버로 추가하여 권한을 테스트합니다.

PostgreSQL 서버에 연결:

postgres 사용자로 psql 명령줄 도구를 사용하여 PostgreSQL 서버에 연결합니다.
```
sudo -u postgres psql
```
reporting_db 데이터베이스에 연결:

reporting_db 데이터베이스에 연결합니다.
```
\c reporting_db
```
사용자 생성 및 reporting_role에 멤버로 추가:

report_user라는 새 사용자를 생성해 보겠습니다. 그런 다음 이 사용자를 reporting_role에 멤버로 추가합니다. 이는 report_user가 reporting_role에 부여된 권한을 상속받게 됨을 의미합니다.
```
CREATE USER report_user;
GRANT reporting_role TO report_user;
```
다음과 같은 출력이 표시되어야 합니다.
```
CREATE ROLE
GRANT ROLE
```
비밀번호 없는 인증을 위한 PostgreSQL 구성:

기본적으로 PostgreSQL 은 peer 인증을 사용하며, 이는 이 실습에서 연결 문제를 일으킬 수 있습니다. report_user의 액세스를 단순화하기 위해 trust로 변경하겠습니다.

먼저 psql 쉘을 종료합니다.
```
\q
```
이제 pg_hba.conf 파일을 수정하여 인증 방법을 변경합니다. 아래 sed 명령은 원본 파일을 pg_hba.conf.bak로 백업하고 로컬 연결에 대해 peer를 trust로 바꿉니다.
```
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf
```
PostgreSQL 을 다시 로드하여 변경 사항을 적용합니다.
```
sudo service postgresql reload
```
sales_data 테이블에 데이터 삽입:

postgres 사용자로 데이터베이스에 다시 연결합니다.
```
sudo -u postgres psql -d reporting_db
```
테스트 목적으로 sales_data 테이블에 샘플 데이터를 삽입합니다.
```
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);
```
다음과 같은 출력이 표시되어야 합니다.
```
INSERT 0 3
```
psql 쉘을 종료합니다.
```
\q
```
report_user로 reporting_db 데이터베이스에 연결:

새 터미널 창을 열고 다음 명령을 사용합니다.
```
psql -d reporting_db -U report_user
```
비밀번호 프롬프트 없이 report_user로 데이터베이스에 연결됩니다.
권한 테스트:

이제 sales_data 테이블에서 데이터를 선택해 보겠습니다.
```
SELECT * FROM sales_data;
```
삽입한 데이터가 표시되어야 합니다.
```
 id | product  | amount
----+----------+--------
  1 | Laptop   | 1200.00
  2 | Mouse    |   25.00
  3 | Keyboard |   75.00
(3 rows)
```
이 출력이 표시되면 report_user가 reporting_role을 통해 부여된 sales_data 테이블에 대한 SELECT 권한을 가지고 있음을 의미합니다.

psql 쉘을 종료합니다.
```
\q
```

권한 회수

이 단계에서는 reporting_role에서 SELECT 권한을 회수하고 report_user가 더 이상 sales_data 테이블에 액세스할 수 없음을 확인합니다.

postgres 사용자로 PostgreSQL 서버에 연결:

postgres 사용자로 연결된 터미널로 돌아갑니다. postgres 사용자로 psql 명령줄 도구를 사용하여 PostgreSQL 서버에 연결합니다.
```
sudo -u postgres psql
```
reporting_db 데이터베이스에 연결:

reporting_db 데이터베이스에 연결합니다.
```
\c reporting_db
```
reporting_role에서 SELECT 권한 회수:

이제 reporting_role에서 sales_data 테이블에 대한 SELECT 권한을 회수합니다.
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
다음과 같은 출력이 표시되어야 합니다.
```
REVOKE
```
psql 쉘을 종료합니다.
```
\q
```
report_user로 reporting_db 데이터베이스에 연결:

report_user로 연결된 터미널로 돌아갑니다.
```
psql -d reporting_db -U report_user
```
report_user로서 액세스 거부 확인:

report_user로서 SELECT 쿼리를 다시 실행하여 더 이상 sales_data 테이블의 데이터에 액세스할 수 없음을 확인합니다.
```
SELECT * FROM sales_data;
```
다음과 유사한 오류 메시지가 표시되어야 합니다.
```
ERROR:  permission denied for table sales_data
```
이는 report_user가 reporting_role에서 권한이 회수되었기 때문에 더 이상 sales_data 테이블에 대한 SELECT 권한을 가지고 있지 않음을 확인합니다.

psql 쉘을 종료합니다.
```
\q
```

요약

이 실습에서는 PostgreSQL 에서 역할 생성, 권한 부여, 사용자 생성, 사용자에게 역할 할당, 권한 회수 방법을 배웠습니다. 이들은 데이터베이스 보안 및 액세스 제어 관리를 위한 기본적인 개념입니다.