Re: BUG #16550: Problem with pg_service.conf
| От | Michał Lis |
|---|---|
| Тема | Re: BUG #16550: Problem with pg_service.conf |
| Дата | |
| Msg-id | c997f95c-6e90-6adf-68c9-64b7dc9617f5@poczta.onet.pl обсуждение исходный текст |
| Ответ на | Re: BUG #16550: Problem with pg_service.conf (Jeff Janes <jeff.janes@gmail.com>) |
| Ответы |
Re: BUG #16550: Problem with pg_service.conf
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: BUG #16550: Problem with pg_service.conf (Jeff Janes <jeff.janes@gmail.com>) |
| Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: PG Bug reporting formДата:
Сообщение: BUG #16553: now() function marked PARALLEL RESTRICTED whereas transaction_timestamp() is PARALLEL SAFE
Следующее
От: Tom LaneДата:
Сообщение: Re: BUG #16553: now() function marked PARALLEL RESTRICTED whereas transaction_timestamp() is PARALLEL SAFE