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

From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: CREATE/ALTER ROLE PASSWORD ('value' USING 'method')
Date: 2017-03-08 02:07:20
Message-ID: CAB7nPqSP+MHqg=dKoNOZu75j2mGAEW622GYz45Mv2V_XOao-9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Add-clause-PASSWORD-val-USING-protocol-to-CREATE-ALT.patch application/octet-stream 12.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2017-03-08 02:07:47 Re: Write Ahead Logging for Hash Indexes
Previous Message Robert Haas 2017-03-08 02:06:04 Re: foreign partition DDL regression tests