If you're new to Postgres, there is a lurking 'gotcha' you'll probably encounter when configuring your first database - it's within the default settings of host based authentication config.
You start by installing Postgres with just a few commands and some time. You follow some online instructions letter-for-letter to create a new user and database. Everything's looking good. Then you try to do some work on the database by uploading a schema and/or uploading some data (e.g., command
psql -U legiscan_api -f schema-pgsql.sql from Legiscan's API Client setup). Suddenly it doesn't let you log into the db. You see
psql: FATAL: Peer authentication failed for user "some_username"
You log back into the database as user
postgres and ask it what users exist. There, sitting in table
pg_user is your newly made made user. So, authentication is failing, and the user exists, so clearly there is something wrong with the password. You try approximately 983 ways to change, remove, add, and use a password and still can't get that authentication error to go away.
Host Based Authentication
If you're encountering this scenario, the difficulty isn't in the user you set up or the command arguments you're using to talk to Postgres. It's probably within what types of access Postgres is allowed to accept.
The filename stands for "postgres host based authentication configuration". [Docs]. It lets you configure what types of access are permissible, dependent on where the access is coming from.
Let's start with looking at the config file of interest.
Where is my
There are several places it could be, dependent on where you have it installed. The safest bet is to ask Postgres itself which config file it's using.
Following this answer on Stack Exchange, find the location of the relevant hba config file using:
sudo -u postgres psql -t -P format=unaligned -c 'show hba_file';
Think of this command as two parts:
sudo -u postgres
Part 1 of Command
The first part changes your system-level user to
postgres (assuming you already made user
postgres when installing Postgres) for part 2 of the command, and not further.
To dig into the idea of changing users a bit: when you open a new terminal, your prompt will look something like
Run the command
sudo su - postgres. This commend, however, will keep you logged in as the specified user. Thus, the prompt will change to something like
Note the different username. Anything you run now will be ran as system-level user
d to disconnect from the current session and return to your default user.
Part 2 of Command
The second part is asking
psql to run the query
show hba_file with some added flourish. Now here's the part that matters:
psql, unless you tell it different through passing arguments, will attempt to log into Postgres and run your command as whatever user you're logged into the system as. Since you did the first part telling the system to temporarily become user
psql will both see you want to be user
postgres within Postgres and trust your system to have already verified your identity.
This is called
peer based authentication.
postgres is the default superuser of Postgres, you should be able to run any query you want and have it work.
This is also why it's so important to keep as many constraints as possible on who can log in as
postgres, and really any other user you may have set up.
That command will give you the location of a
pg_hba.conf. For me, it is
/etc/postgresql/9.5/main/pg_hba.conf. Open the file it specifies an a way that will let you modify it. Note you'll need to open it with superuser access on your system. I used vim through the command:
sudo vi /etc/postgresql/9.5/main/pg_hba.conf
I'm immediately looking to run a local Postgres database for some data exploration and, since I'm assuming you're a first-timer to Postgres, I'll assume you're starting with a local connection too.
The first non-comment line in the config for me is
local all postgres peer
This line tells Postgres that, when connecting through local connections to user
postgres, only accept
peer based authentication. It's the thing above where you have to be the sytem-level user logging into Postgres for it to trust you to be that user. That is, while you are any user other than
postgres, no amount of password configurations can get access to log in as
Sound familiar? Let's look at the next un-commented line:
local all all peer
peer authentication method on
local connection is how it was configured out-of-the-box. It's saying that all local connections (excepting user
postgres, which follows whatever rule is already set above) can only connect through what its calling
peer. So, by default, all users you create will have the same
peer-only auth method associated with it. This is what you need to change (Unless you want to make a system-user to match your database user and actually use peer-based authentication. Which is totally fine. But far too much work for me wanting to just hack on some open data locally.)
Selecting A Different Authentication Method
The other authentication methods of immediate interest are
password will let you log in with a password and
trust will just let you log in 'unconditionally'.
password will send the raw-text password to Postgres. Only use this if you trust the network you're using (which includes if you trust your own computer and you're working locally). Else, check out any of the other
auth-methods in the docs.
trust's 'unconditional' really means unconditional. Just telling postgres you are a given user is enough to have postgres believe you.
Making The Change
Hokay. This whole post comes down to you having to make a single change. Let's say you have some combination of really trusting your own computer and have no problems if someone else can unfaithfully steal the data in your database, and thus choose the
trust auth method.
local all all peer
local all all trust
Restart Postgres Server
Be sure to restart your Postgres server after doing the above! Else the config changes won't be noticed and nothing will be updated. Given how I installed it on Ubuntu, I did this with
sudo service postgresql restart
Voila! You should be able to continue with whatever brought you here in the first place. At the very least, you've started wrapping your mind around the Postgres permissions architecture.