Problem with pg_service.conf

2020-07-22 Thread Michał Lis

Hello,

I can't connect to a database via service defined in pg_service.conf 
file from remote machine.


Connection from local machine using this service definition works fine.

I tested it on PostgreSQL 9.4 and 9.6. The other problem is with the 
localization of the pg_service.conf file.


Reading internet solutions, I found, that this file may be placed in 
global configuration folder.


In my case it is D:\PostgreSQLx86\9.6\etc (the SYSCONFDIR variable 
displayed by pg_config.exe).


Placing the file into this folder does not work. I had to define the 
global system variable PGSERVICEFILE = 
D:\PostgreSQLx86\9.6\etc\pg_service.conf.


After that the connection to database via service began work, but only 
on local machine.


To reproduce problem:

1. Create pg_service.conf file like this:

[test4]
host=192.168.1.2
port=5433
dbname=test
user=postgres
password=abcd

2. Save this file in a directory, in my case it was
D:\PostgreSQLx86\9.6\etc\pg_service.conf
(line ends in Linux style by NotePad++)

3. Set the global system variable:
PGSERVICEFILE =D:\PostgreSQLx86\9.6\etc\pg_service.conf

4. Restart computer to take effects for setting PGSERVICEFILE

5. Open PGAdmin 3 or 4 no matter which.

6. Add and Set database connection
- Name: test
- Host: 192.168.1.2
- Port: 0
- Service: test4
- User: 
- Password: 

On the local machine the connection will be established but on remote 
machine the error:


"Definition of service "test4" not found"

will be raised

Connection from remote machine to server by host, port, user and 
password works fine.


PostgreSQL version: 9.6.11 and 9.4
Operating system: Windows 7 x64 Pro/Ultimate

Regards
Michal







Re: Problem with pg_service.conf

2020-07-23 Thread Michał Lis

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 client is an other computer with Windows 7x64 Pro. It has no access 
to local folders on the server (especially to the PostgreSQL folder) .


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.


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


Yesterday I created a topic:

BUG #16550: Problem with pg_service.conf

on pgsql-bugs list

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.


I my opinion it has no sense, and looks like a bug.

Regards
Michal



W dniu 2020-07-22 o 18:14, Adrian Klaver pisze:

On 7/21/20 2:35 PM, Michał Lis wrote:

Hello,

I can't connect to a database via service defined in pg_service.conf 
file from remote machine.


Connection from local machine using this service definition works fine.

I tested it on PostgreSQL 9.4 and 9.6. The other problem is with the 
localization of the pg_service.conf file.


Reading internet solutions, I found, that this file may be placed in 
global configuration folder.


In my case it is D:\PostgreSQLx86\9.6\etc (the SYSCONFDIR variable 
displayed by pg_config.exe).


Placing the file into this folder does not work. I had to define the 
global system variable PGSERVICEFILE = 
D:\PostgreSQLx86\9.6\etc\pg_service.conf.


After that the connection to database via service began work, but 
only on local machine.


To reproduce problem:

1. Create pg_service.conf file like this:

[test4]
host=192.168.1.2
port=5433
dbname=test
user=postgres
password=abcd

2. Save this file in a directory, in my case it was
D:\PostgreSQLx86\9.6\etc\pg_service.conf
(line ends in Linux style by NotePad++)

3. Set the global system variable:
PGSERVICEFILE =D:\PostgreSQLx86\9.6\etc\pg_service.conf

4. Restart computer to take effects for setting PGSERVICEFILE

5. Open PGAdmin 3 or 4 no matter which.

6. Add and Set database connection
- Name: test
- Host: 192.168.1.2
- Port: 0
- Service: test4
- User: 
- Password: 

On the local machine the connection will be established but on remote 
machine the error:


"Definition of service "test4" not found"

will be raised

Connection from remote machine to server by host, port, user and 
password works fine.


Where is the server located?

Are you on remote machine when you try to use the service file?

If so, that is not going to work as the remote machine will not have 
access to the local environment.





PostgreSQL version: 9.6.11 and 9.4
Operating system: Windows 7 x64 Pro/Ultimate

Regards
Michal