"service" syntax support: "jdbc:postgresql://?service=my-service" (pg_service.conf and .pgpass)

[MarekUniq]

Introduction

psql (libpq) supports ini-style service file. pgjdbc does not support servcie.
psql (libpq) supports .pgpass file. pgjdbc does not support .pgpass.

Goal

Add pg_service.conf and .pgpass support to pgjdbc.
pgjdbc should behave as close as possible to libpq.

References

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

pg_service.conf

Following JDBC URL syntaxes are valid

jdbc:postgresql://?service=my-service
jdbc:postgresql:///?service=my-service
jdbc:postgresql://localhost/?service=my-service
jdbc:postgresql://localhost:5432/?service=my-service
jdbc:postgresql://localhost/?service=my-service&port=5432
jdbc:postgresql://?service=my-service&port=5432
jdbc:postgresql://?dbname=postgres&service=my-service

Properties are overridden in the following order

1. PGProperty defaults for PGHOST, PGPORT and PGDBNAME
   are overridden by
2. Properties loaded by Driver.loadDefaultProperties() (user, org/postgresql/driverconfig.properties)
   are overridden by
3. Properties retrieved by "?service=my-service"
   are overridden by
4. Properties given in java code as argument to DriverManager.getConnection(url, properties)
   are overridden by
5. Values written into "URL like //my-host:5432/"
   are overridden by
6. Values written into "URL parameters like ///?host=my-host&port=5432"

Example

--- pg_service.conf ---
[my-service]
port=4444
--- pg_service.conf ---

--- connect code ---
    String url = "jdbc:postgresql://:6666/?service=my-service&port=7777";
    Properties props = new Properties();
    props.setProperty("PGPORT", "5555");
    Connection conn = DriverManager.getConnection(url, props);
--- connect code ---

if [port=7777] is specified it wins
if [port=7777] is not specified then [//:6666/] wins
if [//:6666/] is not specified then ["PGPORT", "5555"] wins
if ["PGPORT", "5555"] is not specified then [port=4444] wins
if [port=4444] is not specified then [org/postgresql/driverconfig.properties] wins
if [org/postgresql/driverconfig.properties] is not specified then [PGProperty.PG_PORT.getDefaultValue()] wins

"pg_service.conf" resource is selected using the following order

1. java command line property: -Dorg.postgresql.pgservicefile=<my-file1>
   if <my-file1> is specified then it is used (even if it does not exist)
   otherwise continue
   (psql message: psql: error: service file "my-file1" not found)

2. environemnt varaible: ${PGSERVICEFILE}=<my-file2>
   if <my-file2> is specified then it is used (even if it does not exist)
   (psql message: psql: error: service file "my-file2" not found)

3. hard-coded path for windows: System.getenv("APPDATA")\postgresql\pg_service.conf
   hard-coded path for "other os": System.getProperty("user.home")/.pg_service.conf
   if <this-file> exist and is readable then use it
   (psql message: <no any message>)
   otherwise continue

4. environemnt varaible ${PGSYSCONFDIR}=<my-dir4>
   if "<my-dir4>" is specified then file is used: "<my-dir4>/pg_service.conf"
   if <this-file> exist and is readable then use it
   (psql message: <no any message>)
   otherwise continue

If definition for service is not found: Error is reported
(psql: error: definition of service "my-service" not found)

.pgpass

Password is overridden in the following order

1. Password retrieved from ".pgpass" resource
   is overridden by
2. Password retrieved from "?service=my-service"
   is overridden by
3. Password property given in java code as argument to DriverManager.getConnection(url, properties)
   is overridden by
4. Value written into "URL parameters like ///?host=my-host&password=mypass"

".pgpass" resource is selected using the following order

1. java command line property: -Dorg.postgresql.pgpassfile=<my-file1>
   if <my-file1> is specified then it is used. No error, even if file does not exist.
   otherwise continue

2. environemnt varaible: ${PGPASSFILE}=<my-file2>
   if <my-file2> is specified then it is used. No error, even if file does not exist.
   otherwise continue

3. hard-coded path for windows: System.getenv("APPDATA")\postgresql\pgpass.conf
   hard-coded path for "other os": System.getProperty("user.home")/.pgpass
   if <this-file> exist and is readable then use it
   otherwise continue

If matching line for password is not found?
No error is reported, password property remains unset.

".pgpass" file format is the following (colon-separated)

hostname:port:database:username:password[:[any meaningless string]]

Colon (:) and backslash (\) chars for "hostname", "database" and "username" can be optionally escaped with backslash (\).
Colon (:) and backslash (\) chars for "password" must be escaped using backslash (\).

Instead of value, a star (*) can be specified which means any value. For example:

*:port:database:username:password
hostname:*:database:username:password
hostname:port:*:username:password
*:*:database:username:password
*:*:*:username:password
*:*:*:*:password

Based on examples above, multiple lines can match. First matching line is selected.

[davecramer]

We'd welcome a PR. Now I will say we are in the middle of trying to release 43.0.0 so it may be a while before it gets committed.

[MarekUniq]

@davecramer - I can not find a way to create new branch. Do I miss permissions for that?

[davecramer]

@MarekUniq you need to clone the repo locally and then create a local branch. Then you push your branch to your cloned branch. That will create a PR

[MarekUniq]

@davecramer - I think I miss some steps

I have a clone:

git clone ssh://git@github.com/pgjdbc/pgjdbc.git

I have branch:

$ git branch
* issue_2278_pgservice_pgpass
  master

I did commits.
I did pull of master and rebase.

Using "idea", branch push command gives the following error:

$ git push --progress --porcelain origin refs/heads/issue_2278_pgservice_pgpass:issue_2278_pgservice_pgpass --set-upstream

ERROR: Permission to pgjdbc/pgjdbc.git denied to MarekUniq.
fatal: Could not read from remote repository.
Please make sure you have the correct access rights
and the repository exists.

[davecramer]

@MarekUniq Yes, my mistake. You need to fork it... then you can do whatever you want on your fork.

Cheers.

[MarekUniq]

@davecramer - thank you. I managed to open PR. I did not find a way how to fill in "Linked issues" on PR page but maybe it requires higher rights (or skills) :)

[davecramer]

fixes #2282 works :)

[davecramer]

closed with #2347