Malte Krupa

Rotate PostgreSQL Credentials - 2023-10-04

Most guides on how to rotate database credentials create a parent role and inherit permissions to two new roles.

To rotate the credentials they:

  1. enable the new role with a new password
  2. switch all applications to the new credentials
  3. disable the old role

Enable/Disable means to allow or prevent the role from logging in.

I usually do the same … but with a twist. Instead of having e.g. role_a and role_b I’ll create a new role whenever the credentials need to be rotated. This new role will look something like this: role_2023_10_04_09_00_00.

The timestamp makes it very clear how long credentials have been in use and it’s a good reminder to rotate them very soon.

Helper function

For this I use a helper function called create_rotating_role. This function takes three arguments:

CREATE OR REPLACE FUNCTION public.create_rotating_role(
    username character varying,
    passwd character varying,
    parent character varying
)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
    username_complete character varying;
BEGIN
    username_complete = username || '_' || to_char(current_timestamp, 'YYYY_MM_DD_HH_MM_SS');
    execute format($$CREATE ROLE %I WITH LOGIN PASSWORD '%s' IN ROLE %s$$, username_complete, passwd, parent);
    execute format($$ALTER ROLE %I SET ROLE %s$$, username_complete, parent);
    return username_complete;
END;
$function$;

The function does two things. It creates the new user and changes the default role for said user to the role of the parent. Every role will therefore act as the parent role. This is important because it allows all the roles to alter and delete tables which have been created by previously created roles which might not exist anymore.

It’s probably best to add the function in the postgres database. Otherwise you’ll need it in every database where you plan to use it (I think).

Workflow

Initial setup

Let’s assume we start from zero.

We’ll create a new database and a new role which we grant the required permissions:

CREATE DATABASE app_db;
CREATE ROLE app_role_parent WITH NOLOGIN;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_role_parent;

This new role is the parent and all future changes to permissions will be done to it.

It’s redundant but more explicit: We specified WITH NOLOGIN on the role, which is the default for roles create via CREATE ROLE (compared to CREATE USER). This role will not be used to login. That’s what we create the other roles for.

Repeating task

With the new function in place we can create a new role by running:

SELECT create_rotating_role('app_role', <password>, 'app_role_parent');

     create_rotating_role
------------------------------
 app_role_2023_10_04_12_34_56
(1 row)

The output of the function will contain the name of the newly created role.

Now you need to change the credentials in whatever app you’re using and once everything is migrated to the new role you can delete the old one.

DROP ROLE app_role_2023_09_03_01_23_45;

That’s it.

What I learned along the way

Imagine working in IT for way too long and still learning the basics. :)

Sources

Other people posted about this YEARS ago (in order of how resourceful I think their posts are):

Privacy Policy | Imprint