I have a PostgreSQL server running on a remote machine, but I'm connected locally through VPN.
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):
$ sudo -u postgres pgAdmin3 Client is not authorized to connect to Server07:44:11: Erorr: Unable to initialize GTK+, is DISPLAY set properly?
$ 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
$ 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.
How can I log to the local PostgreSQL server which is based on a peer authentication using pgAdmin3 or pgAdmin4?
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  postgres@postgres LOG: provided user name (postgres) and authenticated user name (ubuntumainusername) do not match 2020-04-16 16:34:55.548 CEST  postgres@postgres FATAL: Peer authentication failed for user "postgres" 2020-04-16 16:34:55.548 CEST  postgres@postgres DETAIL: Connection matched pg_hba.conf line 85: "local all postgres peer"