Amazon Redshift
Database Developer Guide


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.
iamauthguid character(36) The IAM authentication ID for the CloudTrail request.
application_name character(250) The initial or updated name of the application for a session.

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 | | 11111 2014-11-05 20:30:29 | rdsdb | dev | | 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 | | john authenticated | 2012-10-25 14:42:10.87613 | | john initiating session | 2012-10-25 14:42:10.87638 | | john disconnecting session | 2012-10-25 14:42:19.95992 | | john (4 rows)