How to connect to a Postgres database without specifying a database name in PDO?

Question

I am using the following connection to connect to Postgres SQL without Database as I need to fetch all the database names later for configuration

try{
    $this->connection = new \PDO($this->database.":host=".$this->host,$this->user,$this->password);
    $this->connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    return $this->connection;
}catch(\PDOException $e){
    echo $e->getMessage();
}

But I am getting the following error

SQLSTATE[08006] [7] FATAL: database "admin" does not exist

The following are the values that are set in the properties:

$this->database = pgsql
$this->host = localhost
$this->user = admin
$this->password = admin

Can anyone please help me out how to connect to Postgres SQL without any database selection with PHP PDO

use database postgres to connect to further list databases — Vao Tsun
– Vao Tsun, Commented Feb 8, 2017 at 12:12
@Deep ya the one which I have mentioned is databaseType like MySQL, postgres — Channaveer Hakari
– Channaveer Hakari, Commented Feb 8, 2017 at 20:23
try to login with pgsql client with that user profile and see if it make the same error — am05mhz
– am05mhz, Commented Oct 31, 2017 at 7:57

Michał Zaborowski · Accepted Answer · 2017-10-27 06:57:00Z

2

There are tree options:

postgres
template0
template1

Please, be aware that connection to the database requires permission. Here is explanation what that templates are. You can look for postgresql default database. Which is quite similar topic.

answered Oct 27, 2017 at 6:57

Michał Zaborowski

4,4032 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

harmic · Accepted Answer · 2017-11-01 04:49:03Z

Postgresql does not have a possibility to connect to the database server without specifying to an existing database.

The reason you are getting this error:

SQLSTATE[08006] [7] FATAL: database "admin" does not exist

is that if you do not specify a database name, it will default to trying to connect to a database with the same name as the user you have specified (see description of connection parameter dbname).

When postgresql is first initialized, three default databases are created:

postgres - intended as a general purpose database for the default administrator user postgres to access
template0 - Master template - should not be changed after initialization
template1 - Default template - used to create all new databases from

See Template Databases for a full description of these.

If you want to get a list of the available databases, you will need to connect to one of the above databases.

I would normally use template1 for this, because in theory the postgres database might have been dropped, and it is dangerous to connect to the template0 database in case you accidentally change it. Since template1 is used as the default template when creating databases, it would most likely exist.

Also note that whatever user you are connecting with needs to have permission to connect to the given database. All three of these databases are owned by the postgres user, which means if you do not connect using this user, you will have to make sure the user you are using has permission to access the database.

William Prigol Lopes · Accepted Answer · 2019-09-26 00:25:00Z

You always need to connect on a database.

If you not inform, the connector tries to connect in a database with the same name of the user that you tried to connect, so, if you try to connect with "admin" user, the postgresql tries to connect on "admin" database.

If postgresql can't find the "admin" database, the connector returns the error mentioned on answer.

All postgresql databases have the "internal tables", wich have the prefix "pg_".

So, if you want a list of databases in postgresql, you can try to run the following select in any database:

warehouse2=# select datname from pg_database;
  datname   
------------
 postgres
 template1
 template0
 warehouse2
(4 rows)

In this example, I listed all databases on my cluster, containing 4 databases.

Hervé Piedvache · Accepted Answer · 2017-10-26 09:42:28Z

0

As you always have a default database called postgres on PostgreSQL you can By default make a connection to it like this :

$dbh = new PDO($this->database.":host=".$this->host.";dbname=postgres",$this->user,$this->password);

Then you will make another one after to the good one.

answered Oct 26, 2017 at 9:42

Hervé Piedvache

7984 silver badges9 bronze badges

Comments

Steve Chambers · Accepted Answer · 2017-10-29 20:04:04Z

0

This answer explains it well:

https://stackoverflow.com/questions/2370525#2411860

You'll need to explicitly point to the one database that is always guaranteed to exist as described above, namely "postgres": $this->connection = new PDO($this->database.":host=".$this->host.";dbname=postgres",$this->user,$this->password);

...or if preferred, set $this->dbname = postgres and then $this->connection = new PDO($this->database.":host=".$this->host.";dbname=".$this->dbname,$this->user,$this->password);

answered Oct 29, 2017 at 20:04

Steve Chambers

39.8k29 gold badges179 silver badges222 bronze badges

Comments

Yannoff · Accepted Answer · 2017-10-31 19:34:41Z

0

For what I've understood from th error message, it seems your DSN is wrong (admin, which is your username and/or password is interpreted as the database name)...

answered Oct 31, 2017 at 19:34

Yannoff

3732 silver badges9 bronze badges

Comments

Felipe Valdes · Accepted Answer · 2017-11-01 03:51:34Z

0

In addition to the current excellent answers, also, Consider creating a db for each unix user (or at least for the "admin" user), so the "psql" command works without parameters and you can conveniently use it for interactive use.

answered Nov 1, 2017 at 3:51

Felipe Valdes

2,2571 gold badge18 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to connect to a Postgres database without specifying a database name in PDO?

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related