CREATE/ALTER ROLE PASSWORD ('value' USING 'method')

Hi all,

As discussed on the thread dedicated to SCRAM
(https://www.postgresql.org/message-id/243d8c11-6149-a4bb-0909-136992f74b23@iki.fi),
here is a separate thread dedicated to the following extension for
CREATE/ALTER ROLE: PASSWORD ('value' USING 'method').

Now that password_encryption has been extended with a new value
'scram', it is a bit bothersome for the user to create roles using
different methods because password_encryption would need to be set
first:
=# SET password_encryption = 'scram';
SET
=# CREATE ROLE foorole PASSWORD 'foopass';
CREATE ROLE
=# SET password_encryption = 'md5';
SET
=# CREATE ROLE foorole2 PASSWORD 'foopass';
CREATE ROLE

What I am proposing with the patch attached is to add a new clause
(grammar is an idea from Robert), to do the same in a single command:
=# CREATE ROLE foorole3 PASSWORD ('foo' USING 'scram');
CREATE ROLE
=# CREATE ROLE foorole4 PASSWORD ('foo' USING 'md5');
CREATE ROLE
This way there is no need to enforce password_encryption prior to
define a new password. Note that like the existing clauses, this is
permissive. In short, if the value is already MD5-encrypted or
SCRAM-encrypted, then the type of the parsed value is enforced
compared to what is defined as method for this USING clause, which is
useful for bumping data.

As this needs clarification before Postgres 10, I am adding a bullet
in the TODO items. This would prove to be useful if more protocols are
added in the future.

Thoughts?
--
Michael