Release notes

PL/Java 1.5.1

This release chiefly adds support for PostgreSQL 9.6 and 10 (beta), and plays more nicely with pg_upgrade. With PostgreSQL 9.6 support comes the ability to declare functions PARALLEL { UNSAFE | RESTRICTED | SAFE }, and with PG 10 support, transition tables are available to triggers.

Security

1.5.1 removes the code at issue in CVE-2016-0768, which pertained to PostgreSQL large objects, but had never been documented or exposed as API.

This is not expected to break any existing code at all, based on further review showing the code in question had also been simply broken, since 2006, with no reported issues in that time. That discovery would support an argument for downgrading the severity of the reported vulnerability, but with no need to keep that code around, it is more satisfying to remove it entirely.

Developers wishing to manipulate large objects in PL/Java are able to do so using the SPI JDBC interface and the large-object SQL functions already available in every PostgreSQL version PL/Java currently supports.

Changes

PostgreSQL 9.6 and parallel query

A function in PL/Java can now be annotated parallel={UNSAFE | RESTRICTED | SAFE}, with UNSAFE the default. A new user guide section explains the possibilities and tradeoffs. (Good cases for marking a PL/Java function SAFE may be rare, as pushing such a function into multiple background processes will require them all to start JVMs. But if a practical application arises, PostgreSQL’s parallel_setup_cost can be tuned to help the planner make good plans.)

Although RESTRICTED and SAFE Java functions work in simple tests, there has been no exhaustive audit of the code to ensure that PL/Java’s internal workings never violate the behavior constraints on such functions. The support should be considered experimental, and could be a fruitful area for beta testing.

Tuple counts widened to 64 bits with PostgreSQL 9.6

To accommodate the possibility of more than two billion tuples in a single operation, the SPI implementation of the JDBC Statement interface now provides the JDK 8-specified executeLargeBatch and getLargeUpdateCount methods defined to return long counts. The original executeBatch and getUpdateCount methods remain but, obviously, cannot return counts that exceed INT_MAX. In case the count is too large, getUpdateCount will throw an ArithmeticException; executeBatch will store SUCCESS_NO_INFO for any statement in the batch that affected too many tuples to report.

For now, a ResultSetProvider cannot be used to return more than INT_MAX tuples, but will check that condition and throw an error to ensure predictable behavior.

pg_upgrade

PL/Java should be upgraded to 1.5.1 in a database cluster, before that cluster is binary-upgraded to a newer PostgreSQL version using pg_upgrade. A new Upgrading installation-guide section centralizes information on both upgrading PL/Java in a database, and upgrading a database with PL/Java in it.

PostgreSQL 10 and trigger transition tables

A trigger annotation can now specify tableOld="name1" or tableNew="name2", or both, and the PL/Java function servicing the trigger can do SPI JDBC queries and see the transition table(s) under the given name(s). The triggers example code has been extended with a demonstration.

Logging from Java

The way the Java logging system has historically been plumbed to PostgreSQL’s, as described in issue 125, can be perplexing both because it is unaffected by later changes to the PostgreSQL settings after PL/Java is loaded in the session, and because it has honored only log_min_messages and ignored client_min_messages. The second part is easy to fix, so in 1.5.1 the threshold where Java discards messages on the fast path is determined by the finer of log_min_messages and client_min_messages.

Conveniences for downstream package maintainers

The mvn command to build PL/Java will now accept an option to provide a useful default for pljava.libjvm_location, when building a package for a particular software environment where the likely path to Java is known.

The mvn command will also accept an option to specify, by the path to the pg_config executable, the PostgreSQL version to build against, in case multiple versions exist on the build host. This was already possible by manipulating PATH ahead of running mvn, but the option makes it more explicit.

A new packaging section in the build guide documents those and a number of considerations for making a PL/Java package.

