scram and \password
Should the \password tool in psql inspect password_encryption and act on it
being 'scram'?
I didn't see this issue discussed, but the ability to search the archives
for backslashes is rather limited.
Cheers,
Jeff
On Sat, Mar 11, 2017 at 2:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
Should the \password tool in psql inspect password_encryption and act on it
being 'scram'?
Not sure if it is wise to change the default fot this release.
I didn't see this issue discussed, but the ability to search the archives
for backslashes is rather limited.
I'll save you some time: it has not been discussed. Nor has
PQencryptPassword been mentioned. Changing to SCRAM is not that
complicated, just call scram_build_verifier() and you are good to go.
Instead of changing the default, I think that we should take this
occasion to rename PQencryptPassword to something like
PQhashPassword(), and extend it with a method argument to support both
md5 and scram. PQencryptPassword could also be marked as deprecated,
or let as-is for some time. For \password, we could have another
meta-command but that sounds grotty, or just extend \password with a
--method argument. Switching the default could happen in another
release.
A patch among those lines would be a simple, do people feel that this
should be part of PG 10?
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 03/10/2017 02:43 PM, Michael Paquier wrote:
On Sat, Mar 11, 2017 at 2:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
Should the \password tool in psql inspect password_encryption and act on it
being 'scram'?Not sure if it is wise to change the default fot this release.
I didn't see this issue discussed, but the ability to search the archives
for backslashes is rather limited.I'll save you some time: it has not been discussed. Nor has
PQencryptPassword been mentioned. Changing to SCRAM is not that
complicated, just call scram_build_verifier() and you are good to go.Instead of changing the default, I think that we should take this
occasion to rename PQencryptPassword to something like
PQhashPassword(), and extend it with a method argument to support both
md5 and scram. PQencryptPassword could also be marked as deprecated,
or let as-is for some time. For \password, we could have another
meta-command but that sounds grotty, or just extend \password with a
--method argument. Switching the default could happen in another
release.A patch among those lines would be a simple, do people feel that this
should be part of PG 10?
Seems like it should work in an analogous way to CREATE/ALTER ROLE.
According to the docs:
8<----
ENCRYPTED
UNENCRYPTED
These key words control whether the password is stored encrypted in
the system catalogs. (If neither is specified, the default behavior is
determined by the configuration parameter password_encryption.) If the
presented password string is already in MD5-encrypted or SCRAM-encrypted
format, then it is stored encrypted as-is, regardless of whether
ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt
the specified encrypted password string). This allows reloading of
encrypted passwords during dump/restore.
8<----
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.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Sun, Mar 12, 2017 at 5:35 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/10/2017 02:43 PM, Michael Paquier wrote:
Instead of changing the default, I think that we should take this
occasion to rename PQencryptPassword to something like
PQhashPassword(), and extend it with a method argument to support both
md5 and scram. PQencryptPassword could also be marked as deprecated,
or let as-is for some time. For \password, we could have another
meta-command but that sounds grotty, or just extend \password with a
--method argument. Switching the default could happen in another
release.A patch among those lines would be a simple, do people feel that this
should be part of PG 10?Seems like it should work in an analogous way to CREATE/ALTER ROLE.
According to the docs:8<----
ENCRYPTED
UNENCRYPTEDThese key words control whether the password is stored encrypted in
the system catalogs. (If neither is specified, the default behavior is
determined by the configuration parameter password_encryption.) If the
presented password string is already in MD5-encrypted or SCRAM-encrypted
format, then it is stored encrypted as-is, regardless of whether
ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt
the specified encrypted password string). This allows reloading of
encrypted passwords during dump/restore.
8<----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.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 03/11/2017 02:21 PM, Michael Paquier wrote:
On Sun, Mar 12, 2017 at 5:35 AM, Joe Conway <mail@joeconway.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.
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?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Sun, Mar 12, 2017 at 8:04 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/11/2017 02:21 PM, Michael Paquier wrote:
On Sun, Mar 12, 2017 at 5:35 AM, Joe Conway <mail@joeconway.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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().
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.
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?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Sun, Mar 12, 2017 at 9:10 AM, Joe Conway <mail@joeconway.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 03/11/2017 11:07 PM, Michael Paquier wrote:
Having a RLS on pg_authid to allow a user to look at its own password
type is an idea.
Given that that is not likely at this stage of the dev cycle, what about
a special purpose SQL function that returns the password type for the
current user? Or is it a security issue of some sort to allow a user to
know their own password type?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Fri, Mar 10, 2017 at 5:43 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Sat, Mar 11, 2017 at 2:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
Should the \password tool in psql inspect password_encryption and act on it
being 'scram'?Not sure if it is wise to change the default fot this release.
Seems like an odd way to phrase it. Aren't we talking about making a
feature that worked in previous releases continue to work?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Mar 13, 2017 at 12:48 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/11/2017 11:07 PM, Michael Paquier wrote:
Having a RLS on pg_authid to allow a user to look at its own password
type is an idea.Given that that is not likely at this stage of the dev cycle, what about
a special purpose SQL function that returns the password type for the
current user?
OK, so what about doing the following then:
1) Create pg_role_password_type(oid), taking a role OID in input and
returning the type.
2) Extend PQencryptPassword with a method, and document. Plaintext is forbidden.
3) Extend \password in psql with a similar -method=scram|md5 argument,
and forbid the use of "plain" format.
After thinking about it, extending PQencryptPassword() would lead to
future breakage, which makes it clear to not fall into the trap of
having password_encryption set to scram in the server's as well as in
pg_hba.conf and PQencryptPassword() enforcing things to md5.
Or is it a security issue of some sort to allow a user to
know their own password type?
Don't think so. Any user has access to the value of
password_encryption. So one can guess what will be the format of a
password hashed.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Mar 13, 2017 at 9:15 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Mar 10, 2017 at 5:43 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Sat, Mar 11, 2017 at 2:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
Should the \password tool in psql inspect password_encryption and act on it
being 'scram'?Not sure if it is wise to change the default fot this release.
Seems like an odd way to phrase it. Aren't we talking about making a
feature that worked in previous releases continue to work?
Considering how fresh scram is, it seems clear to me that we do not
want to just switch the default values of password_encryption, the
default behaviors of PQencryptPassword() and \password only to scram,
but have something else. Actually if we change nothing for default
deployments of Postgres using md5, PQencryptPassword() and \password
would still work properly.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 10, 2017 at 2:43 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:
On Sat, Mar 11, 2017 at 2:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
Should the \password tool in psql inspect password_encryption and act on
it
being 'scram'?
Not sure if it is wise to change the default fot this release.
I'm not proposing that we change the default value of password_encryption,
only that \password respect whatever value it currently finds there. But
after thinking about it a bit more, I reached the same conclusion that Joe
did, that it should use the same hashing method as the current password
does, and only consult password_encryption if there is no password
currently set.
A patch among those lines would be a simple, do people feel that this
should be part of PG 10?
I think it is pretty important to have some way of setting the password
that doesn't risk it ending up in the server log file, or .psql_history, or
having someone shoulder-surf it.
Cheers,
Jeff
On 03/12/2017 08:10 PM, Michael Paquier wrote:
OK, so what about doing the following then:
1) Create pg_role_password_type(oid), taking a role OID in input and
returning the type.
That version would make sense for administrative use. I still think we
might want a version of this that takes no argument, works on the
current_user, and is executable by anyone.
2) Extend PQencryptPassword with a method, and document. Plaintext is forbidden.
Check, although if "plain" were allowed as a method for the sake of
consistency/completeness the function could just immediately return the
argument.
3) Extend \password in psql with a similar -method=scram|md5 argument,
and forbid the use of "plain" format.
Not sure why we would forbid "plain" here unless we remove it entirely
elsewhere.
After thinking about it, extending PQencryptPassword() would lead to
future breakage, which makes it clear to not fall into the trap of
having password_encryption set to scram in the server's as well as in
pg_hba.conf and PQencryptPassword() enforcing things to md5.
I'm not grokking this statement
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Tue, Mar 14, 2017 at 9:54 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/12/2017 08:10 PM, Michael Paquier wrote:
OK, so what about doing the following then:
1) Create pg_role_password_type(oid), taking a role OID in input and
returning the type.That version would make sense for administrative use. I still think we
might want a version of this that takes no argument, works on the
current_user, and is executable by anyone.
Sure.
2) Extend PQencryptPassword with a method, and document. Plaintext is forbidden.
Check, although if "plain" were allowed as a method for the sake of
consistency/completeness the function could just immediately return the
argument.3) Extend \password in psql with a similar -method=scram|md5 argument,
and forbid the use of "plain" format.Not sure why we would forbid "plain" here unless we remove it entirely
elsewhere.
OK. I don't mind about those two, as long as documentation is clear
enough about what using plain leads to.
After thinking about it, extending PQencryptPassword() would lead to
future breakage, which makes it clear to not fall into the trap of
having password_encryption set to scram in the server's as well as in
pg_hba.conf and PQencryptPassword() enforcing things to md5.I'm not grokking this statement
To grok: "To understand profoundly through intuition or empathy.".
Learning a new thing everyday.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Mar 12, 2017 at 11:14 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Mon, Mar 13, 2017 at 9:15 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Mar 10, 2017 at 5:43 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Sat, Mar 11, 2017 at 2:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
Should the \password tool in psql inspect password_encryption and act on it
being 'scram'?Not sure if it is wise to change the default fot this release.
Seems like an odd way to phrase it. Aren't we talking about making a
feature that worked in previous releases continue to work?Considering how fresh scram is, it seems clear to me that we do not
want to just switch the default values of password_encryption, the
default behaviors of PQencryptPassword() and \password only to scram,
but have something else. Actually if we change nothing for default
deployments of Postgres using md5, PQencryptPassword() and \password
would still work properly.
I'm not talking about changing the default, just having it be possible
to use \password with the new system as it was with the old, whatever
exactly we think that means.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
I'm not talking about changing the default, just having it be possible
to use \password with the new system as it was with the old, whatever
exactly we think that means.
Seems to me the intended behavior of \password is to use the best
available practice. So my guess is that it ought to use SCRAM when
talking to a >= 10.0 server. What the previous password was ought
to be irrelevant, even if it could find that out which it shouldn't
be able to IMO.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Mar 14, 2017 at 11:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I'm not talking about changing the default, just having it be possible
to use \password with the new system as it was with the old, whatever
exactly we think that means.
I think that this means looking at password_encryption within
PQencryptPassword(), something that could silently break some
applications. That's why with Joe we are mentioning upthread to extend
PQencryptPassword() with a hashing method, and have a function to
allow retrieval of the password type for a given user.
Seems to me the intended behavior of \password is to use the best
available practice. So my guess is that it ought to use SCRAM when
talking to a >= 10.0 server. What the previous password was ought
to be irrelevant, even if it could find that out which it shouldn't
be able to IMO.
And in a release or two? SCRAM being a fresh feature, switching the
hashing now is not much a conservative approach.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael Paquier <michael.paquier@gmail.com> writes:
On Tue, Mar 14, 2017 at 11:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Seems to me the intended behavior of \password is to use the best
available practice. So my guess is that it ought to use SCRAM when
talking to a >= 10.0 server. What the previous password was ought
to be irrelevant, even if it could find that out which it shouldn't
be able to IMO.
And in a release or two? SCRAM being a fresh feature, switching the
hashing now is not much a conservative approach.
If some other practice becomes better in v12, then we teach it about that
one. It's not like psql hasn't got many other server-version-dependent
behaviors.
Alternatively, if what you mean by that is you don't trust SCRAM at all,
maybe we'd better revert the feature as not being ready for prime time.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Mar 14, 2017 at 12:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Paquier <michael.paquier@gmail.com> writes:
If some other practice becomes better in v12, then we teach it about that
one. It's not like psql hasn't got many other server-version-dependent
behaviors.
Okay. Looking at the code, scram_build_verifier is only available on
the backend side, so if we need to rethink it a bit to be able to have
libpq build a SCRAM verifier:
- The allocation of the verifier buffer needs to happen outside it,
and the caller needs to provide an allocated buffer. Its size had
better be calculated a macro.
- The routine needs to be moved to scram-common.c.
- a copy of hex_encode, say pg_hex_encode added in its own file in
src/common/, needs to be provided. That's 6 lines of code, we could
just have that in scram-common.c.
Does that sound correct?
Alternatively, if what you mean by that is you don't trust SCRAM at all,
maybe we'd better revert the feature as not being ready for prime time.
FWIW, I am confident about the code. Based on years watching this
mailing list, switching long-term behaviors is usually done in a less
faster pace, that's the only reason on which my opinion is based.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
