kuda.ai

postgres: create user with random password

Created on February 20, 2026

postgres

You will see two ways:

1. generate password in the shell and create with psql
2. create a postgres function that generates a password

generate pw via shell

You generate a password in the shell and use it as a sql variable via psql.

PASSWORD=$(openssl rand -base64 18)
psql \
-v pass="$PASSWORD" \
-c "CREATE ROLE david LOGIN PASSWORD :'pass';"

There are many ways to generate your password, openssl is just one way.

Alternatives:

head -c 18 /dev/urandom | base64

python3 -c "import secrets; print(secrets.token_urlsafe(64))"

openssl rand -hex 10

generate with postgres function

-- first, enable pgcrypto:
CREATE EXTENSION IF NOT EXISTS pgcrypto;

DO $$
DECLARE
    pass TEXT := rtrim(encode(gen_random_bytes(15), 'base64'), '=');
BEGIN
    EXECUTE format('CREATE ROLE david LOGIN PASSWORD %L', pass);
    RAISE NOTICE 'Generated password: %', pass;
END $$;

or:

-- Create role with a random password
DO $$
DECLARE
  pwd text;
BEGIN
  pwd := encode(gen_random_bytes(24), 'base64');
  EXECUTE format('CREATE ROLE david WITH LOGIN PASSWORD %L', pwd);
  RAISE NOTICE 'Generated password for myuser: %', pwd;
END $$;