Обсуждение: [psycopg] Feature Request: [PostgreSQL 10] Support for preparing the encrypted password

I have reviewed and annotated some changes to the implementation proposed.

There are a few implementation changes pretty unequivocal to fix bad
behaviour in corner cases and to adopt the same coding style of the
rest of the project.

One more arguable request is that I don't like the function as a
connection method. The function is a wrapper to both
`PQencryptPasswordConn` and `PQencryptPassword`, so the connection is
actually an optional parameter. My suggestion is that the method
should be a function in `extensions` taking an optional connection or
cursor as parameter. If the parameter is omitted only the 'md5'
algorithm is accepted (which is a sensible default for the function
and works for any server and client version).

Another point is: when to release this function? Should we release a
psycopg 2.8 to expose that? I guess releasing in a 2.7.X release is
impolite...

Thoughts welcome.

-- Daniele



On Thu, Aug 3, 2017 at 8:00 AM, Ashesh Vashi <asheshvashi@gmail.com> wrote:
> 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
>

On 8/3/17 7:00 AM, Daniele Varrazzo wrote:
> One more arguable request is that I don't like the function as a
> connection method. The function is a wrapper to both
> `PQencryptPasswordConn` and `PQencryptPassword`, so the connection is
> actually an optional parameter.

I haven't looked at the code, but psql has supported \password back to
at least 9.5. AFAIK that's accomplished at the fe/be layer, so
presumably libpq has had password change support for quite some time.
--
Jim Nasby, Chief Data Architect, Austin TX
OpenSCG                 http://OpenSCG.com