So. Postgres user handling is Bloody Fucking Weird, and very VERY archaic in some places.
To begin with, Postgres users are not the same users as your computer. Except when you use
peer authentication, which is default for "local" connections (in-machine).
peer authentication, you need to have the same username inside the database as outside. So
root cannot connect as
postgres into the database.
Postgres also supports other kinds of authentication, for example
trust, which basically says
yessir, you're ok no matter who you say you are (not very good).
It also supports the ancient and untrustworthy
ident protocol, that noone in their sane mind would ever use, and which was designed in the 80s/90s.
Then comes the slightly more modern authentications,
md5 is basically a username & password combination for your database, where both the username and password are stored in the database system. Then it no longer matters who you are connecting as on the computer ( be it
This is the sane method, and you should use it.
By default, postgres might use it if you give it a hostname of
Then comes the
pg_hba.conf file, which is an amazing hairy construct. It's basically a firewall file for your database system. It tells you which users & databases are allowed to connect from which systems, including your local machine.
ident wasn't stupid enough, they re-envisoned
ident as a config file, allowing you to override the username and other combinations in a file on your machine. Don't use it. Really don't.
What you want to do is this:
peer authentication for anything but the postgres user.
- Create a new admin user (
createuser) with password.
- Add your new admin user to pg_hba.conf like this:
local all mysuperuser md5