Creating user with encrypted password in PostgreSQL

Question

You may provide the password already hashed with md5, as said in the doc (CREATE ROLE):

ENCRYPTED UNENCRYPTED These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.

The information that's missing here is that the MD5-encrypted string should be the password concatened with the username, plus md5 at the beginning.

So for example to create u0 with the password foobar, knowing that md5('foobaru0') is ac4bbe016b808c3c0b816981f240dcae:

CREATE USER u0 PASSWORD 'md5ac4bbe016b808c3c0b816981f240dcae';

and then u0 will be able to log in by typing foobar as the password.

I don't think that there's currently a way to use SHA-256 instead of md5 for PostgreSQL passwords.

I'm not aware of a way to override the default md5 encryption of passwords, but if you have a ROLE (aka "USER") that has an already md5-encrypted password it appears that you can supply that. Verify this using pg_dumpall -g (to see the globals from the cluster) Eg.

psql postgres
create role foo with encrypted password foobar;
\q

-- View the role from pg_dumpall -g
pg_dumpall -g | grep foo
CREATE ROLE foo;
ALTER ROLE foo WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5c98cbfeb6a347a47eb8e96cfb4c4b890';

Or get it from:
select * from pg_catalog.pg_shadow;

-- create the role again with the already-encrypted password
psql postgres
drop role foo;
CREATE ROLE foo;
ALTER ROLE foo WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5c98cbfeb6a347a47eb8e96cfb4c4b890';
\q

-- view the ROLE with the same password
pg_dumpall -g | grep foo
CREATE ROLE foo;
ALTER ROLE foo WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5c98cbfeb6a347a47eb8e96cfb4c4b890';

Docs for CREATE ROLE

Much easier way to to this is:

CREATE USER u0 PASSWORD 'foobar';

select * from pg_catalog.pg_shadow;

Gives passwd: md5ac4bbe016b808c3c0b816981f240dcae

At least from version 10.10, it's possible to use SCRAM-SHA-256 as well.

CREATE USER user_name
WITH PASSWORD 'SCRAM-SHA-256$4096:UunGvPETiX/JNGBvjOgW9A==$CPGNh7/MRfs0ispH9/HSJajOI8Uhp+UCRo/b/ToXIEY=:L6NzxQ3XUeWEeRa+oiuajC9Vgl7wk6ZpHAHl+pv4m00=';
GRANT CONNECT ON DATABASE database_name TO user_name;

(It's important not to forget to GRANT privileges to the new user)

If you want SCRAM to be used by default, you can set the password_cryptography to SCRAM-SHA-256:

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

I know it's possible to set the passwords also avoiding SQL statements, this link to the documentation should help. Maybe, this is a bit less verbose.

Anyway, md5 should be avoided when possible, SCRAM is a more robust way to store passwords.

In case you cannot find a way to create the SCRAM string accepted by Postgres, you can let it crate one for you with the following code.

1. Remember to set the password_encryption to SCRAM

   ALTER SYSTEM SET password_encryption = 'scram-sha-256';
   SELECT pg_reload_conf();
   

   This cannot be run in a transaction block. If for instance, you're using migration files, you probably have to create two different files just ofr those two commands.

2. Create a user with the password you need to encode.

   CREATE USER tmp_user_to_create_a_password
   WITH PASSWORD 'your_password';
   

3. Read the password with SCRAM encryption.

   SELECT rolpassword
   FROM pg_catalog.pg_authid
   WHERE rolname='tmp_user_to_create_a_password';
   

4. Drop the user

   DROP USER IF EXISTS tmp_user_to_create_a_password;
   

5. Now you can create your user without using plain text.

   CREATE USER user_name
   WITH PASSWORD 'SCRAM-SHA-256$4096:3Lctb6GmH15cSO4bjcDsXg==$BSuI1c10J+NZ/Wmx4hwP4TvpdKEO9rl2hekZ8/DVuyA=:j8G9NJ30Xbz3Za2mjXF/j6O3DJbWyqvX886haFe4aCs=';
   GRANT CONNECT ON DATABASE database_name TO user_name;
   

   You can now use 'user_name' and 'your_password' to log-in.