psql's default database on connect (our internal ref. SRP-30861)

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.

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