How can I track failed attempts to log in to my Amazon RDS for PostgreSQL DB instance?

3 minute read

I want to track the number of failed connections to my Amazon Relational Database Service (Amazon RDS) for PostgreSQL DB instance.

Resolution

PostgreSQL generates new log files every hour. To track failed attempts to log in to a DB instance that runs PostgreSQL, turn on the log_connections parameter. The parameter's found in the custom parameter group that's associated with the DB instance. Then, when a user attempts to log in to your DB instance with the wrong credentials, the failed attempts are recorded to the log. The failed attempts look similar to the following example: "error/postgresql.log.2018-04-19-10." For more information, see Working with parameter groups.

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)

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's given to the PostgreSQL database process.

Parsing the log file

Use the AWS Command Line Interface (AWS CLI) or Amazon RDS console to download the log. 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're 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, then publish Amazon RDS for PostgreSQL logs to Amazon CloudWatch logs.