🧐 개요
이번 포스트는 PostgreSQL 서버에서 유저를 생성하고 권한을 부여하는 방법을 기술합니다.
데이터베이스 내부에는 일반적으로 다양한 종류의 데이터가 기록되어 있으며, 많은 서비스들이 데이터베이스 내부의 데이터를 활용합니다. 따라서 데이터베이스에 접근하는 사용자가 많은 만큼, 권한을 차등 분배하여 적절한 보안 설계를 진행할 필요가 있습니다. 모든 사용자가 동일한 권한을 가진다면, 실수로 데이터베이스를 훼손하는 경우가 발생할 수 있기 때문이죠.
🛠️ 유저 생성하기
-- 유저 생성
CREATE USER <user_이름> WITH PASSWORD '<비밀번호>';
-- 0.0.0.0 접속 허용
ALTER USER <user_이름> CONNECTION LIMIT -1;
-- 데이터베이스 생성 권한 부여
ALTER USER <user_이름> CREATEDB;
처음 생성하는 유저에게 ROOT 권한을 부여하기 위해서는 위와 같이 설정할 수 있습니다.
CONNECTION LIMIT 항목을 -1로 설정하면 외부 IP에서의 접속을 허용하게 됩니다.
생성한 유저를 통한 PostgreSQL 서버 접속은 아래의 명령어를 통해 진행할 수 있습니다.
# 데이터베이스 접속
psql -U <user_이름> -d <database_이름> -h <host_주소> -p <port_번호>
MySQL과는 다르게, PostgreSQL은 유저의 권한을 IP 주소로 관리해주지 않습니다. 따라서 전역 접속 허용 또는 비허용만이 가능합니다.
특정 유저의 접속을 IPv4 단위로 제한하려면 관련 환경 설정 파일을 구성하고 서버를 재시동해야 합니다.
🛠️ 권한 부여하기
-- 사용자 생성 권한 부여
GRANT CREATE ROLE TO <user_이름>;
-- 사용자 생성 권한 제거
REVOKE CREATE ROLE FROM <user_이름>;
-- 특정 데이터베이스에 대한 모든 권한 부여
GRANT ALL PRIVILEGES ON DATABASE <database_이름> TO <user_이름>;
-- 특정 테이블에 대한 모든 권한 부여
GRANT ALL PRIVILEGES ON TABLE <database_이름>.<table_name> TO <user_이름>;
-- 특정 데이터베이스/테이블에 대한 권한 부여
-- 예) DROP, CONNECT, SELECT, CREATE
GRANT <permission_type> ON TABLE <table_name> TO <user_이름>;
-- 권한 제거
REVOKE ..
권한을 부여하는 방식은 회사마다 상이하겠으나, 유저에게 데이터베이스 관련 권한을 부여하는 경우 다음과 같은 규칙을 따를 수 있을 것입니다.
1. 관리자의 경우, 전체 데이터베이스 및 유저의 생성 및 삭제 권한을 부여받습니다.
2. 개발자의 경우, 특정 데이터베이스 및 내부 테이블의 생성, 삭제 및 수정 권한을 부여받습니다.
3. 서비스, 어플리케이션 또는 클라이언트의 경우, 특정 테이블의 조회 권한을 부여받습니다.
특정 데이터베이스 뿐만이 아니라 테이블을 지정해서 권한을 부여하는 이유는, VIEW 테이블을 생성하여 필요로 하는 정보만을 확인하고 원본 데이터를 보존하도록 설계할 수 있기 때문입니다. 대다수의 RDBMS 시스템은 기존 테이블의 데이터를 조회하는 VIEW 테이블 생성 기능을 지원하고 있습니다.
+ VIEW 테이블 만들기 (예시)
CREATE VIEW <view_table_이름> AS
SELECT
d1.<column_이름>,
d2.<column_이름>,
d2.<column_이름>
FROM
<database_이름> d1
JOIN <database_이름> d2 ON d1.<column_이름> = d2.<column_이름>;
VIEW 테이블은 CREATE VIEW 쿼리를 통해 생성할 수 있습니다.
VIEW 테이블을 만든 후, 어플리케이션 또는 클라이언트 계정에 VIEW 테이블 접속 권한을 부여하면 필요로 하는 부분의 데이터만 서빙하여 권한을 최소화하고 보안을 강화할 수 있을 것입니다.
📝 마치며
PostgreSQL은 MySQL 만큼은 아니지만, 비교적 사용자 친화적인 RDBMS 서비스를 제공하고 있어 많은 기업에서 두루 사용하고 있습니다. MySQL과 문법 측면에서 공유하는 부분이 크기 때문에, 이미 MySQL 환경에서의 데이터베이스 설계가 익숙하신 분들이라면 함께 공부하기 좋은 분야라고 생각합니다.
'데이터 이모저모 > SQL' 카테고리의 다른 글
[PostgreSQL] 데이터베이스 백업 및 S3 업로드 (1) | 2023.12.27 |
---|---|
[PostgreSQL] 외부 IP로부터의 접속 관리하기(pg_hba, postgresql) (0) | 2023.12.23 |
발자취를 로그처럼 남기고자 하는 초보 개발자의 블로그