Updated PostgreSQL APIs tracked

  • heap_form_tuple
  • 64-bit SPI_processed
  • 64-bit Portal->portalPos
  • 64-bit FuncCallContext.call_cntr
  • 64-bit SPITupleTable.alloced and .free
  • IsBackgroundWorker
  • IsBinaryUpgrade
  • SPI_register_trigger_data

Earlier releases

PL/Java 1.5.0

This, the first PL/Java numbered release since 1.4.3 in 2011, combines compatibility with the latest PostgreSQL and Java versions with modernized build and installation procedures, automatic generation of SQL deployment code from Java annotations, and many significant fixes.

Security

Several security issues are addressed in this release. Sites already using PL/Java are encouraged to update to 1.5.0. For several of the issues below, practical measures are described to mitigate risk until an update can be completed.

CVE-2016-0766, a privilege escalation requiring an authenticated PostgreSQL connection, is closed by installing PL/Java 1.5.0 (including prereleases) or by updating PostgreSQL itself to at least 9.5.1, 9.4.6, 9.3.11, 9.2.15, 9.1.20. Vulnerable systems are only those running both an older PL/Java and an older PostgreSQL.

CVE-2016-0767, in which an authenticated PostgreSQL user with USAGE permission on the public schema may alter the public schema classpath, is closed by release 1.5.0 (including prereleases). If updating to 1.5.0 must be delayed, risk can be mitigated by revoking public EXECUTE permission on sqlj.set_classpath and granting it selectively to responsible users or roles.

This release brings a policy change to a more secure-by-default posture, where the ability to create functions in LANGUAGE java is no longer automatically granted to public, but can be selectively granted to roles that will have that responsibility. The change reduces exposure to a known issue present in 1.5.0 and earlier versions, that will be closed in a future release (CVE-2016-0768, see large objects, access control below).

The new policy will be applied in a new installation; permissions will not be changed in an upgrade, but any site can move to this policy, even before updating to 1.5.0, with REVOKE USAGE ON LANGUAGE java FROM public; followed by explicit GRANT commands for the users/roles expected to create Java functions.

CVE-2016-2192, in which an authenticated user can alter type mappings without owning the types involved. Exploitability is limited by other permissions, but if type mapping is a feature being used at a site, one can interfere with proper operation of code that relies on it. A mitigation is simply to REVOKE EXECUTE ... FROM PUBLIC on the sqlj.add_type_mapping and sqlj.drop_type_mapping functions, and grant the privilege only to selected users or roles. As of 1.5.0, these functions require the invoker to be superuser or own the type being mapped.

Version compatibility

PL/Java 1.5.0 can be built against recent PostgreSQL versions including 9.5, using Java SE 8, 7, or 6. See version compatibility for more detail. OpenJDK is well supported. Support for GCJ has been dropped; features of modern Java VMs that are useful to minimize footprint and startup time, such as class-data sharing, are now more deeply covered in the installation docs.

Build procedures

Since 2013, PL/Java has been hosted on GitHub and built using Apache Maven. See the new build instructions for details.

Reported build issues for specific platforms have been resolved, with new platform-specific build documentation for OS X, Solaris, Ubuntu, Windows MSVC, and Windows MinGW-w64.

The build produces a redistributable installation archive usable with the version of PostgreSQL built against and the same operating system, architecture, and linker. The type of archive is jar on all platforms, as all PL/Java installations will have Java available.

Installation procedures

The jar produced by the build is executable and will self-extract, consulting pg_config on the destination system to find the correct default locations for the extracted files. Any location can be overridden. (Enhancement requests 6, 9)

PL/Java now uses a PostgreSQL configuration variable, pljava.libjvm_location, to find the Java runtime to use, eliminating the past need for highly platform-specific tricks like link-time options or runtime-loader configuration just so that PL/Java could find Java. PostgreSQL configuration variables are now the only form of configuration needed for PL/Java, and the libjvm_location should be the only setting needed if file locations have not been overridden.

In PostgreSQL 9.1 and later, PL/Java can be installed with CREATE EXTENSION pljava. Regardless of PostgreSQL version, installation has been simplified. Former procedures involving Deployer or install.sql are no longer required. Details are in the new installation instructions.

