Document how to deal with `SocketPermission` on `install_jar`

[Zastai]

The documentation says

The first is a URL to the jar file to be loaded. The simplest case would be a file: URL giving a path on the PostgreSQL server host to the jar file, with permissions set so the backend can read it. Other forms of URL are also accepted, so install_jar could retrieve a jar from the web, for example.

However, trying to use an https URL fails:

SELECT sqlj.install_jar('https://user:pass@initech.com/some-path/some.jar', 'some-jar', true);

results in ERROR: java.sql.SQLSyntaxErrorException: access denied ("java.net.SocketPermission" "initech.com:443" "connect,resolve").

(this is with openjdk11 on debian running under WSL).

It's not clear to me where I need to configure something to enable such access (JDK/system level, pljava VM args, ...), so it would be useful to have this mentioned in the documentation.

[jcflack]

Thanks for the report. The place would be here in pljava.policy, which gets installed in SYSCONFDIR:

https://github.com/tada/pljava/blob/V1_6_4/pljava-packaging/src/main/resources/pljava.policy#L75

and it would surely be good for the documentation passage you found to mention it also.

[Zastai]

Is there any tracing that confirms whether that policy file is being used? When is it (re)read (i.e. is a postgres restart needed when it is modified, or will the next use of pljava pick it up)?

I tried adding

permission java.net.SocketPermission "initech.com:443", "connect,resolve";

to pljava.policy (did have to chmod it to 644; it was 444 by default), in the section you indicated, after the read-all-files permission, but even after restarting postgres, an install_jar reported the same error as above.

I tried using

permission java.net.SocketPermission "*", "connect,resolve";

instead, and putting the permission before the file permission, but that made no difference.

Adding it to the global grants did have an effect however(*), so I guess Java does not think the socket access comes from the PL/Java code.

(*) well I'm getting a 401 despite having https://user:pass@host/, so I guess accessing URLs that require authorization (which I would assume would not be an uncommon scenario for JAR files) is not something that will work with the current API (Java seems to require setting up an Authenticator for that, so there might need to be an overload of install_jar that takes a username and password for that to work).

[Zastai]

