PostgreSQL Question posted in
The official documentation can be found here.

postgresql TLE password_check function and pgadmin

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.

  1. Tried putting default date in the function => no error but account expiry didnt updated

  2. Tried adding; EXECUTE FORMAT(‘ALTER ROLE ‘|| quote_ident(username) || ‘ VALID UNTIL ‘ || quote_literal(TO_CHAR((CURRENT_DATE + interval ’90 days’),’Month DD, YYYY’)));

  3. EXECUTE format(‘ALTER USER %I VALID UNTIL ‘|| quote_literal(TO_CHAR((CURRENT_DATE + interval ’90 days’),’Month DD, YYYY’)), username);

  4. EXECUTE format(‘ALTER ROLE %I VALID UNTIL %L’, username, valid_until);

2,3 & 4 errored out with message "Attempted to update invisible tuple"

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    While thinking about possible solution, it was realized that PGADMIN is sending NULL for valid_until parameter and hence function is failing to execute. This is because the account for which I was trying to reset password has no expiry set. I then added an expiry date to the account and then tried to change password and function worked as expected and extended expiry date to current_date + 90 days. Thank you @adrian for your valuable input.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search