Обсуждение: BUG #16550: Problem with pg_service.conf
The following bug has been logged on the website: Bug reference: 16550 Logged by: Michal L Email address: fcs1@poczta.onet.pl PostgreSQL version: 9.6.11 Operating system: Windows 7 x64 Pro/Ultimate Description: 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:\PostgrSQLx86\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:\PostgrSQLx86\9.6\etc\pg_service.conf. After that the connection to database via service began work, but only on local machine. Regards Michal
On Tue, Jul 21, 2020 at 10:16 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16550
Logged by: Michal L
Email address: fcs1@poczta.onet.pl
PostgreSQL version: 9.6.11
Operating system: Windows 7 x64 Pro/Ultimate
Description:
I can't connect to a database via service defined in pg_service.conf file
from remote machine.
This is unlikely to be a bug.
The error message is necessary for others to provide assistance. Sending an email with that, and possibly more, detail and asking for help to the -general list would be an appropriate next step.
Demonstrating that such a connection can be made with a complete psql command line invocation would help.
Connection from local machine using this service definition works fine.
Which is odd but further lessens the likelihood that this is a bug.
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:\PostgrSQLx86\9.6\etc (the
SYSCONFDIR variable displayed by pg_config.exe).
Placing the file into this folder does not work.
The absence of an "e" in PostgrSQL seems suspicious...
I had to define the global system variable PGSERVICEFILE =
D:\PostgrSQLx86\9.6\etc\pg_service.conf.
After that the connection to database via service began work, but only on
local machine.
...though the same typo is here so maybe it's fine. I'm still inclined to believe that either a typo exists, or maybe a user permission issue, before thinking it was a bug.
Doubt it matters but you should also include the full 9.6.x version on the email to -general. If that discussion turns up a demonstrable bug then this report should be amended to include its details.
David J.
Hello,
Thank you for so quick response.
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
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.
Regards
Michal
Thank you for so quick response.
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
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.
Regards
Michal
W dniu 2020-07-21 o 18:20, PG Bug reporting form pisze:
The following bug has been logged on the website: Bug reference: 16550 Logged by: Michal L Email address: fcs1@poczta.onet.pl PostgreSQL version: 9.6.11 Operating system: Windows 7 x64 Pro/Ultimate Description: 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:\PostgrSQLx86\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:\PostgrSQLx86\9.6\etc\pg_service.conf. After that the connection to database via service began work, but only on local machine. Regards Michal
Вложения
On Wed, Jul 22, 2020 at 4:05 AM Michał Lis <fcs1@poczta.onet.pl> wrote:
On the local machine the connection will be established but on remote machine the error:
definition of service "test4" not found
The service file is read by the client, not the server. So the remote machine will use the service file local to it. Did you copy your pg_service.conf to the proper directory on the remote machine?
Cheers,
Jeff
Hello,
No, the file is only on the server side.
I expected the client will ask the server using the service name.
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.
Copping the pg_service.conf to the client and setting the system variable to this file, rather has no sense.
Regards
Michal
No, the file is only on the server side.
I expected the client will ask the server using the service name.
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.
Copping the pg_service.conf to the client and setting the system variable to this file, rather has no sense.
Regards
Michal
W dniu 2020-07-22 o 17:29, Jeff Janes pisze:
On Wed, Jul 22, 2020 at 4:05 AM Michał Lis <fcs1@poczta.onet.pl> wrote:On the local machine the connection will be established but on remote machine the error:
definition of service "test4" not foundThe service file is read by the client, not the server. So the remote machine will use the service file local to it. Did you copy your pg_service.conf to the proper directory on the remote machine?Cheers,Jeff
> On Jul 22, 2020, at 10:02, Michał Lis <fcs1@poczta.onet.pl> wrote:
>
> Copping the pg_service.conf to the client and setting the system variable to this file, rather has no sense.
Well, that's how the pg_service.conf file works; it is read by the client, not by the server:
https://www.postgresql.org/docs/12/libpq-pgservice.html
If you want the client to be able to log into the server without manually specifying credentials, you'll need to store
thosecredentials (password, private key, etc.) somewhere on the client that the client program can access them. The
servicefile is one way of doing that, as is the .pgpasswd file.
--
-- Christophe Pettus
xof@thebuild.com
On Wed, Jul 22, 2020 at 12:47 PM Michał Lis <fcs1@poczta.onet.pl> wrote:
No, the file is only on the server side.
Re-reading the OP I see where you tried to communicate this fact but it is so contrary to the documentation and general use I hadn't considered that this might be what you had done.
I expected the client will ask the server using the service name.
How does the client even know where the server is in order to ask it? The whole point of having pg_service.conf is to avoid passing arguments on the command line and instead store them attached to a name in a local file.
If the service will be found on the server, the server should accept the connection from the client.
This is the purpose of the pg_hba.conf file...
Having the server accept a connection just because the client knows the name of a service is insecure.
In pg_service.conf file can be stored user name and password.
Yes, the user name and password your client is going to present to the user to identify itself. The host field indicates where the server is located.
I want to use login of service type, because I won't to store any login information (ie password) on the client side.
What you describe is worse than having a password securely stored on the local machine.
What you want to do is possible but using the pg_service.conf file plays no role. And really the password should not be placed into pg_service.conf anyway - if you are going to use that file you probably should also use the .pgpass file.
Copping the pg_service.conf to the client and setting the system variable to this file, rather has no sense.
Which machine are you thinking the "system variable" is being set on?
David J.
On Wed, Jul 22, 2020 at 1:02 PM Michał Lis <fcs1@poczta.onet.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
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
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@poczta.onet.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
On Thu, Jul 23, 2020 at 7:26 AM Michał Lis <fcs1@poczta.onet.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.
On Thu, Jul 23, 2020 at 8:26 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Jul 23, 2020 at 7:26 AM Michał Lis <fcs1@poczta.onet.pl> wrote: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).
To seed some thoughts on how that -general discussion could go:
At a fundamental level if the database is physically accessible to an uncontrolled machine there is no perfect solution to preventing the administrator of that machine from obtaining the credentials being used by the application and using them directly. The decision is what level of effort do you want to impose on the administrator to do that (or user, though that is even simpler). If it must be out of the realm of possibility then the software is improperly written given the constraints - it should not utilize direct database connectivity and instead speak with a fully controlled intermediary server in some other protocol and that intermediary server then talks with the database.
Assuming that rewriting the software is not an option the discussion that needs to happen revolves around which are the available/reasonable options for getting unencrypted credentials into the application's memory space so that it may use them in a very private way. At the same time, what are the use cases where tools like pgAdmin would be required and used responsibly versus where the same tool is being used undesirably.
David J.
On Thu, Jul 23, 2020 at 10:26 AM Michał Lis <fcs1@poczta.onet.pl> wrote:
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.
I can reproduce this after using the EDB installer to install.
I create the directory indicated by `pg_config --sysconfdir`, put the pg_service.conf in it, and psql and pg_dump work without setting either PGSERVICEFILE or PGSYSCONFDIR. But pgbench and PgAdmin4 do not work with the service file this way, but do work if I define PGSERVICEFILE, so there does seem to be something buggy going on.
Cheers,
Jeff
On Thu, Jul 23, 2020 at 4:50 PM Jeff Janes <jeff.janes@gmail.com> wrote:
So the bug is that the pg_service.conf file is not read from this location.I can reproduce this after using the EDB installer to install.I create the directory indicated by `pg_config --sysconfdir`, put the pg_service.conf in it, and psql and pg_dump work without setting either PGSERVICEFILE or PGSYSCONFDIR. But pgbench and PgAdmin4 do not work with the service file this way, but do work if I define PGSERVICEFILE, so there does seem to be something buggy going on.
This was on Windows 2019, Windows_Server-2019-English-Full-Base-2020.07.15 (ami-066a1a3fa81bfbd00). With both 9.6 and 12 (the only ones I tested), and I installed both to the default location and a custom location (on a fresh machine each time), makes no difference.
On Thu, Jul 23, 2020 at 2:16 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Thu, Jul 23, 2020 at 4:50 PM Jeff Janes <jeff.janes@gmail.com> wrote:So the bug is that the pg_service.conf file is not read from this location.I can reproduce this after using the EDB installer to install.I create the directory indicated by `pg_config --sysconfdir`, put the pg_service.conf in it, and psql and pg_dump work without setting either PGSERVICEFILE or PGSYSCONFDIR. But pgbench and PgAdmin4 do not work with the service file this way, but do work if I define PGSERVICEFILE, so there does seem to be something buggy going on.This was on Windows 2019, Windows_Server-2019-English-Full-Base-2020.07.15 (ami-066a1a3fa81bfbd00). With both 9.6 and 12 (the only ones I tested), and I installed both to the default location and a custom location (on a fresh machine each time), makes no difference.
pgbench works just fine on Linux when I execute (no environment variables) [1] with an appropriate service entry in /usr/local/pgsql/etc. I find it odd that it wouldn't work on Windows, especially since other programs do and the connection code for pgbench looks the same as for the others and doesn't seem to be influenced by conditional compilation.
pgAdmin4 is out-of-scope for this list.
David J.
1. /usr/local/pgsql/bin/pgbench -i "service=postgres"
Hello,
Thank you for this information and confirmation a part of this problem.
Regards
Michal
Thank you for this information and confirmation a part of this problem.
Regards
Michal
W dniu 2020-07-23 o 23:57, David G. Johnston pisze:
On Thu, Jul 23, 2020 at 2:16 PM Jeff Janes <jeff.janes@gmail.com> wrote:On Thu, Jul 23, 2020 at 4:50 PM Jeff Janes <jeff.janes@gmail.com> wrote:So the bug is that the pg_service.conf file is not read from this location.I can reproduce this after using the EDB installer to install.I create the directory indicated by `pg_config --sysconfdir`, put the pg_service.conf in it, and psql and pg_dump work without setting either PGSERVICEFILE or PGSYSCONFDIR. But pgbench and PgAdmin4 do not work with the service file this way, but do work if I define PGSERVICEFILE, so there does seem to be something buggy going on.This was on Windows 2019, Windows_Server-2019-English-Full-Base-2020.07.15 (ami-066a1a3fa81bfbd00). With both 9.6 and 12 (the only ones I tested), and I installed both to the default location and a custom location (on a fresh machine each time), makes no difference.pgbench works just fine on Linux when I execute (no environment variables) [1] with an appropriate service entry in /usr/local/pgsql/etc. I find it odd that it wouldn't work on Windows, especially since other programs do and the connection code for pgbench looks the same as for the others and doesn't seem to be influenced by conditional compilation.pgAdmin4 is out-of-scope for this list.David J.1. /usr/local/pgsql/bin/pgbench -i "service=postgres"