PGUSER and initdb

On 12/10/19 5:21 AM, Олег Самойлов wrote:

PGUSER doesn't work with initdb and pg_ctl initdb. Lets explain, where this can be important. For instance in MacOS the most convenient way to manage PostgresQL is homebrew.

To install PostgresQL:

brew install postgresql

To upgrade:

brew upgrade postgresql

this will upgrade soft and to upgrade DB

brew postgresql-upgrade-database

The later command do: install previous version of PostgresQL, move postgresql dir to postgesql.old, run pg_upgrade and so on. Convenient.

But this command don't have option to define name of superuser. The only variant to set name off superuser is to use PGUSER environment variable. But PGUSER understand only pg_upgrade, but not initdb.

Hmm:

https://www.postgresql.org/docs/12/app-initdb.html
"
-U username
--username=username

Selects the user name of the database superuser. This defaults to
the name of the effective user running initdb. It is really not
important what the superuser's name is, but one might choose to keep the
customary name postgres, even if the operating system user's name is
different.

...

This utility, like most other PostgreSQL utilities, also uses the
environment variables supported by libpq (see Section 33.14).
"

https://www.postgresql.org/docs/12/libpq-envars.html
"
PGUSER behaves the same as the user connection parameter.
"

Seems to me this is something that you need to bring up with the Brew
maintainers.

--
Adrian Klaver
adrian.klaver@aklaver.com

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 33.14).
"

https://www.postgresql.org/docs/12/libpq-envars.html
"
PGUSER behaves the same as the user connection parameter.
"

This is don't work with `initdb` and `pg_ctl initdb`.

On 12/11/19 5:18 AM, Олег Самойлов wrote:

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 33.14).
"

https://www.postgresql.org/docs/12/libpq-envars.html
"
PGUSER behaves the same as the user connection parameter.
"

This is don't work with `initdb` and `pg_ctl initdb`.

Hmm, yeah. So -U works but not the env variable. I would file a bug
report(https://www.postgresql.org/account/login/?next=/account/submitbug/):

aklaver@tito:~> env | grep PGUSER
PGUSER=postgres

/usr/local/pgsql12/bin/initdb -D pg_test_postgres/
/usr/local/pgsql12/bin/pg_ctl -D pg_test_postgres start

aklaver@tito:~> psql -d postgres -U postgres -p 5429
2019-12-11 08:25:22.334 PST [4978] FATAL: role "postgres" does not exist
psql: error: could not connect to server: FATAL: role "postgres" does
not exist
aklaver@tito:~> psql -d postgres -U aklaver -p 5429
Null display is "NULL".
psql (12.1)
Type "help" for help.

postgres=# \du
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------+-----------
aklaver | Superuser, Create role, Create DB, Replication, Bypass RLS
| {}

/usr/local/pgsql12/bin/initdb -U postgres -D pg_test_postgres/

aklaver@tito:~> psql -d postgres -U postgres -p 5429
Null display is "NULL".
psql (12.1)
Type "help" for help.

postgres=# \du
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
| {}

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 12/11/19 5:18 AM, Олег Самойлов wrote:
This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 33.14).
https://www.postgresql.org/docs/12/libpq-envars.html
PGUSER behaves the same as the user connection parameter.

This is don't work with `initdb` and `pg_ctl initdb`.

This seems like nonsense to me. The initdb documentation says perfectly
clearly that the initial superuser's name is set by -U. It does not
say anything about PGUSER. I think the reference to libpq in the docs
probably should be taken out, because initdb never uses libpq to make
a connection, so libpq's defaults are not relevant.

regards, tom lane

On 12/11/19 9:06 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 12/11/19 5:18 AM, Олег Самойлов wrote:
This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 33.14).
https://www.postgresql.org/docs/12/libpq-envars.html
PGUSER behaves the same as the user connection parameter.

This is don't work with `initdb` and `pg_ctl initdb`.

This seems like nonsense to me. The initdb documentation says perfectly
clearly that the initial superuser's name is set by -U. It does not
say anything about PGUSER. I think the reference to libpq in the docs
probably should be taken out, because initdb never uses libpq to make
a connection, so libpq's defaults are not relevant.

I am not seeing it as nonsense. The OP made a valid observation, PGUSER
is not used by the program even though the docs say it should be. If
what you say about is libpq is also valid for the other programs listed
here:

https://www.postgresql.org/docs/12/reference-server.html

then the docs probably to be gone over for them also:

https://www.postgresql.org/docs/12/app-pg-ctl.html

"pg_ctl, like most other PostgreSQL utilities, also uses the environment
variables supported by libpq (see Section 33.14)."

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com