Unable to connect via password authentication to postgresql on amazon linux

Question

I am using this reference to install postgres-12 on amazon linux: https://techviewleo.com/install-postgresql-12-on-amazon-linux/

The core of it is:

sudo tee /etc/yum.repos.d/pgdg.repo<<EOF
[pgdg12]
name=PostgreSQL 12 for RHEL/CentOS 7 - x86_64
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64
enabled=1
gpgcheck=0
EOF

sudo yum makecache

sudo yum install postgresql12 postgresql12-server

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

sudo systemctl enable --now postgresql-12

systemctl status postgresql-12

This installation does work: postgresql is up. But it only allows local connection via the postgres user. I have been trying to get md5 [password] auth running and it is not working.

]$ psql -U pubkey
psql: error: could not connect to server: FATAL:  Peer authentication failed for user "pubkey"

Here is the pg_hba.conf: notice it does have

  local  all      all          md5

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

I have restarted postgres after adding that md5 line:

sudo systemctl stop  postgresql-12
sudo systemctl start  postgresql-12

But password auth is still not working. What am I missing?

Adrian Klaver · Accepted Answer · 2020-08-20 16:21:40Z

1

Did you do this part of tutorial?:

-bash-4.2$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE

If so to use password(md5) authentication you will need to do:

psql -d postgres -U postgres -h localhost

Without -h you are connecting via socket(local in pg_hba.conf) and using peer auth.

I would also comment out/remove/or move to bottom of IPv4 local connections :

host all all 127.0.0.1/32 ident

In pg_hba.conf first match wins, so if you are connecting via localhost(host in pg_hba.conf) and ipv4 it will be looking for ident auth.

It is all explained here:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

answered Aug 20, 2020 at 16:21

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to connect via password authentication to postgresql on amazon linux

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related