PostgreSQL에서 누가 접속했는지 확인할 수 있을까?

PostgreSQL을 사용하다가 문득 궁금한 게 생겼다.

지금 DB에 누가 접속해 있는지는 알 수 있을 것 같은데,
예전에 누가 접속했는지도 확인할 수 있을까?

 

DBeaver나 pgAdmin 같은 툴로 DB에 접속하면 당연히 PostgreSQL 서버와 연결이 만들어진다.

그렇다면 서버 어딘가에는 접속한 계정이나 IP 같은 정보가 남아 있지 않을까 싶어서 확인해봤다.

결론부터 말하면 현재 접속 정보와 과거 접속 이력은 따로 생각해야 했다.

현재 누가 접속해 있는지는 확인할 수 있다

PostgreSQL에는 pg_stat_activity라는 시스템 뷰가 있다.

현재 DB에 연결되어 있는 세션을 확인할 때 사용할 수 있다.

 

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

 

조회해보면 대략 이런 정보가 나온다.

datname    usename    client_addr     application_name    state
-----------------------------------------------------------------
sampledb   user01     192.168.x.x     DBeaver             idle
sampledb   user02     192.168.x.x     pgAdmin             active

 

usename에서는 접속 계정을, client_addr에서는 접속한 클라이언트의 IP를 확인할 수 있다.

application_name도 생각보다 유용했다.

DBeaver나 pgAdmin처럼 클라이언트 프로그램이 자신의 이름을 전달하는 경우에는 어떤 프로그램으로 연결되어 있는지도 확인할 수 있다.

pg_stat_activity 조회 결과 예시 (현재 접속자 확인)

---

active와 idle은 무슨 차이일까?

조회해보니 state에 active, idle 같은 값도 있었다.

처음에는 idle이면 접속이 끊어진 건가 싶었는데 그건 아니었다.

active는 현재 쿼리를 실행하고 있는 세션이고,

idle은 DB 연결은 유지되고 있지만 현재 실행 중인 쿼리는 없는 상태다.

그래서 DBeaver를 켜놓고 아무 작업도 하지 않고 있더라도 pg_stat_activity에는 연결된 세션이 보일 수 있다.

특정 DB만 보고 싶다면 조건을 추가하면 된다.

 

SELECT
    datname,
    usename,
    client_addr,
    application_name,
    state
FROM pg_stat_activity
WHERE datname = 'sampledb';

 

여기까지는 어렵지 않았다.

문제는 과거 접속 기록이었다.

---

어제 누가 접속했는지도 pg_stat_activity로 볼 수 있을까?

pg_stat_activity는 이름 그대로 현재 상태를 보여주는 뷰다.

따라서 어제 접속했다가 이미 연결을 종료한 사용자를 여기서 다시 찾을 수는 없다.

현재 연결 중
      ↓
pg_stat_activity에서 확인 가능

연결 종료
      ↓
pg_stat_activity에서 사라짐

 

그렇다면 과거 기록은 어디에서 봐야 할까?

PostgreSQL 로그를 확인해야 한다.

그런데 여기서 중요한 게 하나 있었다.

PostgreSQL이 원래부터 접속 로그를 기록하도록 설정되어 있어야 한다.

---

log_connections 설정 확인하기

현재 PostgreSQL이 접속 성공 정보를 로그에 남기도록 되어 있는지는 다음 명령으로 확인할 수 있다.

 

SHOW log_connections;

 

예를 들어 결과가:

log_connections
---------------
off

 

라면 새로운 클라이언트가 접속했을 때의 연결 성공 정보를 log_connections 설정으로 기록하고 있지 않은 상태다.

반대로:

log_connections
---------------
on

 

이면 연결이 성공할 때 관련 로그를 남길 수 있다.

연결 종료도 기록하고 싶다면 별도의 설정이 있다.

 

SHOW log_disconnections;

 

