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

5 minute read

I want to create a password policy in Amazon Relational Database Service (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 Trusted Language Extensions (TLE) extensions to extend core capabilities. To customize how PostgreSQL handles passwords when you create or change passwords for users or roles, use the passcheck hook.

Note: 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

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Create a customer parameter group and set up the TLE extension

Complete the following steps:

Create a custom parameter group, and then associate it to your Amazon RDS for PostgreSQL instance.
Note: Parameter group changes will cause downtime. Use a maintenance window to avoid immediate downtime.
Set up the TLE extension.

Log in to your instance, run the following query, 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=>

Run the following query to create the TLE extension:
```
CREATE EXTENSION pg_tle;
```
Note: To set up and configure the pg_tle extension, your database user role must have rds_superuser role permissions.
Grant the pgtle_admin role to your Amazon RDS for PostgreSQL instance primary user. If you used a default user, then that is the primary postgres user:
```
GRANT pgtle_admin TO example-user;
```
Note: Replace example-user with your Amazon RDS for PostgreSQL instance primary 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: You can modify a PostgreSQL hook's function 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.

Complete the following steps:

Run the following pgtle.install_extension SQL command. Modify the SQL code to your specific needs:

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_$);

Note: In the previous command, replace example-password-check-rules with the name of your password check rules.

Run the following query to create the extension:

postgres=> CREATE EXTENSION "example-password-check-rules";

Note: In the previous command, replace example-password-check-rules with the name of your password check rules.

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

To test your password check rules, use the following examples.
Example:

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)"

Note: The preceding example indicates an error because you can't use passwords from the common password dictionary.
Example:

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)"

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

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: The preceding example indicates an error because the password must contain uppercase letters, lowercase letters, numbers, and special characters.

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.