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

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

From
Matthias Apitz
Date:
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



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

From
Guillaume Lelarge
Date:
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.

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

From
"David G. Johnston"
Date:
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.

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

From
Matthias Apitz
Date:
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



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

From
Guillaume Lelarge
Date:
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.

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

From
Matthias Apitz
Date:
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



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

From
Guillaume Lelarge
Date:
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)

From
hubert depesz lubaczewski
Date:
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



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

From
Tom Lane
Date:
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