Configuring permissions in PL/Java

TRUSTED (and untrusted) procedural languages

PostgreSQL allows a procedural language to be installed with or without the designation TRUSTED. For a language designated TRUSTED, functions can be created in that language by any user (PostgreSQL role) with USAGE permission on that language, as configured with the SQL commands GRANT USAGE ON LANGUAGE ... and REVOKE USAGE ON LANGUAGE .... For a language that is not designated TRUSTED, only a database superuser may create functions that use it, no matter who has been granted USAGE on it.

In either case, once any function has been created, that function may be executed by any user/role granted EXECUTE permission on the function itself; a language’s USAGE privilege (plus superuser status, if the language is not TRUSTED) is only needed to create a function that uses the language.

Because PL functions execute in the database server, a general-purpose programming language with no restrictions on access to its containing process or the server file system may be used for actions or access that PostgreSQL would normally not permit. A superuser can implement such a function using a non-TRUSTED PL, and design the function to enforce its own limits and be safe for use by whatever roles will be granted EXECUTE permission on it.

A TRUSTED PL is expected to enforce appropriate restrictions so that non-superusers can be allowed to use it to create functions on their own, while still subject to PostgreSQL’s normal protections.

Both kinds have their uses, and many of the available PLs, including PL/Java, install two similarly-named ‘languages’ to permit both. Although either can be renamed, a normal installation of PL/Java will create the language java with the TRUSTED property, and javaU without it.

Note: like any SQL identifier, these language names are case-insensitive when not quoted, and are stored in lowercase in PostgreSQL. The spelling with capital U for untrusted is a common convention.

TRUSTED/untrusted versus sandboxed/unsandboxed

In various places in PL/Java’s API, and in the sections below, the words ‘sandboxed’ or ‘unsandboxed’ are used in place of the PostgreSQL TRUSTED or untrusted, respectively. That choice reflects a little trick of language some readers may notice when new to PostgreSQL: it is about equally easy to read ‘trusted’/‘untrusted’ in two opposite ways. (Is this language trusted because of how tightly I restrict it? Or do I restrict it less tightly because I trust it? Is it like a teenager with the car keys?) Old hands at PostgreSQL know which reading is correct, but because some users of PL/Java may be old hands at Java and newcomers to PostgreSQL, it seems safer for PL/Java to use terms that should give the right idea to readers in both groups.

Permissions available in sandboxed/unsandboxed PL/Java

Most PLs that offer both variants, including PL/Java before 1.6, hardcode the differences between what a function in each language is allowed to do. The sandboxed language would apply a fixed set of limitations, such as forbidding access to the server’s file system, and those limits were not adjustable.

A needed function that would only access one, specific, known-safe file, or perhaps would need no file access but have to make a network connection to one known server, might almost be written under those predetermined restrictions, but that wouldn’t count. It would simply have to be created for the unsandboxed language instead, and written defensively against a much wider range of possible misuses or mistakes.

Beginning with 1.6, PL/Java takes a more configurable approach. Using the Java policy file syntax, any of the permissions known to the JDK can be granted to chosen Java code. The default policy file installed with PL/Java includes these lines:

grant principal org.postgresql.pljava.PLPrincipal$Sandboxed * {
};

grant principal org.postgresql.pljava.PLPrincipal$Unsandboxed * {
    permission java.io.FilePermission
        "<<ALL FILES>>", "read,write,delete,readlink";
};

A few observations fall out. Whatever the names may suggest, neither alternative is truly “unsandboxed”. Both are subject to the same Java policy, but can be granted different permissions within it.

As distributed, the only difference between the two is access to the filesystem. The “sandboxed” case grants no additional permissions at all, and the “unsandboxed” case adds read, readlink, write, and delete permission for any file (still subject to the operating system permissions in effect for the PostgreSQL server process, which will be enforced independently of Java).

The permissions granted for either case are freely configurable. Granting the more lenient or dangerous permissions to the “unsandboxed” language is conventional, and reflected in the way PostgreSQL is more restrictive about what roles can create functions in that language.

The permissions known to the JDK are plentiful and fine-grained. New permissions can also be defined and required in custom code, and selectively granted in the policy like any other permission.

The PLPrincipal indicating sandboxed/unsandboxed is only one of the conditions that can be referred to in a policy to control the permissions granted. Others are described below.

Sources of Java policy

Java’s standard Policy implementation will read from a sequence of policy files specified as URLs. The first is normally part of the Java installation, supplying permission grants necessary for trouble-free operation of the JVM itself, and a second will be read, if present, from a user’s home directory.

