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...
2 Answers
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.
Comments
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'; 