Context
I have a PostgreSQL server running on a remote machine, but I'm connected locally through VPN.
Its pg_hba.conf
file is accepting peer auth for local connections;
local all postgres peer
I don't like to change this.
If I follow what is written here: https://documentation.help/pgAdmin3/connect.html ;
The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.
I got this error on pgAdmin3:
I even tried not to leave the Host
field blank but to write the name of the socket file in there: /var/run/postgresql
-> the behaviour is the same.
And if I insert postgresql://
instead, I get;
An error has occurred:
07:55:28: Error: Error connecting to the server: could not translate host name
"postgresql://" to address: Name or service not known
And pgAdmin4 doesn't let me have the Host
field blank:
Until there, I was logged in as the normal Ubuntu user.
So I decided to try while being postgres
Ubuntu user, just like when I want to run a pg_* command using peer auth (e.g. $ sudo -u postgres pg_* -U postgres bla bla
):
pgAdmin3:
$ sudo -u postgres pgAdmin3
Client is not authorized to connect to Server07:44:11: Erorr:
Unable to initialize GTK+, is DISPLAY set properly?
pgAdmin4:
$ sudo -u postgres pgAdmin4
QStandardPaths: XDG_RUNTINE_DIR not set, defaulting to '/tmp/runtime-postgres'
Client is not authorized to connect to Serverqt.qpa.screen: QXcbConnection:
Could not connect to display :1.0
Could not connect to any X display.
Next I decided to check this out on my local machine in order to by-pass these display related errors
pgAdmin3:
$ sudo -u postgres pgadmin3
No protocol specified
07:49:51: Error: Unable to initialize GTK+, is DISPLAY set properly?
I do not have pgAdmin4 on my local machine, but I guess I will encounter some same kind of error.
Question
How can I log to the local PostgreSQL server which is based on a peer authentication using pgAdmin3 or pgAdmin4?
Update:
These three new lines were added to the log file in /var/log/postgres/postgresql-12-main.log
after the unsuccessful connection using pgAdmin3:
2020-04-16 16:34:55.548 CEST [17842] postgres@postgres LOG: provided user name (postgres) and authenticated user name (ubuntumainusername) do not match
2020-04-16 16:34:55.548 CEST [17842] postgres@postgres FATAL: Peer authentication failed for user "postgres"
2020-04-16 16:34:55.548 CEST [17842] postgres@postgres DETAIL: Connection matched pg_hba.conf line 85: "local all postgres peer"