PostgreSQL如何获取登录用户IP与查询信息？解决单IP仅一行问题

Got it, let's tackle this problem step by step. I see you're trying to track logged-in users, their IPs, and the queries they're running in PostgreSQL—including both HeidiSQL sessions and your web app—but you're only getting one row per IP from pg_stat_activity. Let's fix that.

Each row in pg_stat_activity represents a single database session, not a single IP. If you're only seeing one row per IP, it's almost certainly because:

• You accidentally used a GROUP BY client_addr clause in your query (which merges all sessions from the same IP into one row)
• You filtered out idle sessions (but HeidiSQL/web apps often keep idle sessions open for reuse)
• Your web app uses a connection pool, so it only maintains a small number of persistent sessions (not one per request)

Use this query to fetch every active/idle session with user, IP, and query info—no more merging rows per IP:

SELECT
  usename AS logged_in_user,
  client_addr AS ip_address,
  application_name AS client_application,
  state AS session_state,
  TRIM(query) AS executed_query,
  query_start AS query_start_time,
  backend_start AS session_start_time
FROM pg_stat_activity
WHERE
  -- Exclude internal PostgreSQL system processes
  usename IS NOT NULL
  -- Only include sessions with a connected IP (skip local socket connections if needed)
  AND client_addr IS NOT NULL
  -- Include idle sessions that have run queries before (remove this if you only want active queries)
  AND (state <> 'idle' OR query IS NOT NULL)
ORDER BY query_start DESC;

Key Fields Explained: #

• application_name: This is your best way to distinguish HeidiSQL (usually shows up as HeidiSQL) from your web app. For web apps, you can even customize this value in your connection string (e.g., add ApplicationName=MyWebApp to your JDBC/ODBC URL) to make it easier to filter.
• session_state: active means the query is running right now; idle means the session is waiting for the next command (HeidiSQL keeps idle sessions open when you have tabs open).
• executed_query: For active sessions, this is the currently running query. For idle sessions, it's the last query that ran (as long as your track_activity_query_size config is large enough to store it).

• Remove GROUP BY client_addr: If your original query had this clause, that's why you're getting one row per IP. Ditch it to see every individual session.
• Don't filter out idle sessions: HeidiSQL and web connection pools keep sessions idle for reuse—these still have valuable query history in the query field.
• Check connection pool settings: If your web app uses a pool (like HikariCP or pgBouncer), you'll only see as many rows as the pool's maximum size. To track individual user queries in this case, you'll need to add logging in your web app (e.g., log the user ID alongside each query) or use PostgreSQL's query logging.

If you need to track every query that's ever run (not just current/recent sessions), configure PostgreSQL to log all queries:

1. Edit your postgresql.conf file:

   # Log all statements (use 'mod' instead of 'all' to only log data-modifying queries)
   log_statement = 'all'
   # Log connections/disconnections to track when users log in
   log_connections = on
   log_disconnections = on
   # Add context to each log line (user, IP, database, timestamp)
   log_line_prefix = '%t [%p]: [%c-%l] user=%u,db=%d,client=%h '
   

2. Restart PostgreSQL to apply the changes.
3. Check your PostgreSQL log directory (usually /var/log/postgresql/ on Linux, or in your data folder on Windows) for detailed query logs.

This will give you a complete audit trail of every query, who ran it, where it came from, and when it executed.

内容的提问来源于stack exchange，提问作者Vengat Joy