pgsql readme: update auth method

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

pgsql readme: update auth method

Daniel Jakots-3
Hi,

With the version 10, pgsql comes with a new auth method called
scram-sha-256.
https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-PASSWORD

I think we should advise users to pick that one rather than md5. In the
upgrade part, the user has to copy the pg_hba.conf and this file lists
the auth method. Luckily there's no need to sed it, because according
to the docs:

> To ease transition from the md5 method to the newer SCRAM method, if
> md5 is specified as a method in pg_hba.conf but the user's password
> on the server is encrypted for SCRAM (see below), then SCRAM-based
> authentication will automatically be chosen instead.

The other thing is that iiuc, the library has to support this new
auth method but this should affect only the superuser and the
superuser shouldn't be used by any application directly so I think it's
fine.


What do you think?

Cheers,
Daniel


Index: Makefile
===================================================================
RCS file: /cvs/ports/databases/postgresql/Makefile,v
retrieving revision 1.240
diff -u -p -r1.240 Makefile
--- Makefile 6 Sep 2018 19:08:59 -0000 1.240
+++ Makefile 15 Sep 2018 02:11:11 -0000
@@ -17,7 +17,7 @@ PKGNAME-contrib=postgresql-contrib-${VER
 PKGNAME-plpython=postgresql-plpython-${VERSION}
 PKGNAME-pg_upgrade=postgresql-pg_upgrade-${VERSION}
 REVISION-main= 0
-REVISION-server=1
+REVISION-server=2
 
 CATEGORIES= databases
 SHARED_LIBS= ecpg 7.9 \
Index: pkg/README-server
===================================================================
RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v
retrieving revision 1.24
diff -u -p -r1.24 README-server
--- pkg/README-server 4 Sep 2018 12:46:10 -0000 1.24
+++ pkg/README-server 15 Sep 2018 02:11:11 -0000
@@ -14,17 +14,17 @@ initialized using the initdb command.
 
 If you are installing PostgreSQL for the first time, you have to create
 a default database first.  In the following example we install a database
-in /var/postgresql/data with a dba account 'postgres' and md5 authentication.
-We will be prompted for a password to protect the dba account:
+in /var/postgresql/data with a dba account 'postgres' and scram-sha-256
+authentication. We will be prompted for a password to protect the dba account:
 
        # su - _postgresql
        $ mkdir /var/postgresql/data
-       $ initdb -D /var/postgresql/data -U postgres -A md5 -W
+       $ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -W
 
 Please note that by default the cluster's encoding will be SQL_ASCII. If
 you want to have another default encoding, use the option -E with initdb:
 
-       $ initdb -D /var/postgresql/data -U postgres -E UTF8 -A md5 -W
+       $ initdb -D /var/postgresql/data -U postgres -E UTF8 -A scram-sha-256 -W
 
 If your cluster is already created, you can specify another encoding when
 you create a new database with this command:
@@ -129,7 +129,8 @@ to the current version.
 
 5) Create a new data directory (adding -E UTF8 to the initdb line if needed):
 # su _postgresql -c "mkdir /var/postgresql/data"
-# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
+# su _postgresql -c \
+    "initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -W"
 
 6) Restore your old pg_hba.conf and (if used) SSL certificates
 # su _postgresql -c \
@@ -151,7 +152,7 @@ Option 2: pg_upgrade
 
 This will work for an upgrade from the previous major version of
 PostgreSQL supported by OpenBSD to the current version, and should be
-faster than a dump and reload, especially for large databases.
+faster than a dump and reload, especially for large databases.
 
 1) Shutdown the server:
 # rcctl stop postgresql
@@ -164,7 +165,8 @@ faster than a dump and reload, especiall
 
 4) Create a new data directory (adding -E UTF8 to the initdb line if needed):
 # su _postgresql -c "mkdir /var/postgresql/data"
-# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
+# su _postgresql -c \
+    "initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -W"
 
 5) Restore your old pg_hba.conf and (if used) SSL certificates
 # su _postgresql -c \

Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Chris Bennett-3
IMHO, I am no expert at all in which encoding is most commonly used in
practice, but I cannot even imagine myself ever wanting to choose ASCII
first. Since we might change the readme right now, could we make UTF8
the default and mention ASCII as another possible option instead?

I have one old and unimportant database that I created with ASCII when I
used postgresql for the first time after following the instructions as
given. I regret the choice, not that it really ever mattered for that
database.

Thanks,
Chris Bennett


Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Edward Lopez-Acosta
In reply to this post by Daniel Jakots-3
May want to be careful with this as not all applications and libraries
have been updated for the new encryption and will result in errors.

With that said I do think there should be a note to use real standards,
SCRAM-SHA-256, rather than their hand rolled MD5 implementation.

