We are trying to implement strong password policies to our aws hosted postgresql RDS instances using TLE (https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_trusted_language_extension-hooks-reference.html)
I wanted to implement valid_until parameter so how whenever password is changed by user using PGAdmin its account expiry is automatically extended to current_date + 90 days.
Below is the password_check function looks like. Whilst password check works absolutely fine, it fails to execute ALTER USER/ROLE VALID UNTIL statement. I also tried to put a default valid_until value however when function is called via PGAdmin, its using NULL instead of default.
When password is changed using PGAdmin, account expiry value is NULL. Normal user has no ability to mention expiry date using PGAdmin hence default date (in future) is tried.
CREATE OR REPLACE FUNCTION password_check.passcheck_hook(
username text,
password text,
password_type pgtle.password_types,
valid_until TIMESTAMPTZ DEFAULT CURRENT_DATE + INTERVAL '90 days',
valid_null boolean DEFAULT false)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE
Appreciate if there any leads.
Tried putting default date in the function => no error but account expiry didnt updated
Tried adding; EXECUTE FORMAT('ALTER ROLE '|| quote_ident(username) || ' VALID UNTIL ' || quote_literal(TO_CHAR((CURRENT_DATE + interval '90 days'),'Month DD, YYYY')));
EXECUTE format('ALTER USER %I VALID UNTIL '|| quote_literal(TO_CHAR((CURRENT_DATE + interval '90 days'),'Month DD, YYYY')), username);
EXECUTE format('ALTER ROLE %I VALID UNTIL %L', username, valid_until);
2,3 & 4 errored out with message "Attempted to update invisible tuple"
A quick and dirty example:
CREATE OR REPLACE FUNCTION public.pwd_valid_until(usr character varying, dt_str character varying)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
out_str text;
BEGIN
out_str = format('alter role %I valid until %L', usr, dt_str::date + '90 days'::interval);
return out_str;
END;
$function$
;
Test run;
select pwd_valid_until('aklaver', current_date::text);
alter role aklaver valid until '2025-02-23 00:00:00'
This code does not actually change the role that would need EXECUTE format()
, it just shows how to build the query.