이 역시 on으로 설정되어 있어야 연결 종료 정보가 로그에 기록된다.

PostgreSQL 로그 관련 설정 확인 예시

---

로그를 지금 켜면 과거 기록도 볼 수 있을까?

이 부분이 가장 궁금했다.

예를 들어 오늘 확인해보니:

log_connections = off

 

였다고 해보자.

그 상태에서 오늘 on으로 변경하면 어제 접속했던 사람도 확인할 수 있을까?

당연히 그렇지는 않았다.

로그를 남기지 않았던 과거의 접속 정보를 나중에 소급해서 만들어낼 수는 없다.

어제
log_connections = off
→ 접속 로그를 해당 설정으로 남기지 않음

오늘
log_connections = on
→ 이후 발생하는 접속부터 기록

 

그래서 과거 접속 이력이 필요하다면 가장 먼저 해야 할 일은 무작정 로그 파일을 뒤지는 게 아니라 당시 어떤 로깅 설정이 적용되어 있었는지 확인하는 것이었다.

물론 PostgreSQL 외부의 방화벽, 프록시, 운영체제 감사 로그 등 다른 계층에서 별도의 기록을 남기고 있었다면 흔적이 있을 수도 있다.

하지만 그건 PostgreSQL의 log_connections와는 별개의 이야기다.

---

실제 로그에는 어떤 식으로 남을까?

접속 로그가 활성화되어 있다면 PostgreSQL 로그에서 연결 관련 내용을 확인할 수 있다.

여기서 log_line_prefix 설정도 중요하다.

 

SHOW log_line_prefix;

 

이 설정에 따라 로그 앞부분에 시간, 사용자명, 데이터베이스명, 프로세스 ID 등의 정보가 붙는다.

그래서 단순히 로그를 남기는 것뿐만 아니라 나중에 필요한 정보를 추적할 수 있도록 어떤 정보가 기록되는지도 확인할 필요가 있다.

PostgreSQL 접속 로그 예시

---

DBeaver로 접속해도 기록이 남을까?

처음에는 DB에서 직접 SQL을 실행해야만 뭔가 기록되는 건가 싶었는데, DBeaver도 결국 PostgreSQL 서버에 연결하는 클라이언트다.

따라서 DBeaver로 접속하더라도 PostgreSQL 입장에서는 하나의 DB 연결이다.

현재 연결되어 있다면 pg_stat_activity에서 확인할 수 있고, 접속 로그를 남기도록 설정되어 있다면 해당 연결 역시 로그 대상이 될 수 있다.

즉,

DBeaver
   ↓
PostgreSQL 연결
   ↓
pg_stat_activity
   +
접속 로그 설정

 

이라고 생각하면 됐다.

---

결국 현재 접속과 과거 접속은 확인 방법이 달랐다

처음에는 PostgreSQL 어딘가에 접속 이력이 자동으로 계속 쌓여 있을 거라고 막연하게 생각했다.

그런데 확인해보니 그렇지는 않았다.

지금 누가 접속해 있는지 알고 싶다면:

 

SELECT *
FROM pg_stat_activity;

 

과거 누가 접속했는지 알고 싶다면 PostgreSQL 로그를 봐야 하고, 그 전에:

 

SHOW log_connections;
SHOW log_disconnections;

 

으로 접속·종료 로그가 기록되도록 설정되어 있었는지 확인해야 한다.

그리고 이미 로그를 남기지 않았던 과거의 접속을 pg_stat_activity에서 다시 찾아낼 수는 없다.

결국 접속 이력이 나중에 필요할 가능성이 있는 서버라면 문제가 생긴 다음 로그를 찾는 것보다 미리 어떤 로그를 남길지 정해두는 게 중요했다.

다음에는 여기서 한 단계 더 나가서,

접속한 사람은 알겠는데, 그 사람이 어떤 SQL을 실행했는지도 알 수 있을까?

 

이것도 따로 정리해봐야겠다.