On 2018-09-14 21:41, Daniel Jakots wrote:

> Hi,
>
> With the version 10, pgsql comes with a new auth method called
> scram-sha-256.
> https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-PASSWORD
>
> I think we should advise users to pick that one rather than md5. In the
> upgrade part, the user has to copy the pg_hba.conf and this file lists
> the auth method. Luckily there's no need to sed it, because according
> to the docs:
>
>> To ease transition from the md5 method to the newer SCRAM method, if
>> md5 is specified as a method in pg_hba.conf but the user's password
>> on the server is encrypted for SCRAM (see below), then SCRAM-based
>> authentication will automatically be chosen instead.
>
> The other thing is that iiuc, the library has to support this new
> auth method but this should affect only the superuser and the
> superuser shouldn't be used by any application directly so I think it's
> fine.
>
>
> What do you think?
>
> Cheers,
> Daniel
>
>
> Index: Makefile
> ===================================================================
> RCS file: /cvs/ports/databases/postgresql/Makefile,v
> retrieving revision 1.240
> diff -u -p -r1.240 Makefile
> --- Makefile 6 Sep 2018 19:08:59 -0000 1.240
> +++ Makefile 15 Sep 2018 02:11:11 -0000
> @@ -17,7 +17,7 @@ PKGNAME-contrib=postgresql-contrib-${VER
>   PKGNAME-plpython=postgresql-plpython-${VERSION}
>   PKGNAME-pg_upgrade=postgresql-pg_upgrade-${VERSION}
>   REVISION-main= 0
> -REVISION-server=1
> +REVISION-server=2
>  
>   CATEGORIES= databases
>   SHARED_LIBS= ecpg 7.9 \
> Index: pkg/README-server
> ===================================================================
> RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v
> retrieving revision 1.24
> diff -u -p -r1.24 README-server
> --- pkg/README-server 4 Sep 2018 12:46:10 -0000 1.24
> +++ pkg/README-server 15 Sep 2018 02:11:11 -0000
> @@ -14,17 +14,17 @@ initialized using the initdb command.
>  
>   If you are installing PostgreSQL for the first time, you have to create
>   a default database first.  In the following example we install a database
> -in /var/postgresql/data with a dba account 'postgres' and md5 authentication.
> -We will be prompted for a password to protect the dba account:
> +in /var/postgresql/data with a dba account 'postgres' and scram-sha-256
> +authentication. We will be prompted for a password to protect the dba account:
>  
>          # su - _postgresql
>          $ mkdir /var/postgresql/data
> -       $ initdb -D /var/postgresql/data -U postgres -A md5 -W
> +       $ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -W
>  
>   Please note that by default the cluster's encoding will be SQL_ASCII. If
>   you want to have another default encoding, use the option -E with initdb:
>  
> -       $ initdb -D /var/postgresql/data -U postgres -E UTF8 -A md5 -W
> +       $ initdb -D /var/postgresql/data -U postgres -E UTF8 -A scram-sha-256 -W
>  
>   If your cluster is already created, you can specify another encoding when
>   you create a new database with this command:
> @@ -129,7 +129,8 @@ to the current version.
>  
>   5) Create a new data directory (adding -E UTF8 to the initdb line if needed):
>   # su _postgresql -c "mkdir /var/postgresql/data"
> -# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
> +# su _postgresql -c \
> +    "initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -W"
>  
>   6) Restore your old pg_hba.conf and (if used) SSL certificates
>   # su _postgresql -c \
> @@ -151,7 +152,7 @@ Option 2: pg_upgrade
>  
>   This will work for an upgrade from the previous major version of
>   PostgreSQL supported by OpenBSD to the current version, and should be
> -faster than a dump and reload, especially for large databases.
> +faster than a dump and reload, especially for large databases.
>  
>   1) Shutdown the server:
>   # rcctl stop postgresql
> @@ -164,7 +165,8 @@ faster than a dump and reload, especiall
>  
>   4) Create a new data directory (adding -E UTF8 to the initdb line if needed):
>   # su _postgresql -c "mkdir /var/postgresql/data"
> -# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
> +# su _postgresql -c \
> +    "initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -W"
>  
>   5) Restore your old pg_hba.conf and (if used) SSL certificates
>   # su _postgresql -c \
>

Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Daniel Jakots-3
In reply to this post by Chris Bennett-3
On Sat, 15 Sep 2018 05:11:19 -0700, Chris Bennett
<[hidden email]> wrote:

> could we make UTF8 the default

Sounds sane to me.

> and mention ASCII as another possible option instead?

I'd prefer to zap it altogether. I hope users look up in the pgsql doc
what the arguments are for.

Does this diff would work for you?

Cheers,
Daniel

