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

Last updated: 2019-05-09

I need to track the number of failed connections to my Amazon Relational Database Service (Amazon RDS) instance. How can I track failed attempts to log in to my Amazon RDS DB instance that is 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, if 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:

$ 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.