PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Unable to access psql via username field?

PostgreSQL is interpreting the username (myuser) as the default database name when i am trying to connect using psql -h localhost -p 5432 -U myuser, thus i am not able to proceed unless i will specify also the database (see example below); however, with a username of "postgres" i was able to access, i guess the reason is that i do have a database which is named "postgres" as well.

MacBook % psql -h localhost -p 5432 -U myuser  
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "myuser" does not exist
MacBook % psql -h localhost -p 5432 -U private 
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "private" does not exist
MacBook % psql -h localhost -p 5432 -U postgres
psql (14.11 (Homebrew))
Type "help" for help.

postgres=# du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 myuser    |                                                            | {}
 postgres  | Superuser                                                  | {}
 private   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# l
                            List of databases
    Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges  
------------+---------+----------+---------+-------+---------------------
 mydatabase | private | UTF8     | C       | C     | =Tc/private        +
            |         |          |         |       | private=CTc/private+
            |         |          |         |       | myuser=CTc/private
 postgres   | private | UTF8     | C       | C     | 
 template0  | private | UTF8     | C       | C     | =c/private         +
            |         |          |         |       | private=CTc/private
 template1  | private | UTF8     | C       | C     | =c/private         +
            |         |          |         |       | private=CTc/private
(4 rows)

postgres=# q
MacBook % psql -h localhost -p 5432 -U myuser -d mydatabase                          

psql (14.11 (Homebrew))
Type "help" for help.

mydatabase-> du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 myuser    |                                                            | {}
 postgres  | Superuser                                                  | {}
 private   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Version:

PostgreSQL 14.11 (Homebrew) on x86_64-apple-darwin23.2.0, compiled by Apple clang version 15.0.0 (clang-1500.1.0.2.5), 64-bit

print screen terminal

I tried to access also via "private" username but as you can see from the terminal it didn’t work same as for "myuser", there are no databases names "private" and "myuser"…that’s how i came to conclusion that the problem is somewhere else. I’ve tried also to change the search_path

"postgres=# ALTER ROLE myuser SET search_path TO mydatabase, pg_catalog;"

But it didn’t help.

I want to be able to access :% psql -h localhost -p 5432 -U myuser
However, now i can access only via: % psql -h localhost -p 5432 -U myuser -d mydatabase

p.s. I was missing pg_hba.conf and postgresql.conf files in their respactive fdolders (share/postgresql@14/), maybe that’s nomrmal as only their sample files were availible, anyhow i’ve tried to recreatethem, but it didnt help to solve the my problem.

Tags:

2

Answers


  1. 0

    First of all, you must write down the database name when connecting from postgresql. Because the database name is mandatory, not optional, it recognizes it as a database even if you give the -U option.
    Consequently, you can access ‘psql -d <database_name> -U <user_name>’, write down the -h option when you wish to access postgresql on a different server than the current server, and give up to the -p option if it is not the default port. If you do not fill out the -U parameter, try to connect with the same user as the server hostname.

    You can use this command:

    psql -d <dbname> -U <username>

    If your OS hostname is same as your postgresql’s username, then you can use this command also:

    psql -d <dbname>
    Login or Signup to reply.
  2. 0

    Use the -d option to explicitly specify the database you want to connect to:

    psql ... -d dbname
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search