Creating user with encrypted password in PostgreSQL

Question

Is it possible to create a user in PostgreSQL without providing the plain text password (ideally, I would like to be able to create a user providing only its password crypted with sha-256) ?

What I would like to do is to create a user with something like that :

CREATE USER "martin" WITH PASSWORD '$6$kH3l2bj8iT$KKrTAKDF4OoE7w.oy(...)BPwcTBN/V42hqE.';

Is there some way to do that ?

Thank you for your help.

Isaiasisak

Answer 1

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.

Aubigny

The regular mixup of encryption and hashing makes me sad :( MD5 is a hashing algorithm, which means there is no key to "decrypt" the password but brute forcing or a collision attack... – Obrian

How did you encrypt foobar to arrive at ac4bbe016b808c3c0b816981f240dcae? I am trying echo "foobaru0" | md5sum | cut -d' ' -f1 and I get a different one. Then trying it with CREATE USER u0 PASSWORD 'md5c49aa8b010dbdeec293c5a9504fb5531'; creates the user but when I try to login with password foobar it doesn't accept it. Says psql: FATAL: password authentication failed for user "u0" – Archive

@Subbu: use echo -n to suppress the newline character, otherwise it gets hashed too. I used select md5(...) in SQL. – Acatalectic

Thanks. That worked. I also removed quotes. They were not necessary. – Archive

Third time's the charm. I got this command-line to work for me. It's a handy way of getting the correct hash: U=u0; P=foobar; echo -n md5; echo -n $P$U | md5sum | cut -d' ' -f1 – Wolfgang

@TobiasKienzler It's MD5, which was broken a long time ago, so there's at least a chance that one of the passwords could be "decrypted" back into the original text. Besides, the way they're using MD5, the hashes are the equivalent of passwords. So the strength of the hash algorithm is irrelevant in their scheme anyway. – Madeline

The above answer is still valid. We wanted to create a new dataset in Amazon QuickSight. After receiving the GENERIC_SQL_EXCEPTION The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client’s IP address or subnet, and that it is using an authentication scheme supported by the driver. error, we found out that PostgreSQL 14 changed the default password encryption from MD5 to SCRAM, therefore the default Amazon Aurora Serverless v2 hash mechanism won't be good for QS. So create your QS (read-only) user with MD5 hashed passwords :) – Breebreech

Answer 2

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.

Secondclass

Answer 3

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

Centerboard

You can set password_encryption to scram-sha-256 in postgresql.conf to get scram-sha-256 password encryption. Note, however, that older clients may not support SCRAM authentication (see postgresql.org/docs/11/…) – Vasodilator

Answer 4

Much easier way to to this is:

CREATE USER u0 PASSWORD 'foobar';

select * from pg_catalog.pg_shadow;

Gives passwd: md5ac4bbe016b808c3c0b816981f240dcae

Michaelmas

This misses the point of the question which is that the person creating the password doesn't have to know the password in order to create the user. – Gona

Answer 5

As I just had to work this out for something I'm building, I thought I'd drop this snippet of code in for the benefit of future generations. It reads the password from stdin, and outputs a SCRAM-SHA-256 password hash suitable for use in CREATE USER ... ENCRYPTED PASSSWORD '...'. Hope it comes in handy for someone.

require "openssl"
require "securerandom"                                                                                                                                                                                                                         
                                                                                                                                                                                                                                               
# Yes, these are the literal values specified in RFC5802, s3                                                                                                                                                                                   
CLIENT_KEY = "Client Key"                                                                                                                                                                                                                      
SERVER_KEY = "Server Key"                                                                                                                                                                                                                      
                                                                                                                                                                                                                                               
SALT = SecureRandom.bytes(16)
# This is much higher than PostgreSQL implements by default, which is 4096
# (as of https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/include/common/scram-common.h;hb=HEAD, anyway)
# but THAT'S A GOOD THING
ITERATIONS = 65_536

salted_password = OpenSSL::PKCS5.pbkdf2_hmac($stdin.read, SALT, ITERATIONS, 32, OpenSSL::Digest::SHA256.new)
client_key = OpenSSL::HMAC.digest(OpenSSL::Digest::SHA256.new, salted_password, CLIENT_KEY)                                                                                                                                                    
stored_key = OpenSSL::Digest::SHA256.new.digest(client_key)                                                                                                                                                                                    
server_key = OpenSSL::HMAC.digest(OpenSSL::Digest::SHA256.new, salted_password, SERVER_KEY)                                                                                                                                                    
                                                                                                                                                                                                                                               
def b64(s)                                                                                                                                                                                                                                     
  [s].pack("m0")                                                                                                                                                                                                                               
end                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                               
puts "SCRAM-SHA-256$#{ITERATIONS}:#{b64(SALT)}$#{b64(stored_key)}:#{b64(server_key)}"                                                                                                                                                          

Nyssa