VPS의 PostgreSQL에서 역할을 사용하고 부여 권한을 관리하는 방법
소개
SQL. PostgreSQL은 Virtual Private Server에서 애플리케이션 및 웹 데이터를 관리하는 데 사용할 수 있는 강력한 도구입니다.
이 가이드는 권한을 적절하게 관리하고 사용자 권한을 부여하는 방법을 보여줍니다. 이렇게 하면 별도의 데이터베이스에 영향을 주지 않고 애플리케이션에 필요한 권한을 제공할 수 있습니다.
전제 조건
이 자습서를 따르려면 다음이 필요합니다.
- Ubuntu 22.04용 초기 서버 설정 가이드에 따라 구성된 Ubuntu 22.04 서버 1개. 이 필수 자습서를 완료한 후 서버에는 sudo 권한과 기본 방화벽이 있는 루트가 아닌 사용자가 있어야 합니다.\n
- Ubuntu 22.04에서 PostgreSQL을 설치하고 사용하는 방법 자습서의 1단계를 완료하여 서버에 Postgres를 설치하려면\n
환경이 준비되고 Postgres가 서버에서 실행되면 Postgres가 권한을 처리하는 방법에 대해 학습할 수 있습니다.
PostgreSQL에서 역할 및 권한 보기
Postgres는 역할 개념을 통해 권한을 관리합니다. 역할은 사용자와 그룹이 구분되지 않는다는 점에서 기존의 Unix 스타일 권한과 다릅니다. 역할은 이러한 두 가지 규칙과 유사하게 조작할 수 있지만 더 유연합니다. 설치 시 Postgres는 피어 인증을 사용하도록 설정됩니다. 즉, Postgres 역할을 일치하는 Unix/Linux 시스템 계정과 연결합니다. Postgres 내에 역할이 있는 경우 동일한 이름의 Unix/Linux 사용자 이름이 해당 역할로 로그인할 수 있습니다.
설치 절차는 기본 Postgres 역할과 연결된 postgres라는 사용자 계정을 생성했습니다. Postgres를 사용하려면 해당 계정에 로그인하면 됩니다.
먼저 systemctl start 명령을 사용하여 서버가 실행 중인지 확인합니다.
- sudo systemctl start postgresql.service
그런 다음 다음을 입력하여 postgres 계정으로 전환할 수 있습니다.
- sudo -i -u postgres
이제 다음을 입력하여 PostgreSQL 프롬프트에 즉시 액세스할 수 있습니다.
- psql
Postgres 인스턴스의 역할을 나열하려면 다음 명령을 입력하십시오.
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
현재 많은 강력한 권한을 가진 기본 역할은 하나뿐입니다.
PostgreSQL에서 역할 생성
Postgres의 역할을 만드는 방법에는 여러 가지가 있습니다. Postgres 내에서 또는 명령줄에서 역할을 생성할 수 있습니다.
PostgreSQL 내에서 역할 생성
새 역할을 생성하는 한 가지 방법은 Postgres 프롬프트 인터페이스 내에서 생성하는 것입니다. 다음은 Postgres 프롬프트 인터페이스 내에서 새 역할을 생성하기 위한 구문입니다.
- CREATE ROLE new_role_name;
이를 시연하기 위해 demo_role이라는 새 역할을 생성합니다.
- CREATE ROLE demo_role;
정의된 사용자를 다시 확인하십시오.
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
출력에 두 명의 사용자가 표시됩니다.
명령줄에서 역할 만들기
역할을 만드는 다른 방법은 명령줄에서 createuser 명령을 사용하는 것입니다.
먼저 다음을 입력하여 PostgreSQL 명령 프롬프트를 잠시 종료합니다.
- \q
그런 다음 postgres 계정에 로그인합니다.
- sudo -i -u postgres
createuser 명령을 사용하여 명령줄에서 새 역할을 만들 수 있습니다. --interactive 플래그를 사용하면 새 역할의 이름을 묻는 메시지가 표시되고 수퍼유저 권한이 있어야 하는지도 묻습니다.
postgres 계정으로 로그인한 후 다음을 입력하여 새 사용자를 만들 수 있습니다.
- createuser --interactive
스크립트는 몇 가지 선택 사항을 묻는 메시지를 표시하고 응답에 따라 사양에 맞는 올바른 Postgres 명령을 실행합니다.
OutputEnter name of role to add: test_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
이러한 모든 프롬프트에 대해 n(아니요)으로 응답하면 이전 사용자와 유사한 사용자를 생성하게 됩니다.
psql Postgres 프롬프트에 다시 로그인합니다.
- psql
그런 다음 du 명령을 실행하여 두 가지 새로운 역할 간의 차이점을 확인합니다. 이 명령은 PostgreSQL이 아닌 psql 자체에서 처리하는 psql 특정 메타 명령이기 때문에 \\로 시작합니다.
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
명령줄에서 생성된 사용자는 속성으로 나열된 로그인할 수 없음이 없습니다.
PostgreSQL에서 역할 삭제
다음 구문을 사용하여 역할을 삭제할 수 있습니다.
- DROP ROLE role_name;
시연하려면 다음을 입력하여 demo_role 역할을 삭제합니다.
- DROP ROLE demo_role;
존재하지 않는 사용자에 대해 명령을 실행하면 오류 메시지가 표시됩니다.
OutputERROR: role "demo_role" does not exist
이 상황을 방지하고 drop 명령이 사용자가 있는 경우 삭제하고 사용자가 존재하지 않는 경우 아무 작업도 수행하지 않도록 하려면 다음 구문을 사용하십시오.
- DROP ROLE IF EXISTS role_name;
이 옵션을 지정하면 역할의 유효성에 관계없이 명령이 성공적으로 완료됩니다. 위의 명령으로 demo_role을 제거하려고 하면 다음과 같은 결과가 나타납니다.
- DROP ROLE IF EXISTS demo_role;
OutputNOTICE: role "demo_role" does not exist, skipping
DROP ROLE
이제 역할이 삭제되었습니다.
역할 생성 시 권한 정의
이제 변경된 권한으로 demo_role을 다시 만들 준비가 되었습니다. 다음과 같이 기본 create 절 뒤에 원하는 권한을 지정하여 이를 수행할 수 있습니다.
- CREATE ROLE role_name WITH assigned_permissions;
전체 옵션 목록을 보려면 다음을 입력하십시오.
- \h CREATE ROLE
OutputCommand: CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/14/sql-createrole.html
다음을 입력하여 demo_role 사용자에게 로그인 권한을 부여할 수 있습니다.
- CREATE ROLE demo_role WITH LOGIN;
\\du 명령으로 속성을 확인하면 이제 두 사용자가 동일한 권한을 갖게 됩니다.
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
역할을 생성할 때마다 LOGIN 속성을 지정하지 않고도 이 상태에 도달할 수 있습니다. 다음 CREATE USER 명령을 사용하면 역할 로그인 권한이 자동으로 부여됩니다.
- CREATE USER role_name;
역할은 자동으로 부여된 권한으로 생성됩니다.
PostgreSQL에서 역할 권한 변경
이미 생성된 역할의 속성을 변경하려면 ALTER ROLE 명령을 사용합니다. 이 명령의 구문은 다음과 같습니다.
- ALTER ROLE role_name WITH attribute_options;
이 명령을 사용하면 앞에서 설명한 것처럼 사용자를 삭제하고 다시 만들지 않고도 권한 변경을 정의할 수 있습니다. 예를 들어 다음 명령을 실행하여 demo_role을 로그인할 수 없음의 이전 상태로 다시 변경할 수 있습니다.
- ALTER ROLE demo_role WITH NOLOGIN;
\\du 명령으로 변경 사항을 확인할 수 있습니다.
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
로그인 액세스 권한이 있는 역할로 다시 변경하려면 다음 명령을 사용하십시오.
- ALTER ROLE demo_role WITH LOGIN;
이제 역할이 되돌려졌습니다.
PostgreSQL에서 다른 사용자로 로그인
기본적으로 사용자는 시스템 사용자 이름이 PostgreSQL 사용자 이름과 일치하는 경우에만 로컬로 로그인할 수 있습니다. 로그인 유형을 변경하거나 PostgreSQL이 루프백 네트워크 인터페이스를 사용하도록 지정하여 이를 변경할 수 있습니다. 이렇게 하면 실제로는 로컬 연결이더라도 연결 유형이 원격으로 변경됩니다.
먼저 인증할 수 있도록 연결하려는 사용자의 암호를 만듭니다. 이전에 생성한 test_user에 암호를 지정하여 이를 시도할 수 있습니다.
- \password test_user
암호를 입력하고 확인하라는 메시지가 표시됩니다. 이제 PostgreSQL 인터페이스를 종료하고 다음 명령을 사용하여 일반 사용자로 돌아갑니다.
- \q
PostgreSQL은 로그인할 때 운영 체제 사용자 이름과 일치하는 사용자 이름을 사용하고 동일한 이름의 데이터베이스에 연결한다고 가정합니다.
사용하려는 옵션을 명시적으로 지정하려면 매개변수와 함께 다음 구문을 사용하십시오.
- psql -U user_name -d database_name -h 127.0.0.1 -W
다음은 명령의 각 항목에 대한 간략한 분석입니다.
user_name은 연결하려는 사용자 이름으로 대체되어야 합니다.database_name은 액세스 권한이 있는 기존 데이터베이스의 이름이어야 합니다.-h 127.0.0.1섹션은 네트워크 인터페이스를 통해 로컬 시스템에 연결하도록 지정하는 부분으로, 시스템 사용자 이름이 인증되지 않더라도 인증할 수 있습니다. 일치합니다.-W플래그는 PostgreSQL에 암호를 입력할 것임을 알립니다.
test_user로 로그인하려면 다음 명령을 실행하십시오.
- sudo psql -U test_user -d postgres -h 127.0.0.1 -W
이 명령 다음에 암호를 입력해야 합니다.
이 예에서는 데이터베이스 postgres를 사용합니다. 이것은 설치 중에 설정된 기본 데이터베이스입니다. 이 세션에서 몇 가지 작업을 수행하려고 하면 많은 작업을 수행할 수 없음을 알 수 있습니다. 이는 test_user에게 관리 권한이 부여되지 않았기 때문입니다.
현재 세션을 종료합니다.
- \q
그런 다음 관리 postgres 세션으로 돌아갑니다.
- sudo u - postgres psql
다음으로 권한을 부여합니다.
PostgreSQL에서 권한 부여
데이터베이스나 테이블이 생성되면 일반적으로 수퍼유저 상태의 역할을 제외하고 이를 생성한 역할만 수정 권한을 가집니다. 이 동작은 다른 역할에 권한을 부여하여 변경할 수 있습니다.
다음 일반 구문과 함께 GRANT 명령을 사용하여 권한을 부여할 수 있습니다.
- GRANT permission_type ON table_name TO role_name;
다음 명령을 사용하여 이러한 개념을 연습하는 테이블을 만들 수 있습니다.
- CREATE TABLE demo (
- name varchar(25),
- id serial,
- start_date date);
생성한 테이블을 보려면 다음 명령을 입력합니다.
- \d
Output List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
(2 rows)
하나의 table 유형과 하나의 sequence 유형이 있습니다. 시퀀스는 테이블 생성에서 id serial 명령을 사용할 때 생성됩니다. 이렇게 하면 자동 증가 정수가 생성됩니다.
이제 새 데모 테이블에 대한 일부 권한을 demo_role에 부여할 수 있습니다. 이렇게 하려면 다음 명령을 사용하여 demo_role 사용자 UPDATE 권한을 부여합니다.
- GRANT UPDATE ON demo TO demo_role;
권한 유형을 ALL이라는 단어로 대체하여 사용자에게 전체 권한을 부여할 수 있습니다. 다음 명령을 사용하여 test_user에게 이 권한을 부여합니다.
- GRANT ALL ON demo TO test_user;
시스템의 모든 사용자에 대한 권한을 지정하려면 특정 사용자 대신 PUBLIC을 사용할 수 있습니다.
- GRANT INSERT ON demo TO PUBLIC;
그랜트 테이블을 보려면 다음 명령을 사용하십시오.
- \z
Output Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+----------+----------------------------+-------------------+----------
public | demo | table | postgres=arwdDxt/postgres +| |
| | | demo_role=w/postgres +| |
| | | test_user=arwdDxt/postgres+| |
| | | =a/postgres | |
public | demo_id_seq | sequence | | |
(2 rows)
이렇게 하면 할당된 모든 부여 권한이 표시됩니다.
PostgreSQL에서 권한 제거
REVOKE 명령을 사용하여 권한을 제거할 수 있습니다. REVOKE 명령은 권한 부여와 거의 동일한 구문을 사용합니다.
- REVOKE permission_type ON table_name FROM user_name;
명령에서도 ALL 및 PUBLIC과 같은 속기 단어를 사용할 수 있습니다.
- REVOKE INSERT ON demo FROM PUBLIC;
이전에 설정한 권한이 이제 취소되었습니다.
PostgreSQL에서 그룹 역할 사용
역할은 다른 역할을 그룹화하여 광범위한 권한 제어를 허용할 만큼 충분히 유연합니다. 예를 들어, temporary_users라는 새 역할을 만든 다음 해당 그룹에 demo_role 및 test_user를 추가할 수 있습니다.
먼저 그룹으로 사용할 새 역할을 만듭니다.
- CREATE ROLE temporary_users;
그런 다음 새로 생성된 temporary_users 그룹에 사용자를 할당합니다.
- GRANT temporary_users TO demo_role;
- GRANT temporary_users TO test_user;
이제 이 두 사용자는 각 구성원을 개별적으로 관리하는 대신 temporary_users 그룹 역할을 조작하여 권한을 관리할 수 있습니다.
다음을 입력하여 역할 구성원 정보를 볼 수 있습니다.
- \du
Output List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------
demo_role | | {temporary_users}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
temporary_users | Cannot login | {}
test_user | | {temporary_users}
그룹 역할의 모든 구성원은 SET ROLE 명령을 사용하여 구성원인 그룹 역할로 작동할 수 있습니다. 로그인한 postgres 사용자는 현재 수퍼유저 권한을 가지고 있으므로 temporary_users 그룹의 구성원이 아니더라도 SET ROLE 명령을 사용할 수 있습니다.
- SET ROLE temporary_users;
이제 생성된 모든 테이블은 temporary_users 역할이 소유합니다.
- CREATE TABLE hello (
- name varchar(25),
- id serial,
- start_date date);
이제 다음 명령을 실행하여 테이블 소유권을 확인하십시오.
- \d
Output List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-----------------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | temporary_users
public | hello_id_seq | sequence | temporary_users
(4 rows)
새 테이블 및 직렬 데이터 유형과 연관된 시퀀스는 temporary_users 역할이 소유합니다.
원래 역할 권한으로 돌아가려면 다음 명령을 입력하십시오.
- RESET ROLE;
사용자에게 ALTER ROLE 명령과 함께 INHERIT 속성을 부여하면 해당 사용자는 SET ROLE을 사용하지 않고 자동으로 자신이 속한 역할의 모든 권한을 갖게 됩니다. 명령:
- ALTER ROLE test_user INHERIT;
이제 test_user는 자신이 속한 역할의 모든 권한을 가집니다. DROP ROLE 명령을 사용하여 그룹 역할 또는 모든 역할을 제거할 수 있습니다. 다음 명령을 입력하여 temporary_users 그룹으로 이를 테스트할 수 있습니다.
- DROP ROLE temporary_users;
OutputERROR: role "temporary_users" cannot be dropped because some objects depend on it
DETAIL: owner of sequence hello_id_seq
owner of table hello
이것은 hello 테이블이 temporary_users의 소유이기 때문에 오류를 출력합니다. 소유권을 다른 역할로 이전하여 이 문제를 해결할 수 있습니다.
- ALTER TABLE hello OWNER TO demo_role;
다음을 사용하여 temporary_users가 더 이상 테이블을 소유하지 않는지 확인할 수 있습니다.
- \d
Output List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-----------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | demo_role
public | hello_id_seq | sequence | demo_role
(4 rows)
이제 다음 명령을 실행하여 temporary_users 역할을 성공적으로 삭제할 수 있습니다.
- DROP ROLE temporary_users;
그러면 temporary_users 역할이 삭제됩니다. temporary_users의 이전 구성원은 제거되지 않습니다.
결론
이제 PostgreSQL 데이터베이스 권한을 관리하는 데 필요한 기본 기술을 갖추었습니다. 다른 애플리케이션에서 사용하는 데이터를 방해하지 않으면서 애플리케이션이 필요한 데이터베이스에 액세스할 수 있도록 권한을 관리하는 방법을 아는 것이 중요합니다.
Postgres 및 사용 방법에 대해 자세히 알아보려면 다음 가이드를 확인하는 것이 좋습니다.
- SQLite vs MySQL vs PostgreSQL: 관계형 데이터베이스 관리 시스템 비교
- SQL로 쿼리 실행 연습