sys.log_authentication

This view records the history of users' attempts to authenticate with the database and logs success or failure details in each case.

Column Name Data Type Description
start_time timestamptz When the authentication attempt started.
user_name name The name of the user for the connection.
database_name name The database name.
event text AUTHENTICATION
client_ip_address text IP address for remote connections (or [local] for local connections).
client_port text Port number for remote connections.
status integer 0 for success; -1 for failure.
status_detail text Message associated with failures (or SUCCESS for successful authentications).

Examples

Return authentication records for non-system users:
premdb=# select * from sys.log_authentication where user_name not like 'sys%';
         start_time         |  user_name  | database_name |     event      | client_ip_address | client_port | status |                 status_detail                  
----------------------------+-------------+---------------+----------------+-------------------+-------------+--------+------------------------------------------------
 2018-07-20 11:26:52.798-07 | brumsby     | yellowbrick   | Authentication | [local]           |             |      0 | SUCCESS
 2018-07-20 11:26:59.22-07  | brumsby     | premdb        | Authentication | [local]           |             |      0 | SUCCESS
 2018-07-20 18:43:31.995-07 | brumsby     | premdb        | Authentication | [local]           |             |      0 | SUCCESS
 2018-07-20 18:44:37.141-07 | bobr        | premdb        | Authentication | [local]           |             |      0 | SUCCESS
 2018-07-20 18:46:12.87-07  | brumsby     | premdb        | Authentication | [local]           |             |      0 | SUCCESS
 2018-07-23 18:10:19.87-07  | brumsby     | premdb        | Authentication | [local]           |             |      0 | SUCCESS
 2018-07-23 18:24:56.439-07 | yellowbrick | yellowbrick   | Authentication | 127.0.0.1         | 53884       |      0 | SUCCESS
 2018-07-24 11:33:45.312-07 | brumsby     | premdb        | Authentication | [local]           |             |      0 | SUCCESS
 2018-07-24 18:16:17.993-07 | bobr        | premdb        | Authentication | [local]           |             |     -1 | password authentication failed for user "bobr"
(9 rows)
Return failed authentication records: 
premdb=# select * from sys.log_authentication where status !=0;
         start_time         | user_name | database_name |     event      | client_ip_address | client_port | status |                 status_detail                  
----------------------------+-----------+---------------+----------------+-------------------+-------------+--------+------------------------------------------------
 2018-07-24 18:16:17.993-07 | bobr      | premdb        | Authentication | [local]           |             |     -1 | password authentication failed for user "bobr"
(1 row)