Re: pgsql readme: update auth method
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,
Re: pgsql readme: update auth method
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.
Re: pgsql readme: update auth method
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
Re: pgsql readme: update auth method
On Sat, 15 Sep 2018 11:17:27 -0700, Chris Bennett
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!
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
--- Makefile6 Sep 2018 19:08:59 - 1.240
+++ Makefile17 Sep 2018 21:13:05 -
@@ -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= ecpg7.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 - 1.24
+++ pkg/README-server 17 Sep 2018 21:13:05 -
@@ -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 \
Re: pgsql readme: update auth method
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
Re: pgsql readme: update auth method
On Sat, 15 Sep 2018 05:11:19 -0700, Chris Bennett
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
--- Makefile6 Sep 2018 19:08:59 - 1.240
+++ Makefile15 Sep 2018 16:00:00 -
@@ -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= ecpg7.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 - 1.24
+++ pkg/README-server 15 Sep 2018 16:00:00 -
@@ -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 \
Re: pgsql readme: update auth method
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
--- Makefile6 Sep 2018 19:08:59 - 1.240
+++ Makefile15 Sep 2018 02:11:11 -
@@ -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= ecpg7.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 - 1.24
+++ pkg/README-server 15 Sep 2018 02:11:11 -
@@ -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 \
Re: pgsql readme: update auth method
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
