Connect to a local PostgreSQL server using peer auth with pgAdmin

Question

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"

Answer 1

Make sure you have the OS user, which you have used to launch PGAdmin3 or PGAdmin4, is in the database. For creating an OS user in the database, you can use following command:

sudo -u postgres psql -c "CREATE USER $(id -nu;" postgres

In HOST name address include the Postgres unix_socket_directories You can use following command to get the unix_socket_directories setting

sudo -u postgres psql -c "SHOW unix_socket_directories;"

Answer 2

You can use the pg_ident.conf file to define a user mapping which will allow the OS user "ubuntumainusername" to connect as the PostgreSQL user "postgres". However, this will require you to change your pg_hba.conf to activate the user mapping.

Your attempts to change your OS user to postgres and run the programs from that OS account would be another option. The failures you show are probably not due to PostgreSQL but due to your remote desktop not working correctly under sudo. Fixing that is not really a DBA issue, you could try maybe the "superuser" forum instead. Does it work if you try to launch some other GUI, like firefox, through sudo?

I would first just su - postgres to get a full shell as the "postgres" OS user, rather than trying the sudo -u ... shortcut. Whatever environment settings you've done to OS user "ubuntumainusername" to get xterm to work would probably have to be repeated to OS user "postgres".