valid_until is not working for pg_tle

Question

hello,
we recently implemented pg_tle (version 1.4.0) on aws rds (version 13.15) and able to configure a password_check hook which validates password complexity as expected. What we have observed that when valid_until is not working as expected. Our requirement is when password is changed, account expiry should extend to current date + 90 days. We changed the password_check function to update valid_until value but seems its not accepted by postgresql at all.

FUNCTION password_check.passcheck_hook(text, text, pgtle.password_types, timestamp with time zone, boolean)
and following is added while defining this function. This gets called when password gets validated for all checks.

BEGIN
valid_until := current_date + interval '90 days';
execute format('alter role %s valid until %L',username,valid_until);
END

I get following error whichever way i try to call alter role to update expiry

ERROR:  attempted to update invisible tuple

SQL state: 55000

This behaviour is seen using PGAdmin tool only.

if i invoke SELECT password_check.passcheck_hook('abcd','Abcd123','PASSWORD_PLAIN_TEXT',' null, false) manually, then account expiry gets changed to current_date + 90 days (as expected)

re:Post Agent · Answer

The password_check hook is designed to validate passwords, not to modify user attributes like the valid_until timestamp. When you attempt to alter the role within the hook function, you're trying to modify the database during a process that's not intended for such modifications, leading to the "attempted to update invisible tuple" error.

It's however successful if you manually invoke it outside the context of the password change.

You can consider below options:

1. Create a separate function/trigger that runs after a successful password change to update the valid_until timestamp.
2. Implement a custom authentication method using the client authentication hook (clientauth) that runs after the authentication process, please refer here below:

[+] https://aws.amazon.com/blogs/database/enhance-postgresql-database-security-using-hooks-with-trusted-language-extensions/

valid_until is not working for pg_tle

Add your answer

Relevant content