Schema migration

The tables used internally by PL/Java have changed. If PL/Java 1.5.0 is loaded in a database with an existing sqlj schema populated by an earlier PL/Java version (1.3.0 or later), the structure will be updated without data loss (enhancement request 12). Remember that PL/Java runs independently in each database session where it is in use. Older PL/Java versions active in other sessions can be disrupted by the schema change.

A trial installation of PL/Java 1.5.0 can be done in a transaction, and rolled back if desired, leaving the schema as it was. Any concurrent sessions with active older PL/Java versions will not be disrupted by the altered schema as long as the transaction remains open, but they may block for the duration, so such a test transaction should be kept short.

Changes

Behavior of readSQL and writeSQL for base and mirror user-defined types

In the course of fixing issue #98, the actual behavior of readSQL and writeSQL with base or mirror types, which had not previously been documented, now is, along with other details of PL/Java’s type coercion rules found only in the code. Because machine byte order was responsible for issue #98, it now (a) is selectable, and (b) has different, appropriate, defaults for mirror UDTs (which need to match PostgreSQL’s order) and for base UDTs (which must stay big-endian because of how binary COPY is specified). A new documentation section explains in detail.

USAGE to PUBLIC no longer default for java language

Of the two languages installed by PL/Java, functions that declare LANGUAGE javau can be created only by superusers, while those that declare LANGUAGE java can be created by any user or role granted the USAGE privilege on the language.

In the past, the language java has been created with PostgreSQL’s default permission granting USAGE to PUBLIC, but PL/Java 1.5.0 leaves the permission to be explicitly granted to those users or roles expected to create Java functions, in keeping with least-privilege principles. See large objects, access control under known issues for background.

SQL generated by Java annotations

Java code developed for use by PL/Java can carry in-code annotations, used by the Java compiler to generate the SQL commands to declare the new functions, types, triggers, etc. in PostgreSQL (enhancement request 1011112, though different in implementation). This eliminates the need to have Java code and the corresponding SQL commands developed in parallel, and the class of errors possible when both are not updated together. It also allows compile-time checks that the Java methods or classes being annotated are suitable (correct access modifiers, signatures, etc.) for their declared SQL purposes, rather than discovering such issues only upon loading the code into PostgreSQL and trying to use it.

The Java compiler writes the generated SQL into a “deployment descriptor” file (pljava.ddr by default), as specified by the SQL/JRT standard. The file can be included in a jar archive with the compiled code, and the commands will be executed by PL/Java when the install_jar function is used to load the jar.

SQL generation is covered in the updated user documentation, and illustrated in the Hello, World example and several other supplied examples. Reference information is in the API documentation. It is currently usable to declare functions, triggers, and user-defined types, both base and composite.

The history of this feature in PL/Java is long, with the first related commits appearing in 2005, six years in advance of an enhancement request for it. It became generally usable in 2013 when building with Java SE 6 or later, using the annotation processing framework Java introduced in that release. 1.5.0 is the first PL/Java numbered release to feature it.

Annotation keyword changes

If you have been using the SQL generation feature in prerelease git builds of 2013 or later, be aware that some annotation keywords have changed in finalizing the 1.5.0 release. Java code that was compiled using the earlier keywords will continue to work, but will have to be updated before it can be recompiled.

  • For functions: effects=(VOLATILE,STABLE,IMMUTABLE) was formerly type=
  • For functions: type= (an explicit SQL return type for the function) was formerly complexType=
  • For functions: trust=(SANDBOXED,UNSANDBOXED) was formerly (RESTRICTED,UNRESTRICTED)
  • For triggers: called=(BEFORE,AFTER,INSTEAD_OF) was formerly when= and conflicted with the WHEN clause introduced for triggers in PostgreSQL 9.0.
A jar may have more than one deployment descriptor

