Re: scram and \password

On Sun, Mar 12, 2017 at 9:10 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:
> On 03/11/2017 03:15 PM, Michael Paquier wrote:
>> Yeah but it can be called as well while the application is calling
>> PQgetResult() and still looping until it gets a NULL result. Not sure
>> if this is a use-case to worry about, but sending a query to the
>> server in PQencryptPassword() could as well break some applications.
>
> I was suggesting sending the query outside of PQencryptPassword() in
> order to determine what method should be passed as a new argument to
> PQencryptPassword().

Why not. Our thoughts don't overlap, I thought about having
PQencryptPassword() call itself the server for the value of
password_encryption, and force the type depending on what the server
answers.

>> PQencryptPassword() is used for CREATE/ALTER ROLE commands, so
>> actually wouldn't it make sense to just switch PQencryptPassword to
>> handle SCRAM if at some point we decide to switch the default from md5
>> to scram? So many questions.
>
> As long as we support more than one method it would seem to me we need a
> way to determine which one we want to use and not only default it, don't
> we? Apologies if this has already been discussed -- I was not able to
> follow the lengthy threads on SCRAM in any detail.

Definitely, the most simple solution would be just to extend
PQencryptPassword() with a method value, to allow a user to do what he
wants...

>>> I guess a related problem might be, do we have a SQL visible way to
>>> determine what method is used by the current password for a given role?
>>
>> Nope. We are simply looking at a function doing a lookup at pg_authid
>> and then use get_password_type() to check which type of verifier is
>> used... Or have the type of verifier as a new column of pg_authid,
>> information that could be made visible to any users with column
>> privileges.
>
> What happens if the user does not have privs for pg_authid? E.g. if I
> want to change my own password what happens if the default is one
> method, and my password uses the other -- now I cannot change my own
> password using \password?

You can now. However it would be a problem for a user having a SCRAM
verifier using an application that changes the password with
PQencryptPassword() as it would change it back to MD5 on an update.
Having a RLS on pg_authid to allow a user to look at its own password
type is an idea. With multiple verifier types per role such class of
bugs can be also completely discarded.
--
Michael