Hey! We are here with another common issue.
TL;DR: psql -d <database_name>
By default, psql client tries to connect to a database with your username. Let’s see together:
root@bbe89c90b5eb:/# useradd testuser1
root@bbe89c90b5eb:/# useradd testuser2
root@bbe89c90b5eb:/# su - postgres
postgres@bbe89c90b5eb:~$ psql
psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.
postgres=# CREATE ROLE testuser1 WITH superuser login;
CREATE ROLE
postgres=# CREATE ROLE testuser2 WITH superuser login;
CREATE ROLE
postgres=# \q
postgres@bbe89c90b5eb:~$ exit
logout
root@bbe89c90b5eb:/# su - testuser1
su: warning: cannot change directory to /home/testuser1: No such file or directory
$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "testuser1" does not exist
$ exit
root@bbe89c90b5eb:/# su - testuser2
su: warning: cannot change directory to /home/testuser2: No such file or directory
$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "testuser2" does not exist
In the example above, i created two users testuser1 and testuser2, defined roles for those roles to let them login. I switched to those user and tried to execulte psql call. But it failed with the error: database does not exists.
As you can see in the error message, PostgreSQL first looks for a database name, same with username.
You may not want to create database for each user, then user should login to psql with -d parameter:
$ psql -d postgres
psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.
postgres=#
As you can see, you can pass database name to psql as parameter.
If you have a question, or something to add, please leave a comment below!
Leave a Reply