PostgreSQL, postgres 관리자 계정 주요 쿼리 정리

1. PostgreSQL 관리자 계정 개요

1.1 PostgreSQL에서의 관리자 계정

PostgreSQL에서 postgres 사용자는 기본적으로 최고 관리자(SUPERUSER) 권한을 가지며, 서버 설치 시 자동으로 생성됩니다. 이 사용자는 모든 데이터베이스 및 시스템 설정을 관리할 수 있습니다.

• PostgreSQL의 최고 관리자 계정: postgres

• 다른 사용자를 최고 관리자로 승격 가능

• 데이터베이스 및 서버 설정 변경 가능

• 다른 사용자의 세션 강제 종료 가능

1.2 현재 PostgreSQL에서 활성화된 사용자 및 역할 확인

SELECT * FROM pg_roles;

이 명령어를 실행하면 현재 PostgreSQL 서버에서 정의된 모든 역할과 권한을 조회할 수 있습니다.

출력 예시:

rolname            | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin
-------------------+---------+--------------+------------+------------
postgres          | t       | t            | t          | t
admin_user        | f       | t            | f          | t
read_only_user    | f       | f            | f          | t

• rolsuper: 최고 관리자 여부 (t = true, f = false)

• rolcreaterole: 새로운 사용자(역할) 생성 가능 여부

• rolcreatedb: 데이터베이스 생성 가능 여부

• rolcanlogin: 로그인 가능 여부

---

2. PostgreSQL 관리자 계정 관리

2.1 새로운 관리자 계정 생성

CREATE USER new_admin WITH PASSWORD 'StrongPassword123!';
ALTER USER new_admin WITH SUPERUSER;

• CREATE USER: 새로운 사용자 계정 생성

• ALTER USER new_admin WITH SUPERUSER: 해당 계정을 최고 관리자 권한으로 승격

2.2 특정 권한을 가진 계정 생성

CREATE USER db_manager WITH PASSWORD 'SecurePass!';
ALTER USER db_manager WITH CREATEDB;
ALTER USER db_manager WITH CREATEROLE;

• CREATEDB: 새로운 데이터베이스를 생성할 수 있는 권한

• CREATEROLE: 새로운 사용자(역할)를 생성할 수 있는 권한

2.3 기존 사용자 권한 확인

SELECT * FROM pg_roles WHERE rolname = 'db_manager';

2.4 특정 사용자 삭제

DROP USER new_admin;

※ 해당 사용자가 소유한 데이터베이스가 있으면 삭제되지 않음 → 먼저 소유권을 변경 후 삭제해야 함.

2.5 사용자 소유권 이전 후 삭제

ALTER DATABASE mydb OWNER TO postgres;
DROP USER db_manager;

---

3. PostgreSQL 관리자 기능 활용

3.1 현재 로그인된 세션 확인

SELECT pid, usename, client_addr, application_name, state FROM pg_stat_activity;

출력 예시:

pid  | usename  | client_addr  | application_name | state  
------+---------+-------------+------------------+--------
 1234 | admin   | 192.168.1.10 | psql             | active
 5678 | guest   | 192.168.1.11 | dbeaver          | idle   

3.2 특정 사용자 세션 강제 종료

SELECT pg_terminate_backend(1234);

• pg_terminate_backend(pid): 특정 프로세스를 강제 종료

• pg_cancel_backend(pid): 실행 중인 쿼리만 취소 (세션 종료 X)

3.3 데이터베이스 연결 강제 종료

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND state = 'active';

---

4. PostgreSQL 보안 및 인증 관리

4.1 현재 인증 모드 확인

SHOW hba_file;

이 명령어를 실행하면 pg_hba.conf 파일의 위치가 출력됨. 해당 파일을 열어 인증 방식을 확인할 수 있음.

4.2 pg_hba.conf 수정 (MD5 암호 인증 설정)

pg_hba.conf 파일을 편집하여 특정 사용자의 로그인 방식을 변경할 수 있음.

# IPv4 로컬 접근 - MD5 암호 인증 사용
host    all             all             0.0.0.0/0              md5

설정 후 PostgreSQL을 재시작해야 적용됨:

sudo systemctl restart postgresql

4.3 SSL 암호화 강제 적용

ALTER SYSTEM SET ssl = 'on';
SELECT pg_reload_conf();

---

5. 데이터베이스 백업 및 복구

5.1 전체 데이터베이스 백업

pg_dump -U postgres -W -F c -b -v -f "backup_file.backup" mydb

• -U postgres: postgres 사용자로 실행
• -W: 비밀번호 입력 요청
• -F c: 사용자 정의 형식으로 백업 (압축 가능)

5.2 특정 테이블 백업

pg_dump -U postgres -W -t my_table -f my_table_backup.sql mydb

5.3 데이터베이스 복구

pg_restore -U postgres -W -d mydb -v "backup_file.backup"

---

6. 성능 및 로그 모니터링

6.1 PostgreSQL 로그 확인

sudo tail -f /var/log/postgresql/postgresql.log

6.2 실행된 최근 쿼리 확인

SELECT query, state, backend_start, query_start, state_change
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

6.3 자동 실행 계획(Autovacuum) 확인

SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_all_tables;

6.4 인덱스 사용량 분석

SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes;

---