Re: Documentation for CREATE USER

In short, I think it should be made clear that an encrypted user password
for all intents and purposes, is simply obfuscated. Encrypted implies
secure, and after looking into why md5 is the only option (see [1]
<http://www.postgresql.org/message-id/7083.1200929593@sss.pgh.pa.us> link
below), I feel that portion of the documentation should clarify intent.

The documentation for CREATE USER can be found at link [2]
<http://www.postgresql.org/docs/8.0/static/sql-createuser.html> below.

[1]
http://www.postgresql.org/message-id/7083.1200929593@sss.pgh.pa.us

[2]
http://www.postgresql.org/docs/8.0/static/sql-createuser.html

I'm new to this mailing list, and couldn't find any "read first" documents
on conventions to follow - if any. Please let me know if I missed
something prior to subscribing.

Thanks,
Phil

On 14/10/10 6:29, Phil Olson wrote:

> [2]
> http://www.postgresql.org/docs/8.0/static/sql-createuser.html

Just a quick note - the above link is to documentation for a PostgreSQL
version which is no longer supported. If you replace the version number
in the URL with "current", you'll be guaranteed to have a link to the
most recent release.

BTW, "CREATE USER" is now an alias for CREATE ROLE:

http://www.postgresql.org/docs/current/static/sql-createrole.html

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Oct 9, 2014 at 5:29 PM, Phil Olson <philip(dot)olson(dot)m(at)gmail(dot)com> wrote:
> In short, I think it should be made clear that an encrypted user password
> for all intents and purposes, is simply obfuscated.

Well, I do agree that "encrypted" is a misleading word to use in this
context, since "hashed" is the technically correct description of what
gets stored in pg_authid.rolpassword when one uses:

CREATE ROLE ... WITH ENCRYPTED PASSWORD 'secret'

Though of course it's too late to change the SQL syntax we accept. And
I'm not sure whether a doc change is needed, or where it should go if
it is -- the section on pg_authid [1] already accurately spells out
how we store rolpassword when the ENCRYPTED option is given. Possibly
that description could be a bit more clear about the fact that
"possibly encrypted" is talking about the 'ENCRYPTED' option of CREATE
ROLE.

I don't agree with the characterization of md5 hashing with a salt
(not a particularly strong salt choice, but a salt nonetheless) as
being "simply obfuscated". Here's a short summary of how obfuscation
vs. hashing vs. encryption are generally distinguished:

http://stackoverflow.com/a/184369/1772673

> Encrypted implies secure,

See link above, "encrypted" has a basically well-understood definition.

Josh

[1] http://www.postgresql.org/docs/current/static/catalog-pg-authid.html

*forgot to reply-all

I appreciate the feedback. I do understand in the scheme of things, this
is very low priority so I'll entrust the final say with you guys and
refrain from following up beyond this email.

> I'm not sure whether a doc is needed
It should avoid threads such as the one I first emailed about seen in [1].
In most contexts, if I had the option to "encrypt" a password and the only
choice of method was md5 - it begs to be googled "why"

> or where it [documentation] should go if it is
I figure it would be placed in as a note, similarly to the note seen below
"Joins Between Tables" found at [2]

> I don't agree with the characterization of md5 hashing with a salt
as being "simply obfuscated"
My point here is that given the developer's comments seen in [1]

"
So the only reason we bother with hashing here is to keep a superuser from
finding out your cleartext password, which might possibly let him crack
into non-database services that you foolishly used the same password for
"

Hashing is the means to reach an effectively obfuscated string of text
(general definition of the word: "render obscure, unclear, or
unintelligible.").

I appreciate your time,
Phil

[1]
http://www.postgresql.org/message-id/7083.1200929593@sss.pgh.pa.us

[2]
http://www.postgresql.org/docs/9.3/static/tutorial-join.html

On Tue, Oct 14, 2014 at 11:22 AM, Josh Kupershmidt <schmiddy(at)gmail(dot)com>
wrote:

> On Thu, Oct 9, 2014 at 5:29 PM, Phil Olson <philip(dot)olson(dot)m(at)gmail(dot)com>
> wrote:
> > In short, I think it should be made clear that an encrypted user password
> > for all intents and purposes, is simply obfuscated.
>
> Well, I do agree that "encrypted" is a misleading word to use in this
> context, since "hashed" is the technically correct description of what
> gets stored in pg_authid.rolpassword when one uses:
>
> CREATE ROLE ... WITH ENCRYPTED PASSWORD 'secret'
>
> Though of course it's too late to change the SQL syntax we accept. And
> I'm not sure whether a doc change is needed, or where it should go if
> it is -- the section on pg_authid [1] already accurately spells out
> how we store rolpassword when the ENCRYPTED option is given. Possibly
> that description could be a bit more clear about the fact that
> "possibly encrypted" is talking about the 'ENCRYPTED' option of CREATE
> ROLE.
>
> I don't agree with the characterization of md5 hashing with a salt
> (not a particularly strong salt choice, but a salt nonetheless) as
> being "simply obfuscated". Here's a short summary of how obfuscation
> vs. hashing vs. encryption are generally distinguished:
>
> http://stackoverflow.com/a/184369/1772673
>
> > Encrypted implies secure,
>
> See link above, "encrypted" has a basically well-understood definition.
>
> Josh
>
> [1] http://www.postgresql.org/docs/current/static/catalog-pg-authid.html
>