Quick pg_hba.conf configuration for new user authentication

We have edit the pg_hba configuration for local host to allow the local user connection for the new user.

To allow pgtst_usr we created to always use password when connecting locally on the server, we need to enforce md5 in the method column pg_hba.conf. But if you change this for all users, it impacts postgres user as well.

So we need to make 2 entries 1 for postgres user and other for the non-default user pgtst_usr as like below...

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             pgtst_usr                               md5 <<< pgtst_usr forced to use password authentication
local   all             postgres                                peer <<< postgres user will use peer authentication, which is if os user postgres is what is used to connect to the ssh terminal, then user is allowed to authenticate
# IPv4 local connections:

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres=#

Now postgres user is able to authenticate without password (peer method):

-bash-4.2$ psql
psql (15.0)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \q

Now pgtst_usr is able to authenticate using password:

-bash-4.2$ psql -U pgtst_usr -d postgres
Password for user pgtst_usr:
psql (15.0)
Type "help" for help.
postgres=> \conninfo
You are connected to database "postgres" as user "pgtst_usr" via socket in "/var/run/postgresql" at port "5432".
postgres=> \q
-bash-4.2$

Thanks