PostgreSQL expiration date management functions

Table of Contents

1. TOC
   1. Description
   2. Instructions
   3. Helper script
   4. RDS considerations
   5. Security considerations

Description

This project tries to find a way to allow users the management of the VALID UNTIL expiration clause by themself. Everyghin without granting super permissions and having a histoc of changes on a pseudo-audit table.

You can easly combine this functions with the passwordcheck extra extension, the regex inside dba.change_valid_until match the default requirements in the extension for special characters and you can change the variable _min_password_length to match your requirements (in the case you changed it, of course).

⚠️ WARNING
Amazon RDS has some notes at the end...
⚠️ WARNING

Instructions

First deploy

Modify passchanger.sql according your needings:

• Change _min_password_length on change_my_password function
• Change _password_lifetime on change_valid_until function

Deploy passchanger.sql on the desired cluster/database.

It will:

• create a dba schema
• create a dba role
• create the pwdhistory table for audit purpouses
• Grant the minimum permissions for this new role so the whole thing works
• Create the 2 needed functions and grant permissions on them to dba

Updates

Just execute the CREATE OR REPLACE FUNCTION part of the passchanger.sql file.

⚠️ WARNING
Amazon RDS has some notes at the end...
⚠️ WARNING

Allowing users to use that functions

Take the file grants_to_grant.sql and modify the username dodger so it match the username that should have the permissions. Execute the grants on the cluster/database you have deployed passchanger.sql

Changing password & extending expiration date

The user should just execute:

select dba.change_my_password('YOUR_NEW_GENERATED_PASSWORD_NOT_THIS_ONE') ;

Helper script

I've generated a helper script to make the process easier for users:

[email protected] $ bash password_creator.sh 
-- CHECK: password check
-- <Wl}TxqRPBQaV_N<rU#A 
-- /CHECK: password check

-- ##############################################
select dba.change_my_password('<Wl}TxqRPBQaV_N<rU#A') ;
-- ##############################################

RDS considerations

As Amazon has modified Postgresql so you don't have access as a real superuser and the dangerous function change_valid_until should run as the owner of the database (the user created when you deploy the database through AWS)

There's a passchanger_rds.sql file which should be used instead of the normal one.

For updates you should change the owner of the change_valid_until to the database owner:

ALTER FUNCTION dba.change_valid_until(text) OWNER TO _DATABASEOWNER;

Modify _DATABASEOWNER according your admin username...

Security considerations

• Non-RDS change_valid_until function does not uses ALTER USER to modify VALID UNTIL, it makes an update pg_catalog.pg_authid set rolvaliduntil instead, so the dba user has only grant over that table/column instead of granting additional permissions to him.
• RDS change_valid_until should run as the database owner, is the only way to make this work as you can't access pg_catalog.pg_authid on rds, it uses ALTER USER ... VALID UNTIL instead.