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.
Enter the pg_hba.conf file.
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 pg_hba.conf?
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:
1. sudo -u postgres
2. psql ...
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
yourname@yourcomputername:~$
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
postgres@yourcomputername:~$
Note the different username. Anything you run now will be ran as system-level user postgres. Type ctrl+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 postgres, 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.
Since user 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
Understanding 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 postgres.
Sound familiar? Let's look at the next un-commented line:
local all all peer
This 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 and trust.
As expected, password will let you log in with a password and trust will just let you log in 'unconditionally'.
Warning: 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.
Second warning: 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.
Within pg_hba.conf, change
local all all peer
to
local all all trust
and save.
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.