psql -d {databasename} without specifying user or hostname

Question

I have two Linux systems with Postgresql:

1. Ubuntu 12.04 with Postgresql 9.1
2. Ubuntu 14.04 with Postgresql 9.3

On both, I have created the same users and databases (with the same owners). They also have the same .pgpass file and more or less the same pg_hba.conf files. Both pg_hba.conf files contain:

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

I should also add that PGHOST is specified on neither machine.

However, whereas on (1) I can run the following directly:

psql -d myDB

on (2) I need to specify the username and hostname:

psql -d myDB -U myDBuser -h localhost

or I get the following error:

psql: FATAL:  role "ubuntu" does not exist

Why is this the case, and how can I make (2) like (1) (to save a bit of typing)?

Answer 1

Why's it happening?

psql (and other libpq apps) default to connecting:

• Over unix sockets, if available
• As the current operating system user
• To a database of the same name as the default username

Just set the client defaults to use tcp/ip

You can create a .pgservice file that lets you specify a set of connection options, e.g.

[mydb]
host=localhost
user=myDBUser
dbname=mydb

Then just run

psql service=mydb

You can also make a shell alias, like

alias mypsql='psql service=myservice'

or without the service file:

alias mypsql='psql "dbname=myDB user=myDBuser host=localhost"

which allows you to just run mypsql instead.

Or configure username mapping/password auth for unix sockets

The underlying cause of your original error is probably that you've configured peer authentication for local connections (or that was the default). So if the username you're connecting as isn't the same as the local unix user, you'll get an error saying:

FATAL:  Peer authentication failed for user "myunixusername"

which is by the sounds of things, the user ubuntu.

If you wish to permit connections over unix sockets so you don't have to specify -h localhost, you should use md5 password authentication for local connections in pg_hba.conf, or add a pg_ident.conf entry mapping the OS user ubuntu to the postgres user myDBuser so it's allowed to log in.

So either prepend:

local   myDB             myDBuser                                md5

before other entries in pg_hba.conf (to use password auth) or prepend:

local   all             postgres                                peer map=peermap

and in pg_ident.conf add:

peermap    ubuntu     myDBuser

to instead use peer auth (so no password, just secure username matching) but allow OS user ubuntu to connect as db user myDBuser.

local connections using unix domain sockets are slightly faster, but there's otherwise no real advantage over TCP/IP.

Default database

There's no shortcut for setting a default database for a username. So you'll still need to use a pgservice file or shell alias for that.