Обсуждение: Problem with pg_service.conf
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: <empty> - Password: <empty> 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
On 7/21/20 4: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 Are you sure that PGSERVICEFILE is being defined correctly? > > 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: <empty> > - Password: <empty> > > 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 > > > > > -- Angular momentum makes the world go 'round.
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: <empty> > - Password: <empty> > > 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 > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
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
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: <empty>
- Password: <empty>
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
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.