Can I do it with PostgreSQL? – 19 – Create user … identified by values

Puh, that last post in this series is already half a year old. Time is moving too fast 🙁 Today, while being at a customer again, this question came up: Can I do something comparable in PostgreSQL to what I can do in Oracle, which is: Create a user and provide the hashed password so that the password is the same on the source and the target (which implies not knowing the password at all)? In Oracle you can find the hashed passwords in user$ where can I find that in PostgreSQL? Lets go.

When we look at the “create user” command there is no option which seems to do that:

postgres=# h create user
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Maybe we can just pass the hashed password? Lets try be creating a new user:

postgres=# create user u with login password 'u';
CREATE ROLE

The hashed passwords in PostgreSQL are stored in pg_shadow:

postgres=# select passwd from pg_shadow where usename = 'u';
               passwd                
-------------------------------------
 md56277e2a7446059985dc9bcf0a4ac1a8f
(1 row)

Lets use that hash and create a new user:

postgres=# create user w login encrypted password 'md56277e2a7446059985dc9bcf0a4ac1a8f';
CREATE ROLE

Can we login as w using “u” as a password?

postgres@pgbox:/home/postgres/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W
Password for user u: 
psql: FATAL:  no pg_hba.conf entry for host "192.168.22.99", user "w", database "postgres", SSL off

Ok, makes sense. After fixing that:

postgres@pgbox:/home/postgres/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W
Password for user w: 
psql: FATAL:  password authentication failed for user "w"

So obviously this is not the way to do it. Do we have the same hashes in pg_shadow?

postgres=# select usename,passwd from pg_shadow where usename in ('w','u');
 usename |               passwd                
---------+-------------------------------------
 u       | md56277e2a7446059985dc9bcf0a4ac1a8f
 w       | md56277e2a7446059985dc9bcf0a4ac1a8f
(2 rows)

Hm, exactly the same. Why can’t we login then? The answer is in the documentation:”Because MD5-encrypted passwords use the role name as cryptographic salt, …”. We can verify that be re-creating the “w” user using the same password as that of user “u”:

postgres=# drop user w;
DROP ROLE
postgres=# create user w login password 'u';
CREATE ROLE
postgres=# select usename,passwd from pg_shadow where usename in ('w','u');
 usename |               passwd                
---------+-------------------------------------
 u       | md56277e2a7446059985dc9bcf0a4ac1a8f
 w       | md53eae63594a41739e87141e8333d15f73
(2 rows)

The hashed values are not the same anymore. What of course is working is to re-create the user with that hash:

postgres=# drop role w;
DROP ROLE
postgres=# create user w login password 'md53eae63594a41739e87141e8333d15f73';
CREATE ROLE

Now we should be able to login with the password ‘u’:

postgres@pgbox:/home/postgres/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W
Password for user w: 
psql (10.0 dbi services build)
Type "help" for help.

postgres=> 

Fine. Another way of getting the password hashes is to use pg_dumpall using the “–globals-only” switch:

postgres@pgbox:/home/postgres/ [PG10] pg_dumpall --globals-only > a.sql
postgres@pgbox:/home/postgres/ [PG10] grep -w w a.sql 
CREATE ROLE w;
ALTER ROLE w WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md53eae63594a41739e87141e8333d15f73';

Hope that helps.