How do I troubleshoot password authentication issues in my Amazon Redshift cluster?

Short description

When an Amazon Redshift database user password expires or the user uses a wrong password, authentication fails.

You receive the following error:

"FATAL: password authentication failed for user "<database user name>"".

If you can access the Amazon Redshift superuser account, then you can reset the password for the Amazon Redshift database user. For more information about password constraints, see CREATE USER.

Resolution

Check the history of changes for the database user and the latest configuration

Complete the following steps:

1. Open the Amazon Redshift console.

2. Use a SQL client to connect to Amazon Redshift.

3. Connect to Amazon Redshift as a superuser.
   Note: If you need to reset the password for the superuser account, see How do I reset the account password in Amazon Redshift?

4. Run the following command to confirm the history of changes for the database user and the latest configuration:

   select * from SYS_USERLOG where user_name = 'database username' order by record_time;

   Note: Replace database username with your Amazon Redshift database username.

Set a new password

Note: In the following commands, replace database username your Amazon Redshift database username and new password with a new password. Also, replace new expiration date with a new expiration date.

To reset the password with a new expiration date, run the ALTER USER command with the VALID UNTIL option to set the new date:

alter user database username password 'new password' valid until 'new expiration date'

To reset the password without an expiration date, run the ALTER USER command and set the VALID UNTIL option to infinity:

alter user database username password 'new password' valid until 'infinity'

Related information

SYS_USERLOG