PL/Java, by default, uses the first Java-supplied URL, for the policy file installed with Java, followed by the file pljava.policy in the directory reported by pg_config --sysconfdir. A default version of that file is installed with PL/Java.

The pljava.policy file, by default, is used instead of any .java.policy file in the OS user’s home directory that Java would normally load. There probably is no such file in the postgres user’s home directory, and if for any reason there is one, it probably is not tailored to PL/Java.

The configuration variable pljava.policy_urls can be used to name different, or additional, policy files.

Permission grants are cumulative in Java’s standard Policy implementation: there is no policy syntax to deny a permission if it is conveyed by some other applicable grant in any of the files on the policy_urls list. If an application must restrict a permission that is granted unconditionally in the Java-supplied policy file, for example, the typical approach would be to copy that file, remove the grant of that permission, and alter pljava.policy_urls to read the modified file in place of the original.

Conditional and unconditional permission grants

A grant in a policy can be unconditional, for example:

grant {
    permission java.util.PropertyPermission
        "sqlj.defaultconnection", "read";
};

That grant (which is included in the default pljava.policy) allows any Java code to read that property.

Conditional grants to PLPrincipal$Sandboxed and PLPrincipal$Unsandboxed were shown above.

It is also possible to condition a grant on the codebase (represented as a URL) of the code being executed. If the SQLJ.INSTALL_JAR function is used to install PL/Java’s examples jar under the name examples, this grant will allow the JSR-310 test example to work:

grant codebase "sqlj:examples" {
    permission java.util.PropertyPermission "user.timezone", "write";
};

The sqlj URL scheme is (trivially, and otherwise nonfunctionally) defined within PL/Java to allow forming a codebase URL from the name of an installed jar.

Grant conditions currently unsupported

A reader familiar with Java security policy may consider granting permissions based on the signer identity of a cryptographically signed jar, or on a Principal representing the PostgreSQL role executing the current function. In this version of PL/Java, such grants are not yet supported.

While it is not yet possible to grant permissions based on a principal representing the PostgreSQL session user or role, it is possible for a superuser, with ALTER ROLE ... SET, to set user-specific values of pljava.policy_urls that will load different, or additional, policy files. While that will only reflect the connected user at the start of the session and not any role changes during the session, it may be enough for some uses.

PLPrincipal with a language name

The grants for sandboxed/unsandboxed shown above have a * wildcard after the principal class name. It is possible to replace the wildcard with the name of the language (as used in SQL with CREATE LANGUAGE and CREATE FUNCTION) in which a function is declared.

A basic installation of PL/Java creates just two named languages, java and javaU, declared as TRUSTED/sandboxed and untrusted/unsandboxed, respectively. In such an installation, these grants would be effectively equivalent to those shown earlier:

grant principal org.postgresql.pljava.PLPrincipal$Sandboxed "java" {
};

grant principal org.postgresql.pljava.PLPrincipal$Unsandboxed "javaU" {

    // Java does not circumvent operating system access controls;
    // this grant will still be limited to what the OS allows a
    // PostgreSQL backend process to do.
    permission java.io.FilePermission
        "<<ALL FILES>>", "read,readlink,write,delete";
};

However, it is possible to use CREATE LANGUAGE to create any number of named languages that share PL/Java’s handler entries and can be used to declare PL/Java functions. For example, suppose CREATE TRUSTED LANGUAGE is used to create another language entry with the name java_tzset and this grant is included in the policy:

grant principal org.postgresql.pljava.PLPrincipal$Sandboxed "java_tzset" {
    permission java.util.PropertyPermission "user.timezone", "write";
};

If the JSR-310 test example in PL/Java’s examples jar is declared with LANGUAGE java_tzset rather than LANGUAGE java (as, in fact, it is), it will be able to set the time zone and succeed.

The SQLJ.ALIAS_JAVA_LANGUAGE function can be used to create such aliases conveniently.

When grants to specific named languages and grants with the wildcard are present, code will have all of the permissions granted to the specific language by name, in addition to all permissions that appear in grants to the language class (PLPrincipal$Sandboxed or PLPrincipal$Unsandboxed, whichever applies) with a wildcard name.

A grant is silently ignored unless the class and the name both match. If the java_tzset language were declared as above but a grant entry used the right name but the PLPrincipal$Unsandboxed class by mistake, that grant would be silently ignored.

Grants to a codebase compared with grants to a principal

