How can I track failed attempts to log in to my Amazon RDS DB instance that's running PostgreSQL?

Resolution

PostgreSQL generates new log files every hour. To track failed attempts to log in to a DB instance that is running PostgreSQL, enable the log_connections parameter in the custom parameter group that is associated with the DB instance. For more information, see Modifying Parameters in a DB Parameter Group. After you enable the log_connections parameter, when a user attempts to log in to your DB instance with the wrong credentials, the failed attempts are recorded to the log, for example, error/postgresql.log.2018-04-19-10.

See the following example of an unsuccessful login attempt:

2018-04-19 10:12:31 UTC:123.45.67.8(6789):[unknown]@[unknown]:[12507]:LOG: connection received: host=123.45.67.8port=12345
2018-04-19 10:12:31 UTC:123.45.67.8(6789):test_user@Test_DB:[12507]:FATAL: password authentication failed for user "test_user"

You can also use the PostgreSQL log_connections parameter to record successful login attempts to a DB instance that is running PostgreSQL.

See the following example of a successful login attempt:

2018-04-19 10:19:20 UTC:123.45.67.8(6789):[unknown]@[unknown]:[17196]:LOG: connection received: host=123.45.67.8 port=12345
2018-04-19 10:19:20 UTC:123.45.67.8(6789):test_user@Test_DB:[17196]:LOG: connection authorized: user=test_user database=Test_DB SSL enabled (protocol=xxxx, cipher=xxxx, compression=off)

You can use the log_connections and the log_disconnections parameters to record the duration of a user's session.

See the following example for the duration of a session:

2018-04-19 10:19:43 UTC:123.45.67.8(6789):test_user@Test_DB:[17196]:LOG: disconnection: session time: 0:00:23.753 user=test_user database=Test_DB host=123.45.67.8 port=12345

Note: The IP address of the client machine is 123.45.67.8, and 6789 is the PID (Process ID) that is given to the PostgreSQL database process.

Parsing the log file

Download the log using the AWS Command Line Interface (AWS CLI) or using the Amazon RDS console. Then, use the grep utility to search the failed connections:

Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent AWS CLI version.

$ grep -B 1 "authentication failed" postgresql.log.2018-04-19-10
2018-04-19 10:12:31 UTC:123.45.67.8(6789):[unknown]@[unknown]:[12507]:LOG: connection received: host=123.45.67.8port=12345
2018-04-19 10:12:31 UTC:123.45.67.8(6789):test_user@Test_DB:[12507]:FATAL: password authentication failed for user "test_user"

Note: The - B 1 parameter tells grep to also show the line that precedes the lines that match the pattern that you are searching for. In this example, the pattern is authentication failed.

You can also use the PostgreSQL log_fdw extension to access these logs as foreign tables. If you want to keep PostgreSQL log files for longer than the retention period, you can export Amazon RDS PostgreSQL logs to CloudWatch logs if you use PostgreSQL versions 9.6.6 and above and 10.4 and above.

Related information

How do I enable query logging using Amazon RDS for PostgreSQL?

PostgreSQL Database Log Files

Exporting Log Data to Amazon S3

Publishing Aurora PostgreSQL Logs to Amazon CloudWatch Logs