STL_CONNECTION_LOG
Logs authentication attempts and connections and disconnections.
This table is visible only to superusers. For more information, see Visibility of Data in System Tables and Views.
Table Columns
| Column Name | Data Type | Description |
|---|---|---|
| event | character(50) | Connection or authentication event. |
| recordtime | timestamp | Time the event occurred. |
| remotehost | character(32) | Name or IP address of remote host. |
| remoteport | character(32) | Port number for remote host. |
| pid | integer | Process ID associated with the statement. |
| dbname | character(50) | Database name. |
| username | character(50) | User name. |
| authmethod | character(32) | Authentication method. |
| duration | integer | Duration of connection in microseconds. |
| sslversion | character(50) | Secure Sockets Layer (SSL) version. |
| sslcipher | character(128) | SSL cipher. |
| mtu | integer | Maximum transmission unit (MTU). |
| sslcompression | character(64) | SSL compression type. |
| sslexpansion | character(64) | SSL expansion type. |
Sample Queries
To view the details for open connections, execute the following query.
select recordtime, username, dbname, remotehost, remoteport from stl_connection_log where event = 'initiating session' and pid not in (select pid from stl_connection_log where event = 'disconnecting session') order by 1 desc; recordtime | username | dbname | remotehost | remoteport --------------------+-------------+------------+---------------+--------------------------------- 2014-11-06 20:30:06 | rdsdb | dev | [local] | 2014-11-06 20:29:37 | test001 | test | 10.49.42.138 | 11111 2014-11-05 20:30:29 | rdsdb | dev | 10.49.42.138 | 33333 2014-11-05 20:28:35 | rdsdb | dev | [local] | (4 rows)
The following example reflects a failed authentication attempt and a successful connection and disconnection.
select event, recordtime, remotehost, username from stl_connection_log order by recordtime; event | recordtime | remotehost | username -----------------------+---------------------------+--------------+--------- authentication failure | 2012-10-25 14:41:56.96391 | 10.49.42.138 | john authenticated | 2012-10-25 14:42:10.87613 | 10.49.42.138 | john initiating session | 2012-10-25 14:42:10.87638 | 10.49.42.138 | john disconnecting session | 2012-10-25 14:42:19.95992 | 10.49.42.138 | john (4 rows)
