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.

Answer: 
You may provide the password already hashed with `md5`, as said in the doc ([CREATE ROLE](http://www.postgresql.org/docs/9.2/static/sql-createrole.html)):

> 
> 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.
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](https://blog.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram). Maybe, [this](https://www.cybertec-postgresql.com/en/from-md5-to-scram-sha-256-in-postgresql/) 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.