Thread: 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

If unset, it will use the PostgreSQL user name also as the database name.

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

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

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