How do I create a password policy in Amazon RDS for PostgreSQL?

Short description

By default, Amazon RDS for PostgreSQL doesn't have functionality to enforce a password policy. However, you can use PostgreSQL hooks and TLE extensions to extend PostgreSQL's core capabilities. To customize how PostgreSQL handles passwords when you create or change passwords for users or roles, use the passcheck hook.

Note: Trusted Language Extensions (TLE) supports Amazon RDS for PostgreSQL versions 16.1 and newer, 15.2 and newer, 14.5 and newer, and 13.12 and newer. For more information, see Requirements for using Trusted Language Extensions for PostgreSQL.

Resolution

Before you begin, set up the TLE extension for your Amazon RDS for PostgreSQL instance. To set up the TLE extension, complete the following steps:

1. Update the shared_preload_libraries parameter to include pg_tle in your custom parameter group that's associated with your Amazon RDS for PostgreSQL instance.

2. Reboot your RDS instance for the update to the shared_preload_libraries parameter to take effect.

3. Log in to your instance, and then confirm that the shared_preload_libraries parameter is updated.

   postgres=> SHOW shared_preload_libraries;
   shared_preload_libraries
   rdsutils,pg_tle,pg_stat_statements
   (1 row)
   postgres=>

4. Create the TLE extension:
   Note: To set up and configure the pg_tle extension, your database user role must have rds_superuser role permissions.

   CREATE EXTENSION pg_tle;

5. Grant the pgtle_admin role to your Amazon RDS for PostgreSQL instance primary user. If you used a default user, then it's the postgres user.
   Note: Replace example-user with your Amazon RDS for PostgreSQL instance primary user.

   GRANT pgtle_admin TO example-user;

Set up the passcheck hook

A PostgreSQL passcheck hook checks passwords for SQL operations and doesn't allow users to set passwords listed in the password_check.bad_passwords table. The passcheck hook also checks password length and confirms that passwords contain uppercase and lowercase letters, numbers, and special characters.

Note: A PostgreSQL hook's function can be modified to your specific needs. You can add more passwords to the bad_passwords table, change the password length required, or modify the function to check the password complexity.

To set up the passcheck hook, complete the following steps:

1. Run the pgtle.install_extension SQL code. Modify the SQL code to your specific needs.
   Note: Replace example-password-check-rules with the name of your password check rules.

   SELECT pgtle.install_extension (
     'example-password-check-rules',
     '1.0',
     'Do not let users use the 10 most commonly used passwords',
   $_pgtle_$
     CREATE SCHEMA password_check;
     REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
     GRANT USAGE ON SCHEMA password_check TO PUBLIC;
     CREATE TABLE password_check.bad_passwords (plaintext) AS
     VALUES
       ('123456'),
       ('password'),
       ('12345678'),
       ('qwerty'),
       ('123456789'),
       ('12345'),
       ('1234'),
       ('111111'),
       ('1234567'),
       ('dragon');
     CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext);
     CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean)
     RETURNS void AS $$
       DECLARE
         invalid bool := false;
       BEGIN
       
         -- Check password length
         IF length(password) < 8 THEN
           RAISE EXCEPTION 'Password must be at least 8 characters long.';
         END IF;
         
         -- Check common passwords from password from bad_passwords table
         IF password_type = 'PASSWORD_TYPE_MD5' THEN
           SELECT EXISTS(
             SELECT 1
             FROM password_check.bad_passwords bp
             WHERE ('md5' || md5(bp.plaintext || username)) = password
           ) INTO invalid;
           IF invalid THEN
             RAISE EXCEPTION 'Cannot use passwords from the common password dictionary';
           END IF;
         ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN
           SELECT EXISTS(
             SELECT 1
             FROM password_check.bad_passwords bp
             WHERE bp.plaintext = password
           ) INTO invalid;
           IF invalid THEN
             RAISE EXCEPTION 'Cannot use passwords from the common password dictionary';
           END IF;
         END IF;
         
         -- Check password contains uppercase lowercase number and special character
         IF NOT (password ~ '[A-Z]' AND password ~ '[a-z]' AND password ~ '[0-9]' AND password ~ '[^a-zA-Z0-9]') THEN
           RAISE EXCEPTION 'Password must contain uppercase letters, lowercase letters, numbers, and special characters';
         END IF;
       END
     $$ LANGUAGE plpgsql SECURITY DEFINER;
     GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC;
     SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck');
   $_pgtle_$);

2. Create the extension:
   Note: Replace example-password-check-rules with the name of your password check rules.

   CREATE EXTENSION example-password-check-rules;

3. Modify your custom parameter group that's associated with your instance, and then turn on the pgtle.enable_password_check parameter.

4. Test your password check rules.
   Example:
   Note: The following example indicates an error because you can't use passwords from the common password dictionary.

   postgres=> CREATE ROLE t_role PASSWORD 'password';
   ERROR:  Cannot use passwords from the common password dictionary
   CONTEXT:  PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 25 at RAISE
   SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"

   Example:
   Note: The following example indicates an error because the password must be at least 8 characters long.

   postgres=> CREATE ROLE t_role PASSWORD 'pass';
   ERROR:  Password must be at least 8 characters long.
   CONTEXT:  PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 7 at RAISE
   SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"

   Example:
   Note: The following example indicates an error because the password must contain uppercase letters, lowercase letters, numbers, and special characters.

   postgres=> CREATE ROLE t_role PASSWORD 'passwordd';
   ERROR:  Password must contain uppercase letters, lowercase letters, numbers, and special characters
   CONTEXT:  PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 31 at RAISE
   SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"

Note: For versions of Amazon RDS for PostgreSQL that don't support TLE, use AWS Identity and Access Management (IAM) for Amazon RDS or Kerberos authentication. Also, to restrict password creation to a set of roles or a specific role, see Delegating and controlling user password management.

Related information

Working with Trusted Language Extensions for PostgreSQL

Hooks reference for Trusted Language Extensions for PostgreSQL.