CODEWITHSUNDEEP
postgresqlpasswordsshaplaintext
Martin
Martin

Reputation: 2028

Creating user with encrypted password in PostgreSQL

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.

Upvotes: 73

Views: 141302

Answers (5)

womble
womble

Reputation: 12407

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

Upvotes: 0

Emanuele Pepe
Emanuele Pepe

Reputation: 151

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.

Upvotes: 15

Kamil D
Kamil D

Reputation: 93

Much easier way to to this is:

CREATE USER u0 PASSWORD 'foobar'; 

select * from pg_catalog.pg_shadow;

Gives passwd: md5ac4bbe016b808c3c0b816981f240dcae

Upvotes: 5

bma
bma

Reputation: 9766

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

Upvotes: 8

Daniel Vérité
Daniel Vérité

Reputation: 61526

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.

Upvotes: 102

Related Questions