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:

pgAdminIII error

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:

pgAdmin4 bug

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"

• jjanes: "but I'm connected locally through VPN." What does this mean? The N in VPN stands for "network". How are you setting up your VPN to be connected locally, i.e. without using the network?
• jjanes: Your first image says that peer authentication failed. Look in the database servers log file to get the details of that failure.
• s.k: I'm working on the remote server through a VPN, with the help of a remote desktop because the server has a graphical user interface. Hence, the commands I insert in the console on the server are 'local' to the machine. I checked for the logs, see update.

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;"

• s.k: I'll try this out as well, it seems you are on a right track from what is written in the log file.

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".