Index: Makefile
===================================================================
RCS file: /cvs/ports/databases/postgresql/Makefile,v
retrieving revision 1.240
diff -u -p -r1.240 Makefile
--- Makefile 6 Sep 2018 19:08:59 -0000 1.240
+++ Makefile 15 Sep 2018 16:00:00 -0000
@@ -17,7 +17,7 @@ PKGNAME-contrib=postgresql-contrib-${VER
 PKGNAME-plpython=postgresql-plpython-${VERSION}
 PKGNAME-pg_upgrade=postgresql-pg_upgrade-${VERSION}
 REVISION-main= 0
-REVISION-server=1
+REVISION-server=2
 
 CATEGORIES= databases
 SHARED_LIBS= ecpg 7.9 \
Index: pkg/README-server
===================================================================
RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v
retrieving revision 1.24
diff -u -p -r1.24 README-server
--- pkg/README-server 4 Sep 2018 12:46:10 -0000 1.24
+++ pkg/README-server 15 Sep 2018 16:00:00 -0000
@@ -14,21 +14,12 @@ initialized using the initdb command.
 
 If you are installing PostgreSQL for the first time, you have to create
 a default database first.  In the following example we install a database
-in /var/postgresql/data with a dba account 'postgres' and md5 authentication.
-We will be prompted for a password to protect the dba account:
+in /var/postgresql/data with a dba account 'postgres' and scram-sha-256
+authentication. We will be prompted for a password to protect the dba account:
 
        # su - _postgresql
        $ mkdir /var/postgresql/data
-       $ initdb -D /var/postgresql/data -U postgres -A md5 -W
-
-Please note that by default the cluster's encoding will be SQL_ASCII. If
-you want to have another default encoding, use the option -E with initdb:
-
-       $ initdb -D /var/postgresql/data -U postgres -E UTF8 -A md5 -W
-
-If your cluster is already created, you can specify another encoding when
-you create a new database with this command:
- CREATE DATABASE xxx TEMPLATE template0 ENCODING 'xxx' ;
+       $ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W
 
 It is strongly advised that you do not work with the postgres dba account
 other than creating more users and/or databases or for administrative tasks.
@@ -127,9 +118,10 @@ to the current version.
 4) Backup your old data directory:
 # mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR}
 
-5) Create a new data directory (adding -E UTF8 to the initdb line if needed):
+5) Create a new data directory:
 # su _postgresql -c "mkdir /var/postgresql/data"
-# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
+# su _postgresql -c \
+    "initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W"
 
 6) Restore your old pg_hba.conf and (if used) SSL certificates
 # su _postgresql -c \
@@ -151,7 +143,7 @@ Option 2: pg_upgrade
 
 This will work for an upgrade from the previous major version of
 PostgreSQL supported by OpenBSD to the current version, and should be
-faster than a dump and reload, especially for large databases.
+faster than a dump and reload, especially for large databases.
 
 1) Shutdown the server:
 # rcctl stop postgresql
@@ -162,9 +154,10 @@ faster than a dump and reload, especiall
 3) Backup your old data directory:
 # mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR}
 
-4) Create a new data directory (adding -E UTF8 to the initdb line if needed):
+4) Create a new data directory:
 # su _postgresql -c "mkdir /var/postgresql/data"
-# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
+# su _postgresql -c \
+    "initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W"
 
 5) Restore your old pg_hba.conf and (if used) SSL certificates
 # su _postgresql -c \

Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Chris Bennett-3
Perfect.
Thanks. You don't know how many times I've had to re-do after grabbing
that first line without UTF-8.

Happier,
Chris Bennett


Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Daniel Jakots-3
On Sat, 15 Sep 2018 11:17:27 -0700, Chris Bennett
<[hidden email]> wrote:

> Perfect.
> Thanks. You don't know how many times I've had to re-do after grabbing
> that first line without UTF-8.

Thanks for your feedback!

<pea> danj: about the diff for pgsql. I agree with the utf-8 bit but
for the scram-sha, i'm not sure. Not all clients support it right now.
It could be annoying

Here's a diff that only deals with the encoding stuff. OK?

Also I can't really see a case where a lib without support for the new
auth method could be a problem. If it doesn't support it, you can just
set MD5 for the app user in your pg_hba.conf, no? Am I missing
something?