PL/Java formerly allowed only one entry in a jar to be a deployment descriptor (that is, a file of SQL commands to be executed upon loading or unloading the jar). The SQL/JRT standard allows multiple entries to be deployment descriptors, executed in the order they are mentioned in the jar manifest, or the reverse of that order when the jar is being unloaded. PL/Java now conforms to the standard.

The behavior is useful during transition to annotation-driven deployment descriptor generation for a project that already has a manually-maintained deployment descriptor. PL/Java’s own pljava-examples project is an illustration, in the midst of such a transition itself.

Note the significance placed by SQL/JRT on the order of entries in a jar manifest, whose order is normally not significant according to the Jar File Specification. Care can be needed when manipulating manifests with automated tools that may not preserve order.

Conditional execution within deployment descriptors

Deployment descriptors have a primitive conditional-execution provision defined in the SQL/JRT standard: commands wrapped in a
BEGIN IMPLEMENTOR identifier construct will only be executed if the identifier is recognized by the SQL/JRT implementation in use. The design makes possible jars that can be installed on different database systems that provide SQL/JRT features, with database-specific commands wrapped in BEGIN IMPLEMENTOR blocks with an identifier specific to the system. By default, PL/Java recognizes the identifier postgresql (matched without regard to case).

PL/Java extends the standard by allowing the PostgreSQL configuration variable pljava.implementors to contain a list of identifiers that will be recognized. SQL code in a deployment descriptor can conditionally add or remove identifiers in this list to influence which subsequent implementor blocks will be executed, giving a still-primitive but more general control structure.

In sufficiently recent PostgreSQL versions, the same effect could be achieved using DO statements and PL/pgSQL control structures, but this facility in PL/Java does not require either to be available.

Interaction with SET ROLE corrected

PL/Java formerly was aware of the user ID associated with the running session, but not any role ID that user may have acquired with SET ROLE. The result would commonly be failed permission checks made by PL/Java when the session user did not have the needed permission, but had SET ROLE to a role that did. Likewise, within install_jar, PL/Java would execute deployment descriptor commands as the original session user rather than as the user’s current role, with permission failures a likely result.

Correcting this issue has changed the PL/Java API, but without a bump of major version because the prior API, while deprecated, is still available.

  • getOuterUserName and executeAsOuterUser are new, and correctly refer to the session user or current role, when active.
  • getSessionUserName and executeAsSessionUser are still present but deprecated, and their semantics are changed. They are now deprecated aliases for the corresponding new methods, which honor the set role. Use cases that genuinely need to refer only to the session user and ignore the role should be rare, and should be discussed on the mailing list or opened as issues.
Unicode transparency

Since the resolution of bug 21, PL/Java contains a regression test to ensure that character strings passed and returned between PostgreSQL and Java will round-trip without alteration for the full range of Unicode characters, when the database encoding is set to UTF8.

More considerations apply when the database encoding is anything other than UTF8, and especially when it is SQL_ASCII. Please see character encoding support for more.

Bugs fixed

Since 1.5.0-BETA3
In 1.5.0-BETA3

Updated PostgreSQL APIs tracked

Several APIs within PostgreSQL itself have been added or changed; PL/Java now uses the current versions of these where appropriate:

  • find_coercion_pathway
  • set_stack_base
  • GetOuterUserId
  • GetUserNameFromId
  • GetUserIdAndSecContext
  • pg_attribute_*
  • Large objects: truncate, and 64-bit offsets

Known issues and areas for future work

Developments in PostgreSQL not yet covered
Large objects, access control
PL/Java does not yet expose PostgreSQL large objects with a documented, stable API, and the support it does contain was developed against pre-9.0 PostgreSQL versions, where no access control applied to large objects and any object could be accessed by any database user. PL/Java’s behavior is proper for PostgreSQL before 9.0, but improper on 9.0+ where it would be expected to honor access controls on large objects (CVE-2016-0768). This will be corrected in a future release. For this and earlier releases, the recommendation is to selectively grant USAGE on the java language to specific users or roles responsible for creating Java functions; see “default USAGE permssion” under Changes.
INSTEAD OF triggers, triggers on TRUNCATE