Whenever a Java operation requires a permission check, it could be on a call stack several levels deep, perhaps involving code from more than one codebase (or, more generally, “protection domain”). The Java rule is that the needed permission must be in effect, one way or another, for every protection domain on the call stack at the point where the permission is needed. In other words, the available permissions are the intersection, over all domains on the stack, of the permissions in effect for each domain. The rationale is that the proposed action must not only be something the currently executing method is allowed to do; there is a calling method causing this method to do it, so it must also be something the caller is allowed to do, and so on up the stack. (For one crucial exception to this rule, see handling privileges.)

Permissions granted to a Principal are not so tightly bound to what specific code is executing; the same code may execute at different times on behalf of more than one principal. A principal often represents a user or role for whom the code is executing, though role principals are not implemented in this PL/Java release. The sandboxed/unsandboxed function distinction is represented as a kind of Principal because it, too, is a property of the thread of execution, from its entry at the SQL-declared function entry point and through any number of protection domains the thread may traverse. Any permissions granted by principal may be thought of as combined with any codebase-specific permissions in every domain present on the stack.

Entry points other than SQL-declared functions

Not every entry into PL/Java is through an SQL-declared function with an associated language name or sandboxed/unsandboxed property. For those that are not, permission decisions are based on an “access control context” (essentially, the in-effect Principals and initial protection domains) constructed as described here.

Set-returning functions

While a set-returning function is declared as an SQL function, the initial call is followed by repeated calls to the returned iterator or ResultSet provider or handle, and a final call to close the provider or handle. The access control context constructed for the initial call is saved, and reused while iterating and closing.

Savepoint and transaction listeners

Java code may register listeners for callbacks at lifecycle stages of savepoints or transactions. Each callback will execute in the access control context of the code that registered it, except that PL/Java’s own domain will also be represented on the stack. Because effective permissions are an intersection over all domains on the stack, if any permission has been granted to the callback’s codebase that is not also granted to PL/Java’s own code, the callback code will be unable to exercise that permission except within a doPrivileged block.

Mapped UDT readSQL/writeSQL methods

When a Java user-defined type is defined without fully integrating it into PostgreSQL’s type system as a BaseUDT, its readSQL and writeSQL methods will not have corresponding SQL function declarations, but will be called directly as PL/Java converts values between PostgreSQL and Java form. Those calls will be made without any PLPrincipal, sandboxed or unsandboxed, so they will execute with only the permissions granted to their codebase or unconditionally.

The conversion functions for a BaseUDT do have SQL function declarations, and will execute in a context constructed based on the declaration in the usual way.

SQL-declared functions not in PL/Java-managed jars

It is possible to issue an SQL CREATE FUNCTION naming a method from a codebase that is not a PL/Java-managed sqlj: jar, such as a jar on the filesystem module path, or a method of the Java runtime itself. For example, many how-to articles can be found on the web that demonstrate a successful PL/Java installation by declaring an SQL function that directly calls java.lang.System.getProperty.

Such declarations are allowed, but will execute as if called from a protection domain with the same Principals, if any, that PL/Java would normally supply, and no other permissions but those the policy grants unconditionally.

Note: many of the how-to articles that can be found on the web happen to demonstrate their System.getProperty-calling example functions on some property that isn’t readable under Java’s default policy. Those examples should be changed to use a property that is normally readable, such as java.version or org.postgresql.pljava.version.

Troubleshooting

When in doubt what permissions may need to be granted in pljava.policy to run some existing PL/Java code, these techniques may be helpful.

Running PL/Java with a ‘trial’ policy

To simplify the job of finding the permissions needed by some existing code, it is possible to run PL/Java at first with a ‘trial’ policy, allowing code to run while logging permissions that pljava.policy has not granted. The log entries have a condensed format meant to be convenient for this use. Trial policy configuration is described here.

Using policy debug features provided by Java

Java itself offers a number of debugging switches to reveal details of permission decisions. It may be useful to add -Djava.security.debug=access in the setting of pljava.vmoptions, and observe the messages on the PostgreSQL backend’s standard error (which should be included in the log file, if logging_collector is on). It is not necessary to change the pljava.vmoptions setting cluster-wide, such as in postgresql.conf; it can be set in a single session for troubleshooting purposes.

Other options for java.security.debug can be found in Troubleshooting Security. Some can be used to filter the logging down to requests for specific permissions or from a specific codebase.

The log output produced by Java’s debug options can be voluminous compared to the condensed output of PL/Java’s trial policy.

Forward compatibility

The current implementation makes use of the Java classes Subject and SubjectDomainCombiner in the javax.security.auth package. That should be regarded as an implementation detail; it may change in a future release, so relying on it is not recommended.