Re: scram and \password

On Sun, Mar 12, 2017 at 8:04 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:
> On 03/11/2017 02:21 PM, Michael Paquier wrote:
>> On Sun, Mar 12, 2017 at 5:35 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:
>>> So if the password is not already set, \password uses
>>> password_encryption to determine which format to use, and if the
>>> password is already set, then the current method is assumed.
>>
>> Yeah, the problem here being that this routine does not need a live
>> connection to work, and we surely don't want to make that mandatory,
>> that's why I am suggesting something like the above. Another approach
>> would be to switch to SCRAM once password_encryption does this switch
>> as well... There is no perfect scenario here.
>
> You might extend PQencryptPassword() to take a method. Or create a new
> function that does? Certainly psql has a connection available to run the
> ALTER ROLE command that it crafts.

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.

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.

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