Postgres create user with encrypted password

Question

I have a flyway script which creates a User in postgres with an encrypted password. I want flyway to create the user account, and then use the account with a springboot java application. I want to use an encrypted password because the script will be stored in a git repository, and I dont want to store it as plain-text. The process therefore involves encrypting the desired password so it can be added to the flyway sql script, and then adding the password as a secret which can be read as a property by the java application:


CREATE USER special_user ENCRYPTED PASSWORD 'ab2843414119e861a2202b6e77bb4c48b4d32172a23a0268e31b972985754e3e';

I believe the postgres instance is using the default (sha256sum) for encryption:


show password_encryption;

> scram-sha-256

The password used in the sql was encrypted as follows:

➜  workspace echo Password! > input.txt
➜  workspace sha256sum input.txt 
ab2843414119e861a2202b6e77bb4c48b4d32172a23a0268e31b972985754e3e  input.txt

However when I try to use the username+password combination to authenticate I get errors. This appears to be due to the password being incorrect. If I set the password without specifying encryption (as plain text) the username-password works ok.

What am I doing wrong? I have seen other posts where the encrypted password is prefixed with SCRAM-SHA-256$4096: but I have tried this and it did not work either (I cant find any mention of the format in the documentation)

thanks

Read [Password auth](https://www.postgresql.org/docs/current/auth-password.html). I don't believe there is a way to use the `SCRAM-SHA-256$4096: ...` form directly. You will need to provide the plain text to the client. — Adrian Klaver, Jun 26 '23 at 14:36
As Adrian says, there should not be a way to use sha-256 hash directly. Only way would be changing the source code to bypass the authendication :). Just out of curiosity, why do you want to do that? If you want to keep it in a store more securely(!?), then use your own salt and hashing algorithm to compare. Yet, does not make sense for me it is the reason. — Umut TEKİN, Jun 26 '23 at 15:04
To verify how is being stored (which encryption algorithm is used) the password for that user execute `select rolname, rolpassword from pg_catalog.pg_authid where rolname ='special_user';` — Pepe N O, Jun 26 '23 at 15:07
There's an answer to a similar question [here](https://stackoverflow.com/questions/68400120/how-to-generate-scram-sha-256-to-create-postgres-13-user?rq=1) and the docs for constructing the encrypted string for SHA-256 [here](https://www.postgresql.org/docs/15/catalog-pg-authid.html) — Pepe N O, Jun 26 '23 at 20:47

score 1 · Answer 1 · answered Jun 26 '23 at 15:20

scram-sha-256 is not the same thing as sha256sum.

That password in the CREATE statement is in plain text. The 'ENCRYPTED' keyword in ENCRYPTED PASSWORD doesn't actually do anything, it is just for compatibility. Rather the system automatically decides if it is encrypted or not just by inspecting the format, and that string is not in the correct format for it to be already encrypted.

It is not trivial to construct a proper scram-sha-256 verifier. Obviously there is code in PostgreSQL itself that does it, implemented in 'pg_fe_scram_build_secret' exposed in the front-end libpq as 'PQencryptPasswordConn'.

If it is just a one-time thing, you can set the password in psql, then query to pg_shadow to obtain the verifier. Then you can use that verifier to stuff it into a new system in order to set the password for that new system.

score 0 · Answer 2 · answered Jun 27 '23 at 09:37

The answer to this question was contained in this post and this post

The easiest way I found to create the encrypted password is to create a user with the desired password as plain text:

create user special_user with password 'Password!';

then copy the encrypted value from the database:

SELECT rolname, rolpassword
FROM pg_catalog.pg_authid
where rolname = 'special_user';

...

special_user, SCRAM-SHA-256$4096:cxoSfsrgfD44vKa0UxC7zw==$X9Ff7zIz6RHAUK9RuBrCrFq0zgb/AFKWZHXfnytnIgw=:82LlxgQl+kv++jWYBzhHf+420gJtObUwQkw9FJYuFD8=

the value returned can then be added to the flyway sql script. These steps can be carried out on an instance of postgres running locally, and the encrypted value then used on a different database. The doc does suggest the ServerKey is contained in the encrypted string, but this doesnt appear to cause any problems.

Postgres create user with encrypted password

2 Answers2