Product DocumentationAppearance
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)Parent topic:System Views