Index: Makefile
===================================================================
RCS file: /cvs/ports/databases/postgresql/Makefile,v
retrieving revision 1.240
diff -u -p -r1.240 Makefile
--- Makefile 6 Sep 2018 19:08:59 -0000 1.240
+++ Makefile 17 Sep 2018 21:13:05 -0000
@@ -17,7 +17,7 @@ PKGNAME-contrib=postgresql-contrib-${VER
 PKGNAME-plpython=postgresql-plpython-${VERSION}
 PKGNAME-pg_upgrade=postgresql-pg_upgrade-${VERSION}
 REVISION-main= 0
-REVISION-server=1
+REVISION-server=2
 
 CATEGORIES= databases
 SHARED_LIBS= ecpg 7.9 \
Index: pkg/README-server
===================================================================
RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v
retrieving revision 1.24
diff -u -p -r1.24 README-server
--- pkg/README-server 4 Sep 2018 12:46:10 -0000 1.24
+++ pkg/README-server 17 Sep 2018 21:13:05 -0000
@@ -19,16 +19,7 @@ We will be prompted for a password to pr
 
        # su - _postgresql
        $ mkdir /var/postgresql/data
-       $ initdb -D /var/postgresql/data -U postgres -A md5 -W
-
-Please note that by default the cluster's encoding will be SQL_ASCII. If
-you want to have another default encoding, use the option -E with initdb:
-
-       $ initdb -D /var/postgresql/data -U postgres -E UTF8 -A md5 -W
-
-If your cluster is already created, you can specify another encoding when
-you create a new database with this command:
- CREATE DATABASE xxx TEMPLATE template0 ENCODING 'xxx' ;
+       $ initdb -D /var/postgresql/data -U postgres -A md5 -E UTF8 -W
 
 It is strongly advised that you do not work with the postgres dba account
 other than creating more users and/or databases or for administrative tasks.
@@ -127,9 +118,10 @@ to the current version.
 4) Backup your old data directory:
 # mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR}
 
-5) Create a new data directory (adding -E UTF8 to the initdb line if needed):
+5) Create a new data directory:
 # su _postgresql -c "mkdir /var/postgresql/data"
-# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
+# su _postgresql -c \
+    "initdb -D /var/postgresql/data -U postgres -A md5 -E UTF8 -W"
 
 6) Restore your old pg_hba.conf and (if used) SSL certificates
 # su _postgresql -c \
@@ -162,9 +154,10 @@ faster than a dump and reload, especiall
 3) Backup your old data directory:
 # mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR}
 
-4) Create a new data directory (adding -E UTF8 to the initdb line if needed):
+4) Create a new data directory:
 # su _postgresql -c "mkdir /var/postgresql/data"
-# su _postgresql -c "initdb -D /var/postgresql/data -U postgres -A md5 -W"
+# su _postgresql -c \
+    "initdb -D /var/postgresql/data -U postgres -A md5 -E UTF8 -W"
 
 5) Restore your old pg_hba.conf and (if used) SSL certificates
 # su _postgresql -c \


Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Chris Bennett-3
On Mon, Sep 17, 2018 at 05:20:16PM -0400, Daniel Jakots wrote:
>
> Also I can't really see a case where a lib without support for the new
> auth method could be a problem. If it doesn't support it, you can just
> set MD5 for the app user in your pg_hba.conf, no? Am I missing
> something?
>

No, sounds fine to me. I have several things that I need to eject from
using MD5. Maybe I'll go do that now-ish.

Chris Bennett


Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Stuart Henderson
In reply to this post by Daniel Jakots-3
On 2018/09/17 17:20, Daniel Jakots wrote:
> Also I can't really see a case where a lib without support for the new
> auth method could be a problem. If it doesn't support it, you can just
> set MD5 for the app user in your pg_hba.conf, no? Am I missing
> something?

OK for the UTF8 change.

README-server only deals with setting up the 'postgres' user, I'm pretty
sure SCRAM-SHA-256 makes sense for that, there shouldn't really be
issues with outdated clients connecting as postgres. I'd be happy to
change that before 6.4.

I'd be tempted to patch postgresql.conf.sample to set "password_encryption
= 'scram-sha-256'" by default as well but think that is post-6.4.

Reply | Threaded
Open this post in threaded view
|

Re: pgsql readme: update auth method

Pierre-Emmanuel Andre
On Mon, Sep 17, 2018 at 10:46:24PM +0100, Stuart Henderson wrote:

> On 2018/09/17 17:20, Daniel Jakots wrote:
> > Also I can't really see a case where a lib without support for the new
> > auth method could be a problem. If it doesn't support it, you can just
> > set MD5 for the app user in your pg_hba.conf, no? Am I missing
> > something?
>
> OK for the UTF8 change.
>
> README-server only deals with setting up the 'postgres' user, I'm pretty
> sure SCRAM-SHA-256 makes sense for that, there shouldn't really be
> issues with outdated clients connecting as postgres. I'd be happy to
> change that before 6.4.
>
> I'd be tempted to patch postgresql.conf.sample to set "password_encryption
> = 'scram-sha-256'" by default as well but think that is post-6.4.
>

Go ahead. Ok pea@ for both utf8 change and SCRAM-SHA-256.
Regards,