PostgreSQL 역할 및 권한 관리

소개

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

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

이것은 가이드 실험입니다. 학습과 실습을 돕기 위한 단계별 지침을 제공합니다.각 단계를 완료하고 실무 경험을 쌓기 위해 지침을 주의 깊게 따르세요. 과거 데이터에 따르면, 이것은 초급 레벨의 실험이며 완료율은 96%입니다.학습자들로부터 93%의 긍정적인 리뷰율을 받았습니다.

역할 생성

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