Re: BUG #16550: Problem with pg_service.conf

On Thu, Jul 23, 2020 at 7:26 AM Michał Lis <fcs1(at)poczta(dot)onet(dot)pl> wrote:

> Hello Jeff, David and Christophe,
>
> Thank you for answers and suggestions.
>

As this isn't a bug my primary reply was sent to the message on -general.

>
> 1. On the server side:
>
> The first thing I made before report this problem was reading the
> documentation in which is written:
>
> The connection service file can be:
> - a per-user service file at ~/.pg_service.conf
> - or the location specified by the environment variable PGSERVICEFILE
> - or it can be a system-wide file at `pg_config
> --sysconfdir`/pg_service.conf
> * - or in the directory specified by the environment variable
> PGSYSCONFDIR.*
>
> The last point talks the service file could placed into PGSYSCONFDIR.
> Using PostrgeSQL on Windows the PGSYSCONFDIR leads into D:\PostgreSQLx86\9.6\etc
> folder as a result of PG_config.exe.
> This folder normally is absent. I created it manually and then copy into
> it the pg_service.conf file.
>
>

> So the bug is that the pg_service.conf file is not read from this location.
>

You haven't adequately demonstrated this supposed bug (I'll admit I haven't
tried to either). But given that you have been operating under the
misconception that the server software (postgres) should be reading this
file I'm disinclined to accept this presently.

>
> Finally I had to set the system variable PGSERVICEFILE =
> D:\PostgreSQLx86\9.6\etc\pg_service.conf
>
> After that I was been able to connect to PostgreSQL server by defined
> service name from machine where PosrgreSQL was installed.
>
> Using PGAdmin 3 which comes with PostgreSQL
>

You used a third-party package that installed both pgAdmin3 and PostgreSQL
for you - pgAdmin is not part of the core PostgreSQL project.

I have to set the host IP or name. Without that the OK button on the
> connection form is inactive.
> (PGAdmin 4 doesn't need the IP/host name, what is proper).
>
> So it seems to be a bug in PGAdmin 3, which require host IP/name to use
> service connection.
>

pgAdmin3 is discontinued so don't expect a bug fix.

2. On the client side:
>

See the -general post but in short your expectations are simply wrong and
need to be forgotten and replaced with what actually happens.

> I need it all to hide connection properties in QGIS and make QGIS project
> independent from servers used in different places.
>

That isn't a sufficient level of detail for someone else to describe a
solution (if you provide more please start a new thread on -general).
pg_service.conf/.pgpass are both convenience facilities for the client.
You can do nothing more than what you could accomplish with a properly
written command or setting the appropriate environment variables on the
client machine. pg_hba.conf provides server controls, especially when
combined with network access control (e.g., VPNs, firewalls, and routing
rules). Usually both are used in conjunction to provide security in depth.
David J.