Hi Daniele,
I have created a github pull request (
#576) for the mentioned subject line.
Use case (in pgAdmin 4):
When we specify a password for a existing user/role, we need to execute a query something like:
ALTER USER ashesh PASSWORD 'XXX' ...;
Similarly, while creating user/role, the generated SQL is something like this:
CREATE USER ashesh PASSWORD 'XXX' ...';
For security reasons, the value in XXX in about queries can not be plain text. Otherwise - it will be available in the log files.
For PostgreSQL 9.6, and earlier, we always used md5 for encryption, as it always saves the password in md5 in PostgreSQL catalog.
From PostgreSQL 10+, they have introduce the password_encryption GUC, which supports - md5, scram-sha-256, or plain.
Hence - when user has set the password_encryption in their postgresql.conf, encrypted password must be provided in that format. To achieve that, libpq have introduced a function 'PQencryptPasswordConn', which will allow the client to prepare the password in the current password encryption method.
Implementation:
'PQencryptPasswordConn' function requires connection as first parameter to determine the current encryption algorithm, and prepare the encrypted password based on that algorithm.
Because - we need the connection object, I thought to add a method 'encrypt_password' in the 'connection' class itself.
Signature for encrypt_password is:
encrypt_password(password, user, [algorithm])
Where,
password - plain text password, which needs to be encrypted
user - name of the user, for which the password is being encrypted
algorithm (optional) - Algorithm to be used, if not specified the password_encryption algorithm wil be fetched from the database server (done by the 'PQencryptPasswordConn' internally).
For PostgreSQL <= 9.6, it will always use the 'PQencryptPassword' function of libpq, and ignores the algorithm.
For PostgreSQL 10+, it depends on the libpq version used at compile time. If the libpq version >=10, it will use the 'PQencryptPasswordConn' function, otherwise throws an error.
Please review the pull request, and share your view.
Thanks for your support.
-- Thanks,
Ashesh Vashi