How can I get psql to work in ubuntu from shell: $ psql -U admin -d mydb

Question

Apparently this is a pretty hard question to ask because, simple as it is, no one seems to have answered it.

I have PostgreSQL running on ubuntu 12.10 server (no GUI).

My goal - my question - is to create a database named "mydb" and a user "admin" such that I can give this command as an ordinary user from the shell and connect to the database:

$ psql -U admin -d mydb

But I cannot find the answer to this anywhere.

I can log in as user postgres by su'ing and running the psql command:

$ sudo su -m postgres
postgres@baseubu1210dev:~$ psql
psql (9.1.7)
Type "help" for help.

postgres=# 

I have figured out how to create a database named "mydb" and a user "admin" such that:

postgres=# \du
 Role name |                 List of roles Attributes       | Member of 
-----------+------------------------------------------------
 admin     | Superuser, Create role, Create DB              | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

I have not figured out how to make sure that user "admin" can connect to the "mydb" database - perhaps someone with such privileges doesn't need to? [As I write, I notice that I should probably revoke Superuser privileges from admin.]

And what I really want to know is how to connect from a regular user's shell:

$ psql -U postgres
psql: FATAL:  Peer authentication failed for user "postgres"

There are surely unwritten assumptions about running postgreSQL that I'm misunderstanding, but a couple of hours of searching, looking at tutorials, etc., has not solved the problem. I'll be perfectly happy if someone says this has already been answered, especially if it has. I assume and hope that the answer is simple and appreciate your help.

Thanks, ge

Answer 1

From the error-message, it seems that the authentication-settings have not been properly set.

Besides the settings in the database itself, PostgreSQL also uses a configuration file (pg_hba.conf) that specifies what authentication-mechanisms can be used per user, host and database. You can specify that users can only connect via password/md5 password or even 'no authentication required'

Currently your configuration probably uses 'Peer' authentication for all users, which simply means: Only allow that user if it is the currently logged-in user (e.g. your shell-user should also be called 'admin'). From your question, this is not what you want, you'll probably want to use password or md5 authentication mechanism.

I think these pages will give you the answer you need:

http://web.archive.org/web/20131001194046/http://blog.deliciousrobots.com/2011/12/13/get-postgres-working-on-ubuntu-or-linux-mint/ (archived version)

Official documentation:

http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Remember to reload postgresql after modifying the pg_hba.conf file!

update

The original blog-post I referred to is now offline, the link now points to an archived version. Thanks to @O_the_Del for reporting.

Answer 2

On Debian, PostgreSQL listens by default on localhost, so if you have a rule in pg_hba.conf which allows TCP/IP connections then you can use the -h parameter to match that rule:

psql -U admin -h localhost mydb