Обсуждение: PGUSER and initdb
PGUSER doesn't work with initdb and pg_ctl initdb. Lets explain, where this can be important. For instance in MacOS the mostconvenient 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 soon. Convenient. But this command don't have option to define name of superuser. The only variant to set name off superuser is to use PGUSERenvironment variable. But PGUSER understand only pg_upgrade, but not 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 themost 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 andso 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 PGUSERenvironment 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 Section33.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 Section33.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 Section33.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 Section33.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