These are supported by annotations and the SQL generator, and the runtime will deliver them to the specified method, but the TriggerData interface has no new methods to recognize these cases (that is, no added methods analogous to isFiredAfter, isFiredByDelete). For a method expressly coded to be a TRUNCATE trigger or an INSTEAD OF trigger, that is not a problem, but care should be taken when coding a trigger method to handle more than one type of trigger, or creating triggers of these new types that call a method developed pre-PL/Java-1.5.0. Such a method could be called with a TriggerData argument whose existing isFired... methods all return false, likely to put the method on an unexpected code path.

A later PL/Java version should introduce trigger interfaces that better support such evolution of PostgreSQL in a type-safe way.

Constraint triggers
Constraint trigger syntax is not supported by annotations and the SQL generator. If declared (using hand-written SQL), they will be delivered by the runtime, but without any constraint-trigger-specific information available to the called method.
Event triggers
Event triggers are not yet supported by annotations or the SQL generator, and will not be delivered by the PL/Java runtime.
Range types
No predefined mappings for range types are provided.
PRE_PREPARE, PRE_COMMIT, PARALLEL_ABORT, PARALLEL_PRE_COMMIT, and PARALLEL_COMMIT transaction callbacks, PRE_COMMIT subtransaction callbacks
Listeners for these events cannot be registered and the events will not be delivered.
Imperfect integration with PostgreSQL dependency tracking

In a dump/restore, manual intervention can be needed if the users/roles recorded as owners of jars are missing or have been renamed. A current thread on pgsql-hackers should yield a better solution for a future release.

Quirk if deployment descriptor loads classes from same jar

The install_jar function installs a jar, optionally reading deployment descriptors from the jar and executing the install actions they contain. It is possible for those actions to load classes from the jar just installed. (This would be unlikely if the install actions are limited to typical setup, function/operator/datatype creation, but likely, if the install actions also include basic function tests, or if the setup requirements are more interesting.)

If, for any class in the jar, the first attempt to load that class is made while resolving a function declared STABLE or IMMUTABLE, a ClassNotFoundException results. The cause is PostgreSQL’s normal treatment of a STABLE or IMMUTABLE function, which relies on a snapshot from the start of the install_jar query, when the jar was not yet installed. A workaround is to ensure that the install actions cause each needed class to be loaded, such as by calling a VOLATILE function it supplies, before calling one that is STABLE or IMMUTABLE. (One could even write install actions to declare a needed function VOLATILE before the first call and then redeclare it.)

This issue should be resolved as part of a broader rework of class loading in a future PL/Java release.

Partial implementation of JDBC 4 and later

The changes to make PL/Java build under Java SE 6 and later, with version 4.0 and later of JDBC, involved providing the specified methods so compilation would succeed, with real implementations for some, but for others only stub versions that throw SQLFeatureNotSupportedException if used. Regrettably, there is nothing in the documentation indicating which methods have real implementations and which do not; to create such a list would require an audit of that code. If a method throws the exception when you call it, it’s one of the unimplemented ones.

Individual methods may be fleshed out with implementations as use cases arise that demand them, but for a long-term roadmap, it seems more promising to reduce the overhead of maintaining another JDBC implementation by sharing code with pgjdbc, as has been discussed on pljava-dev.

Exception handling and logging

PL/Java does interconvert between PostgreSQL and Java exceptions, but with some loss of fidelity in the two directions. PL/Java code has some access to most fields of a PostgreSQL error data structure, but only through internal PL/Java API that is not expected to remain usable, and code written for PL/Java has never quite had first-class standing in its ability to generate exceptions as information-rich as those from PostgreSQL itself.

PL/Java in some cases generates the categorized SQLExceptions introduced with JDBC 4.0, and in other cases does not.

This area may see considerable change in a future release. Thoughts on logging is a preview of some of the considerations.

