4

On Microsoft Windows, the pgpass file is located as followed:

On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

source: https://www.postgresql.org/docs/current/libpq-pgpass.html

When I create a subscription, as shown below, I do not provided a password in the connection string.

CREATE SUBSCRIPTION alltables
CONNECTION 'host=192.168.1.5 port=5432 user=replicator1 dbname=test1'
PUBLICATION alltables;

Instead, I want the password to be stored in a pgpass.conf file with restricted access.

After a few tries, I have noticed that in the case of creating a subscription, the pgpass.conf is not expected to be found in the user directory of the Windows user running the CREATE SUBSCRIPTION command. In other words, if my Windows 10 user name is foobar the file is not expected to be C:\Users\foobar\AppData\Roaming\postgresql\pgpass.conf. Instead it seems to be expected in the user directory of the Windows user running the PostgreSQL program.

I have found this by adding passfile=C:\\Users\\foobar\\AppData\\Roaming\\postgresql\\pgpass.conf in the connection string and granting read permissions to the NETWORK SERVICE user (the user that runs the postgres.exe processes). In that case, the subscription is created. But if I remove the passfile option from the connection string then the terse ERROR: could not connect to the publisher: fe_sendauth: no password supplied error message is returned.

So, where would %APPDATA%\postgresql\pgpass.conf point to in that case? Said differently, where should the pgpass.conf file be in that case?

soliz
  • 735
  • 1
  • 7
  • 17
  • 2
    That question essentially is: what is `%APPDATA%` for the `NETWORK SERVICE` user? – Laurenz Albe Jun 16 '21 at 13:46
  • I suppose so, yes – soliz Jun 16 '21 at 13:59
  • One solution would be to run the Postgres service with a regular (restricted) user that does have an `%APPDATA%` directory. If you do that, you need to make sure to adjust the privileges on the current data directory so that the "new" user has full access to it. –  Jun 16 '21 at 21:24

1 Answers1

3

As commented by Laurenz, the question essentially is: what is %APPDATA% for the NETWORK SERVICE user?

In my case that is C:\Windows\ServiceProfiles\NetworkService\AppData\Roaming\.

So, by creating the pgpass file in C:\Windows\ServiceProfiles\NetworkService\AppData\Roaming\postgresql\pgpass.conf, I can create a subscription as below and the password will successfully be taken from the file.

CREATE SUBSCRIPTION alltables
CONNECTION 'host=192.168.1.5 port=5432 user=replicator1 dbname=test1'
PUBLICATION alltables;
soliz
  • 735
  • 1
  • 7
  • 17