CODEWITHSUNDEEP
postgresqldebian
Richard
Richard

Reputation: 65520

Postgres: cannot connect to database after creating it?

I'm trying to create a new Postgres database and superuser, on Debian 7.

However, I don't seem to be able to connect to my new database once I've created it.

This is what I've done:

$ sudo su - postgres
postgres@:~$ createdb prescribing
postgres@$ psql
postgres=# create user prescribing with password 'mypassword';
CREATE ROLE
postgres=#  GRANT ALL PRIVILEGES ON DATABASE prescribing to prescribing;
GRANT
postgres=# exit\q
could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory
postgres@:~$ psql -d prescribing -U prescribing
psql: FATAL:  Peer authentication failed for user "prescribing"

What have I done wrong?

I'm not sure whether the error is to do with the lack of a history file, or whether it's some interaction between Unix user and database user that I don't fully understand.

I also tried psql -U prescribing -d prescribing -W, to see if I could get it to ask me for a password, but it didn't help.

Update: Here's the content of pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Upvotes: 1

Views: 1211

Answers (1)

harmic
harmic

Reputation: 30577

Using 'peer' authentication, the unix user has to match the database user. You are trying to connect as the 'prescribing' user but logged in as the linux 'postgres' user. It does not work due to this mismatch.

The 'peer' authentication is selected because you are connecting locally (via unix sockets), and your pg_hba.conf specifies using 'peer' for that. If you change the 'local' entry to md5, then you will be able to use password authentication. Alternatively, you can connect via TCP/IP address 127.0.0.1 instead, eg:

psql -h 127.0.0.1 -U prescribing -d prescribing

Upvotes: 2

Related Questions