Re: Problem with pg_service.conf - Mailing list pgsql-general

From David G. Johnston
Subject Re: Problem with pg_service.conf
Date
Msg-id CAKFQuwajsr4Jk-FMrZtsPTtqOYydx8+giLxW9W4dizvDUYz0Tg@mail.gmail.com
Whole thread Raw
In response to Re: Problem with pg_service.conf  (Michał Lis <fcs1@poczta.onet.pl>)
List pgsql-general
On Thu, Jul 23, 2020 at 6:12 AM Michał Lis <fcs1@poczta.onet.pl> wrote:
Hello,

The server is located in the lan and runs on Windows 7x64 Ultimate.
On this server I created pg_service.conf file and set the environment variable of
PGSERVICEFILE.

The server software (postgres) doesn't use PGSERVICEFILE, only client software does (psql, pg_dump, etc.).
 
Coping the pg_service.conf file from server to the client and setting environment variable PGSERVICEFILE to this file has no sense, because of possibility of storing login and password in this file.

Replace "copying" (Google incorrectly wants to replace copying with coping...) with "moving"; or just "create a pg_service.conf file on the local machine with identical contents".

I want to connect to the server by service name, because I don't want to store any login information on the client side.

What you are describing is simply not possible.  You can avoid storing credentials on the client if you wish but you need a person to enter them manually each time.  If you want any form of self-authentication by the client then the credentials the client uses must be made available to it somehow.  Searching the internet will surely turn up many such options for this.  But it is nonsense to say that the client need only supply an unencrypted shared secret (i.e., the service name) to the server and be granted access.  That is no better than storing a username and password on the local machine.

At this moment I have the information that the copy of pg_service.conf file is necessary on the client side to establish connection by service name.

PostgreSQL has a client-server architecture.  pg_service.conf is used only by the client.  In your local machine example the client and the server software are on the same machine and so the fact that the pg_service.conf file was only being read by the client was not apparent.  Once you move the client to a machine that lacks a pg_service.conf file the client rightfully complains that it cannot find a service entry that exists on the server.  If you want both the local machine and remote machine client software to be aware of the service name then both machines need their own pg_service.conf file with that service definition.  The client is not continually downloading the pg_service.conf file from the server (if you wanted a shared pg_service.conf file you could do so but you would probably want a separate configuration machine to provide it, not the PostgreSQL server).  And the server doesn't recognized service names supplied by the client - it only recognizes credentials and a database name (and other configuration settings too)

David J.

pgsql-general by date:

Previous
From: Samarendra Sahoo
Date:
Subject: Re: How to enable TDE on Postgresql 12.3 deployed using Kubernetes
Next
From: Bo Peng
Date:
Subject: Re: HA setup with pg pool in docker