Re: BUG #16550: Problem with pg_service.conf

Hello Jeff, David and Christophe,

Thank you for answers and suggestions.

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.

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

2. On the client side:

As you described, the PGSERVICEFILE must be set and leads into existing
file.

But this option is not acceptable of the security, because login and
password must be stored on local machine, in one of ways:
- inside the pg_service.conf
- or in c:\Users\<user_name>\AppData\Roaming\postgresql\pgpass.conf file.

Both of them are plain text file.

I was hope the storing only information of host, port, dbname (and user)
in pg_service.conf file (on client) and
login credentials in
c:\Users\<user_name>\AppData\Roaming\postgresql\pgpass.conf file on the
server
would be enough to establish connection, but it didn't work either.

3. Jeff told about 'ident' mode defined in pg_hba.conf, but this kind of
connections is based on windows user login name. As I understand, this
type of login needn't password. Is it possible to use username from
pg_service.conf file instead windows user name ?

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

Regards
Michal

W dniu 2020-07-23 o 00:53, Jeff Janes pisze:
> On Wed, Jul 22, 2020 at 1:02 PM Michał Lis <fcs1(at)poczta(dot)onet(dot)pl
> <mailto:fcs1(at)poczta(dot)onet(dot)pl>> wrote:
>
> Hello,
>
> No, the file is only on the server side.
>
> I expected the client will ask the server using the service name.
>
>
> How would it know what server to ask?  The hostname and port of the
> server are contained in the file, which it doesn't have.
>
> If the service will be found on the server, the server should
> accept the connection from the client.
>
> In pg_service.conf file can be stored user name and password.
>
> I want to use login of service type, because I won't to store any
> login information (ie password) on the client side.
>
>
> There isn't much point in using a password, if the server doesn't
> require the client to prove knowledge of it.  You might as well just
> use 'ident'.
> Cheers,
>
> Jeff