Postgres: cannot connect to database after creating it?

Question

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

Could you show us your settings of pg_hba.conf? Looks like you block unknown database users. — Frank Heikens
– Frank Heikens, Commented Mar 12, 2015 at 10:56

harmic · Accepted Answer · 2015-03-12 11:20:30Z

2

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

answered Mar 12, 2015 at 11:20

harmic

31k6 gold badges75 silver badges102 bronze badges

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

4 Comments

Frank Heikens Over a year ago

Check the manual for things like this: postgresql.org/docs/current/interactive/auth-pg-hba-conf.html If you change "peer" to "md5", you can connect the way you did, just type the password and you're in.

Richard Over a year ago

There are actually two lines beginning with 'local', and I'm not sure which I shoudl change to md5. Should I change the first (user postgres) or the second (user all)?

krzysiek.ste Over a year ago

If you want connect to database directly from any system user, the local record in pg_hba.conf file should look like this: local all all md5 Change only user all. Remember to restart postgresql service after that change.

Frank Heikens Over a year ago

@krzysiek.ste: reload is good enough, you don't need a restart. You could even execute pg_reload_conf() as a database superuser: SELECT pg_reload_conf();

Collectives™ on Stack Overflow

Postgres: cannot connect to database after creating it?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related