It also looks like names like foo-bar are not allowed for the jar (which is a shame, because that's the typical naming convention for the jar files themselves); I can use foo_bar instead, but that's perhaps also something that bears explicit mention in the documentation.

[jcflack]

Is there any tracing that confirms whether that policy file is being used? When is it (re)read (i.e. is a postgres restart needed when it is modified, or will the next use of pljava pick it up)?

It will be seen on the first use of Java in any given session. Sessions that existed (and had already used Java) before the file was edited will not see the change, but closing such a session and opening a new one will be enough.

Adding it to the global grants did have an effect however(*), so I guess Java does not think the socket access comes from the PL/Java code.

That's interesting. I can't investigate it at the moment, but I see that the comment in the policy file suggests a URLPermission instead. Have you tried that? It may be (possibly, thinking aloud) that a URLPermission granted to the PL/Java code will be treated as implying the necessary SocketPermission at another layer.

Some tracing option are discussed in

https://tada.github.io/pljava/use/policy.html#Troubleshooting

The logs from Java's built-in tracing options can be large haystacks where it is hard to find the needle. PL/Java's TrialPolicy makes an effort to condense the stack traces down to only where protection domains are crossed, generally making it easier to see where a permission is needed.

What is the version of Java here? In JEP 411, the Java developers intend, some time after Java 17, to make the access controls PL/Java relies on here go away. As of the latest releases, I do not see that they have made the APIs go away yet, but if you are using a >17 version and seeing unexpected access controls behavior, it might be worth comparing the behavior on <= 17.

(*) well I'm getting a 401 despite having https://user:pass@host/, so I guess accessing URLs that require authorization (which I would assume would not be an uncommon scenario for JAR files) is not something that will work with the current API (Java seems to require setting up an Authenticator for that, so there might need to be an overload of install_jar that takes a username and password for that to work).

It would certainly be more convenient if user:pass@ worked. :( I'm not sure if that would be best done with an install_jar overload or just by recognizing that syntax in a URL. Or even some more ambitious change to expose Authenticator functionality in some way, which might offer alternatives to exposing the credentials in the jar URL.

It also looks like names like foo-bar are not allowed for the jar

There have been some historic differences between PL/Java's jar names and what the SQL standard says. The standard allows a <jar name> to have an optional schema qualifier and a <jar id>, which is simply <identifier>, so foo-bar would not work but "foo-bar" would (and would be case-sensitive). PL/Java has historically disallowed a schema, and enforced the syntax of Java identifiers instead. It has been that way a long time, but I have been reluctant to document it much, because I would rather have a future version align more with the standard.

[Zastai]

I'm using jdk 11 for now, because that seems to be the default on the Debian I'm using under WSL.

Am traveling now, so won't be able to do anything until next Tuesday, but will try a URLPermission then.

[Zastai]

The URLPermission without corresponding SocketPermission does seem to work (but still with the 401 for my case).

[jcflack]

It is good to know the URLPermission works (and, I trust, works when placed in the grant clause specific to o.p.pljava.codesource?).

It looks as though the authentication support just needs to be added in addClassImages, which should be straightforward. It is disappointing that Java natively parses and recognizes a userinfo part of a URL, but does not use it without further effort.

[Zastai]

Argh, I'll have to retest this tomorrow. While I did add it in the codesource section (and commented out the SocketPermission entry there), I failed to spot the global-level SocketPermission I added.

[Zastai]

OK, so when there is only a URLPermission, at the codebase grant level, things do not work (with the same error about SocketPermission being needed with connect,resolve. Same when adding the URLPermission at the top level.

So it looks like the error is accurate, and a SocketPermission is needed. And I assume that you are using an API that just reads from a URI, with the specific scheme (file/ftp/http/...) being irrelevant, so the actual socket access is not in your code, which means it makes sense that the permission needs to be given elsewhere.

[jcflack]

That is quite possibly my fault. I may have been too clever by half in dropping permissions to only what the URLConnection says it needs.

A URLPermission (added in Java 1.8) ought to do the trick, but when you ask an HttpURLConnection what permission it needs (older method) it replies with a SocketPermission.

So if starting with a URLPermission and then making the connection with permissions dropped to just what the HttpUrlConnection says it needs, the connection may end up being attempted with permissions of ∅.

I probably need to give up on the permission-dropping, and use the non-restricting form of doPrivileged there.

Hmm ... how to get some install_jar-with-a-URL tests into the CI script ....

[jcflack]

install_jar-with-http-URL tests, that is. The URLConnection for a file: URL of course responds with a FilePermission narrowed to the specific file, and that works fine.

As nothing happens in the doPrivileged block beyond getting an InputStream from the URL for further processing outside of it, there should be little opportunity for mischief using the non-restricting form there.

[Zastai]

As long as you can only have a custom URL stream handler by explicitly constructing a URL yourself, that sounds right. But if you you can install a handler for a new/existing URL scheme at the system level, then you could have a URL "nefarious://blah" which triggers a URL connection provided by the MyNefariousStreamHandler; that could then do whatever during the getInputStream().

[jcflack]

MyNefariousStreamHandler could also return a NefariousURLConnection whose getPermission method says "I require AllPermission, thank you very much"*, so the exercise of narrowing permissions to what the URLConnection requires wouldn't help there.

So yes, RuntimePermission "setFactory" should not be doled out like candy.

* which is no more or less than what the non-overridden superclass method returns, anyway :)

[jcflack]

Would you have an opportunity to try a build from the bug/REL1_6_STABLE/issue425 branch and see if this works for you? I have added a test of install_jar over HTTP to the CI script and it works for me locally. Again, Appveyor is showing all failures, which appear to be some issue between Appveyor and Maven repositories rather than a PL/Java problem.

In the CI script, a URLPermission is granted to PL/Java's codebase using the trial policy feature. You could grant a similar permission in your normal policy, and make it narrower than http:* if you like.

[jcflack]

Beware that sqlj.jar_repository has a jarorigin column, and the URL from which a jar was loaded shows up there, with auth credentials if the URL includes those.

That might be worthy of a rethink. I'm not sure the jarorigin column is used for anything, other than reminding humans where the things came from.

[Zastai]

Will do, although I currently don't know when that would be.

[jcflack]

Believed resolved in 1.6.5.