Обсуждение: psql's default database on connect (our internal ref. SRP-30861)
Hello,
testpos@srap53dxr1:~> psql --help
...
-d, --dbname=DBNAME database name to connect to (default: "testpos")
testpos@srap53dxr1:~> whoami
testpos
testpos@srap53dxr1:~> env | grep PG
PGPASSWORD=xxxxxxxx
PGHOST=localhost
PGPORT=5432
testpos@srap53dxr1:~> LANG=C psql -Usisis
psql (13.3)
Type "help" for help.
sisis=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
sisis | sisis | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
...
Why psql connects to the database 'sisis'? I was expecting that it
will try to connect to a database 'testpos' as this is the UNIX login
and as '-d....' was not specified. I was expecting in this case an
error like this:
testpos@srap53dxr1:~> export PGDATABASE=testpos
testpos@srap53dxr1:~> psql -Usisis
psql: error: FATAL: database »testpos« does not exist
What do I uderstand wrong?
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Tear it down! Defund the Humboldt Forum!
https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html
Le ven. 6 août 2021 à 08:53, Matthias Apitz <guru@unixarea.de> a écrit :
Hello,
testpos@srap53dxr1:~> psql --help
...
-d, --dbname=DBNAME database name to connect to (default: "testpos")
testpos@srap53dxr1:~> whoami
testpos
testpos@srap53dxr1:~> env | grep PG
PGPASSWORD=xxxxxxxx
PGHOST=localhost
PGPORT=5432
testpos@srap53dxr1:~> LANG=C psql -Usisis
psql (13.3)
Type "help" for help.
sisis=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
sisis | sisis | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
...
Why psql connects to the database 'sisis'? I was expecting that it
will try to connect to a database 'testpos' as this is the UNIX login
and as '-d....' was not specified. I was expecting in this case an
error like this:
testpos@srap53dxr1:~> export PGDATABASE=testpos
testpos@srap53dxr1:~> psql -Usisis
psql: error: FATAL: database »testpos« does not exist
What do I uderstand wrong?
When you try to connect without specifying a database name on the command line, psql will check PGDATABASE. If unset, it will use the PostgreSQL user name also as the database name. If you don't specify explicitly the PostgreSQL username, it will check PGUSER and, if unset, will use the OS username.
That matches all your examples above.
--
Guillaume.
On Friday, August 6, 2021, Guillaume Lelarge <guillaume@lelarge.info> wrote:
If unset, it will use the PostgreSQL user name also as the database name.
This is true, but its not what the docs says:
psql usage doc:
“The default user name is your operating-system user name, as is the default database name.”
Specifying -U doesn’t change one’s operating-system name, and the “as is” language is shorthand for “the default database name is your operating-system user name”.
David J.
El día viernes, agosto 06, 2021 a las 09:07:56a. m. +0200, Guillaume Lelarge escribió:
> Le ven. 6 août 2021 à 08:53, Matthias Apitz <guru@unixarea.de> a écrit :
>
> >
> > Hello,
> >
> > testpos@srap53dxr1:~> psql --help
> > ...
> > -d, --dbname=DBNAME database name to connect to (default: "testpos")
> >
> > testpos@srap53dxr1:~> whoami
> > testpos
> >
> > testpos@srap53dxr1:~> env | grep PG
> > PGPASSWORD=xxxxxxxx
> > PGHOST=localhost
> > PGPORT=5432
> >
> > testpos@srap53dxr1:~> LANG=C psql -Usisis
> > psql (13.3)
> > Type "help" for help.
> >
> > sisis=# \l
> > List of databases
> > Name | Owner | Encoding | Collate | Ctype | Access
> > privileges
> >
> > -----------+----------+----------+-------------+-------------+-----------------------
> > postgres | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> > sisis | sisis | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> > ...
> >
> > Why psql connects to the database 'sisis'? I was expecting that it
> > will try to connect to a database 'testpos' as this is the UNIX login
> > and as '-d....' was not specified. I was expecting in this case an
> > error like this:
> >
> > testpos@srap53dxr1:~> export PGDATABASE=testpos
> > testpos@srap53dxr1:~> psql -Usisis
> > psql: error: FATAL: database »testpos« does not exist
> >
> > What do I uderstand wrong?
> >
>
> When you try to connect without specifying a database name on the command
> line, psql will check PGDATABASE. If unset, it will use the PostgreSQL user
> name also as the database name. If you don't specify explicitly the
> PostgreSQL username, it will check PGUSER and, if unset, will use the OS
> username.
>
> That matches all your examples above.
No, it does not match all my examples. Read again what it says as
default on --help.
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Tear it down! Defund the Humboldt Forum!
https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html
Le ven. 6 août 2021 à 09:25, Matthias Apitz <guru@unixarea.de> a écrit :
El día viernes, agosto 06, 2021 a las 09:07:56a. m. +0200, Guillaume Lelarge escribió:
> Le ven. 6 août 2021 à 08:53, Matthias Apitz <guru@unixarea.de> a écrit :
>
> >
> > Hello,
> >
> > testpos@srap53dxr1:~> psql --help
> > ...
> > -d, --dbname=DBNAME database name to connect to (default: "testpos")
> >
> > testpos@srap53dxr1:~> whoami
> > testpos
> >
> > testpos@srap53dxr1:~> env | grep PG
> > PGPASSWORD=xxxxxxxx
> > PGHOST=localhost
> > PGPORT=5432
> >
> > testpos@srap53dxr1:~> LANG=C psql -Usisis
> > psql (13.3)
> > Type "help" for help.
> >
> > sisis=# \l
> > List of databases
> > Name | Owner | Encoding | Collate | Ctype | Access
> > privileges
> >
> > -----------+----------+----------+-------------+-------------+-----------------------
> > postgres | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> > sisis | sisis | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> > ...
> >
> > Why psql connects to the database 'sisis'? I was expecting that it
> > will try to connect to a database 'testpos' as this is the UNIX login
> > and as '-d....' was not specified. I was expecting in this case an
> > error like this:
> >
> > testpos@srap53dxr1:~> export PGDATABASE=testpos
> > testpos@srap53dxr1:~> psql -Usisis
> > psql: error: FATAL: database »testpos« does not exist
> >
> > What do I uderstand wrong?
> >
>
> When you try to connect without specifying a database name on the command
> line, psql will check PGDATABASE. If unset, it will use the PostgreSQL user
> name also as the database name. If you don't specify explicitly the
> PostgreSQL username, it will check PGUSER and, if unset, will use the OS
> username.
>
> That matches all your examples above.
No, it does not match all my examples. Read again what it says as
default on --help.
Actually, it does. You launched "psql --help" without specifying a PostgreSQL username and database name, so it used your OS username as both of them, hence the "default: testpos" for the database.
El día viernes, agosto 06, 2021 a las 09:34:42a. m. +0200, Guillaume Lelarge escribió:
> > No, it does not match all my examples. Read again what it says as
> > default on --help.
> >
>
> Actually, it does. You launched "psql --help" without specifying a
> PostgreSQL username and database name, so it used your OS username as both
> of them, hence the "default: testpos" for the database.
Sorry, it does not:
testpos@srap53dxr1:~> psql --help -Usisis | grep DBNAME
psql [OPTION]... [DBNAME [USERNAME]]
-d, --dbname=DBNAME database name to connect to (default: "testpos")
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Tear it down! Defund the Humboldt Forum!
https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html
Le ven. 6 août 2021 à 09:51, Matthias Apitz <guru@unixarea.de> a écrit :
El día viernes, agosto 06, 2021 a las 09:34:42a. m. +0200, Guillaume Lelarge escribió:
> > No, it does not match all my examples. Read again what it says as
> > default on --help.
> >
>
> Actually, it does. You launched "psql --help" without specifying a
> PostgreSQL username and database name, so it used your OS username as both
> of them, hence the "default: testpos" for the database.
Sorry, it does not:
testpos@srap53dxr1:~> psql --help -Usisis | grep DBNAME
psql [OPTION]... [DBNAME [USERNAME]]
-d, --dbname=DBNAME database name to connect to (default: "testpos")
This wasn't in your first examples. I agree it's misleading. I guess this happens because psql has no idea which database it will connect to in the end. It isn't its call, but libpq's.
Re: psql's default database on connect (our internal ref. SRP-30861)
От
hubert depesz lubaczewski
Дата:
On Fri, Aug 06, 2021 at 08:53:22AM +0200, Matthias Apitz wrote: > What do I uderstand wrong? Please check https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS Specifically: > dbname : The database name. Defaults to be the same as the user name. > In certain contexts, the value is checked for extended > formats; see Section 33.1.1 for more details on those. > user : PostgreSQL user name to connect as. Defaults to be the same > as the operating system name of the user running the > application. depesz
Matthias Apitz <guru@unixarea.de> writes:
> Sorry, it does not:
> testpos@srap53dxr1:~> psql --help -Usisis | grep DBNAME
> psql [OPTION]... [DBNAME [USERNAME]]
> -d, --dbname=DBNAME database name to connect to (default: "testpos")
While that's certainly confusing, I'm not sure it's wrong.
I'd interpret "default" as meaning "default if you don't specify
something different on the command line". The default username
is reported under similar rules:
$ psql --help -Ufoo | grep user
-U, --username=USERNAME database user name (default: "tgl")
In any case, these choices of default have stood for a very long
time and we're unlikely to consider changing them. If (some parts
of) the docs are inaccurate we should change the docs.
regards, tom lane