Re: Problem with pg_service.conf

On Thu, Jul 23, 2020 at 6:12 AM Michał Lis <fcs1(at)poczta(dot)onet(dot)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.