Types with type modifiers and COPY

Although it is possible to create a PL/Java user-defined type that accepts a type modifier (see the example), such a type will not yet be handled by SQL COPY or any other operation that requires the input or receive function to handle the modifier. This is left for a future release.

Credits

PL/Java 1.5.0 owes its being to original creator Thomas Hallgren and many contributors:

Daniel Blanch Bataller, Peter Brewer, Frank Broda, Chapman Flack, Marty Frasier, Bear Giles, Christian Hammers, Hal Hildebrand, Robert M. Lefkowitz, Eugenie V. Lyzenko, Dos Moonen, Asif Naeem, Kenneth Olson, Johann Oskarsson, Thomas G. Peters, Srivatsan Ramanujam, Igal Sapir, Jeff Shaw, Rakesh Vidyadharan, grunjol, mc-soi.

Periods in PL/Java’s development have been sponsored by EnterpriseDB.

In the 1.5.0 release cycle, multiple iterations of testing effort have been generously contributed by Kilobe Systems and by Pegasystems, Inc.

PL/Java 1.4.1 (9 December 2010)

Note: Does not compile with Java 6. Use JDK 1.5 or 1.4.

Compiles with PostgreSQL 8.4 and 9.0.

Connection.getCatalog() has been implemented.

Bugfixes:

Other commits:

For a multi-threaded pljava function we need to adjust stack_base_ptr before calling into the backend to avoid stack depth limit exceeded errors. Previously this was done only on query execution, but we need to do it on iteration of the ResultSet as well.

When creating a variable length data type, the code was directly assigning the varlena header length rather than going through an access macro. The header format changed for the 8.3 release and this manual coding was not noticed and changed accordingly. Use SET_VARSIZE to do this correctly.

Handle passed by value data types by reading and writing directly to the Datum rather than dereferencing it.

If the call to a type output function is the first pljava call in a session, we get a crash. The first pljava call results in a SPI connection being established and torn down. The type output function was allocating the result in the SPI memory context which gets destroyed prior to returning the data to the caller. Allocate the result in the correct context to survive function exit.

Clean up a warning about byteasend and bytearecv not having a prototype when building against 9.0 as those declarations are now in a new header file.

PL/Java 1.4.0 (1 February 2008)

Warning! The recent postgresql security releases changed the API of a function that PL/Java uses. The source can be built against either version, but the binaries will only run against the version they were built against. The PL/Java binaries for 1.4.0 have all been built against the latest server releases (which you should be using anyway). If you are using an older you will have to build from source. The binary releases support: 8.3 - All versions. 8.2 - 8.2.6 and up. 8.1 - 8.1.11 and up. 8.0 - 8.0.15 and up.

PL/Java 1.3.0 (18 June 2006)

This release is about type mapping and the creation of new types in PL/Java. An extensive effort has gone into making the PL/Java type system extremely flexible. Not only can you map arbitrary SQL data types to java classes. You can also create new scalar types completely in Java. Read about the Changes in version 1.3.

Changes

PL/Java 1.2.0 (20 Nov 2005)

The PL/Java 1.2.0 release is primarily targeted at the new PostgreSQL 8.1 but full support for 8.0.x is maintained. New features include support IN/OUT parameters, improved meta-data handling, and better memory management.

PL/Java 1.1.0 (14 Apr 2005)

PL/Java 1.1.0 includes a lot of new features such as DatabaseMetaData, ResultSetMetaData, language handlers for both trusted and untrusted language, additional semantics for functions returning SETOF, and simple ObjectPooling.

PL/Java 1.0.1 (07 Feb 2005)

This release resolves a couple of important security issues. The most important one is perhaps that PL/Java now is a trusted language. See Security for more info. Filip Hrbek, now member of the PL/Java project, contributed what was needed to make this happen.

PL/Java 1.0.0 (23 Jan 2005)

Today, after a long period of fine tuning, PL/Java 1.0.0 was finally released.