trust_postgres.md

PostgreSQL 'trust' Authentication Method Guide

When connecting to your database client to a database, you might get the following error:

connection error error: password authentication failed for user <user_name>

You can solve it by specifying user and password in the connection configuration or by changing how clients are authenticated. This guide explains how to solve the issue with the second approach.

Step 1 - Locate the file pg_hba.conf

Open PostgreSQL interactive terminal:

$ psql

Locate the directory of the file pg_hba.conf

user_name=# show hba_file;

The directory of that file should be returned and should look similar to this:

/etc/postgresql/12/main/pg_hba.conf

If you encounter the following error:

ERROR:  must be superuser or a member of pg_read_all_settings to examine "hba_file"

continue with step #2, otherwise go to step #3.

Step 2 - Add attribute SUPERUSER to your user/role

Quit PostgreSQL terminal with \q and run the following command on ubuntu terminal:

$ sudo -u postgres psql -c "ALTER ROLE <user_name> WITH SUPERUSER"

It should return ALTER ROLE and now if you run \du in psql terminal you will see that the attribute Superuser has been added to your user:

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

Repeat Step #1

Step 3 - Modify authentication methods in pg_hba.config

Quit PostgreSQL terminal with \q and run the following command on ubuntu terminal:

$ sudo nano directory_from_step_2/pg_hba.conf

For example:

$ sudo nano /etc/postgresql/12/main/pg_hba.conf

You should be able to see the content of the file in your terminal. (I have tried to open it with vscode but haven't been able to do so.)

At the very end of the file (you probably have to use arrow keys to move up and down the file) there are some rules already predifined. Example:

# Database administrative login by Unix domain socket
local   all             all                                     trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Pay attention to the columns METHOD and USERS. In that example I have already changed all the values in USER to all and all the values in METHOD to trust. In the column USER you can specify the user you want to apply the rule to. With all the rule will be applied to all users. In the column METHOD you can find different authentication methods like trust, md5, password, peer, etc. Read the content of pg_hba.conf for more info.

Exit the file with ctrl + x (see options available at the bottom of the terminal window). Save modified buffer with y and then you will see the following message right on top of those options:

File Name to Write: directory_from_step_2/pg_hba.conf

Press enter to save and restart your PostgreSQL server with:

sudo service postgresql restart