CODEWITHSUNDEEP
postgresql
Jarek
Jarek

Reputation: 351

Set postgres password with hash

I need to clone role from one postgresql database to another. Is it possible to somehow extract role password hash and set it in another database ? I'd like to avoid any clear password manipulation...

Upvotes: 2

Views: 2629

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51511

you can select password from pg_shadow and use it as per docs, as Eavn told. Or you can use pg_dumpall -g which will basically prepare statements to run with md5 passwords eg. at my machine:

CREATE ROLE r;
ALTER ROLE r WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5514f1b439f404f86f77090fa9edc96ce';

Upvotes: 1

Evan Carroll
Evan Carroll

Reputation: 1

If PostgreSQL thinks you're setting the password with an MD5 hash, it stores it directly. From the docs

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.

Upvotes: 3

Related Questions