PostgreSQL: FATAL: Peer authentication failed for user “name”

By | 09/11/2019
 

A quick note mainly for myself on how to enable remote access in PostgreSQL and create its database dump.

An EC2-server moved to our AWS account with one website.

During an attempt to connect to the PostgreSQL running on this host – the “FATAL: Peer authentication failed for user “name”” appears.

And the same error if connect via localhost or by remote login.

PostgreSQL remote login enable

Usually, this error happens when PostgreSQL has no md5 Auth type configured for connections.

To enable the remote login – edit the /etc/postgresql/11/main/pg_hba.conf config and set values by:

type database-name username-name remote-address auth-type

In this current case it will look like:

...
host dbname username 188.***.***.94/32 md5
...

Where 188.***.***.94 is my home IP.

Applying PostgreSQL config changes with no reboot

The server is absolutely new, just came to us, I’m doing all of this on Friday evening and have no wish to reboot it now, but need to as I did changes in its /etc/postgresql/11/main/pg_hba.conf.

To apply those changes and make no reboot – switch to the postgres user:

root@ip-172-31-9-189:~# su - postgres
postgres@ip-172-31-9-189:~$

And run pg_ctl reload:

postgres@ip-172-31-9-189:~$ /usr/lib/postgresql/11/bin/pg_ctl reload -D /var/lib/postgresql/11/main
server signaled

Check process, that PostgreSQL is still running:

postgres@ip-172-31-9-189:~$ ps aux | grep post
postgres 10524  0.0  1.1 320924 24304 ?        S    Jul18   2:00 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
postgres 10526  0.0  1.4 321108 28588 ?        Ss   Jul18   0:02 postgres: 11/main: checkpointer
postgres 10527  0.0  0.5 320980 10936 ?        Ss   Jul18   0:36 postgres: 11/main: background writer
postgres 10528  0.0  0.4 320956  9496 ?        Ss   Jul18   0:43 postgres: 11/main: walwriter
postgres 10529  0.0  0.3 321416  7620 ?        Ss   Jul18   0:50 postgres: 11/main: autovacuum launcher
postgres 10530  0.0  0.2 176332  5820 ?        Ss   Jul18   1:10 postgres: 11/main: stats collector
postgres 10531  0.0  0.3 321264  6632 ?        Ss   Jul18   0:01 postgres: 11/main: logical replication launcher

And connect from the home IP:

[setevoy@setevoy-arch-pc ~] $ psql -h 18.***.***.27 -p 6666 -U username dbname
Password for user username:
...
dbname=>

All good so far.

Create a PostgreSQL database dump

And the last thing to create a database dump.

Use the pg_dump here:

[setevoy@setevoy-arch-pc ~] $ pg_dump -h 18.***.***.27 -p 6666 -U username dbname > dbname.pgsql
Password:

Check contents:

[setevoy@setevoy-arch-pc ~] $ head dbname.pgsql
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.3 (Ubuntu 11.3-1.pgdg18.04+1)
-- Dumped by pg_dump version 11.5
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
...

Done.