Set postgres password with hash

Question

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...

Evan Carroll · Accepted Answer · 2017-04-21 17:49:32Z

3

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.

answered Apr 21, 2017 at 17:49

Evan Carroll

1

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2017-04-21 18:10:15Z

1

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';

answered Apr 21, 2017 at 18:10

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Set postgres password with hash

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related