Question 195055 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres create user with encrypted password

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search