HBA stands for Host-base Authentication.
The pg_hba.conf config file controls the authentication method.
By default, local connections to Postgres uses peer authentication. That means that instead of asking you for a password, it checks to see if you are currently logged in as a system user that matches the user name in Postgres.
This is where the pg_hba.conf config file comes in.
We are going to change the way we do local authentication and instead tell Postgres to require an encrypted password. First, we need to actually set a password for the postgres user. To do this we need to open up psql as the user postgres.
# Notice no password
sudo -u postgres psql
A password is not required because there is a local system user named postgres and a Postgres user named postgres.
grep postgres /etc/passwd
postgres:x:113:118:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
Apply a password for user postgres.
ALTER USER postgres WITH ENCRYPTED PASSWORD '12345';
Quit out of Postgres.
\q
Log back in using the now password protected postgres user.
# Notice still no password needed
sudo -u postgres psql
Lets create another user named tommy besides the default postgres user for testing purposes.
Keep in mind that I already have a local system user with the username tommy.
grep tommy /etc/passwd
tommy:x:1002:1003::/home/tommy:/bin/bash
Create Postgres user tommy with access to database tommy.
CREATE USER tommy WITH ENCRYPTED PASSWORD '12345';
CREATE DATABASE tommy;
GRANT ALL PRIVILEGES ON DATABASE tommy to tommy;
\du # View permissions
Quit out of Postgres.
\q
Login to Postgres with user tommy. You will notice that it still does NOT require a password even though one was applied.
# Notice no password
sudo -u tommy psql
psql (9.5.25)
Type "help" for help.
tommy=# \q
Now that we have a password set for both users postgres and tommy, we want to update Postgres to require password authentication when accessing Postres locally. To do this, we need to edit the pg_hba.conf file.
As I said before, Postgres uses peer authentication by default. This means database connections will be granted to local system users that own or have privileges on the database being connected to. Such authentication is useful in cases where a particular system user will be running a local program (e.g. scripts, CGI/FastCGI processes owned by separate users, etc.), but for greater security you may wish to require passwords to access your databases.
To do this, we need to edit the pg_hba.conf file.
Let’s read the pg_hba.conf file first.
cat /etc/postgresql/9.5/main/pg_hba.conf
There are two sections to pay attention to.
Local postrges user security:
Allows user postgres to login locally without a password.
local all postgres peer
Local all users security:
Allows local system users that have Postgres privileges with the same username to login without password.
local all all peer
Let’s update the security settings.
vi /etc/postgresql/9.5/main/pg_hba.conf
Before:
# Database administrative login by Unix domain socket
local all postgres peer
# "local" is for Unix domain socket connections only
local all all peer
After:
Replace peer with md5 on this line to activate password authentication using an MD5 hash. This means that Postgres user postgres and local system users will no longer be able to login without a password.
# Database administrative login by Unix domain socket
local all postgres md5
# "local" is for Unix domain socket connections only
local all all md5
Restart Postgres to apply the changes.
service postgresql restart
Now if we want to connect to Postgres locally, it will require a password.
sudo -u tommy psql
Password:
Quit out of Postgres.
\q
sudo -u postgres psql
Password:
Quit out of Postgres.
\q