An experimental extension implementing a JWT-based context for Postgres, protected by digital signatures. This is meant to be useful for use cases like row-level security (RLS), requiring a trusted context the users can't manipulate.

Use cases like RLS require trusted context, used as source for values referenced in the policies. The canonical examples rely on Postgres roles to do this - the policies either reference current_user, or use it to lookup additional values.

But that has various issues. Each application user requires a dedicated database role, and managing systems with many roles can be challenging.

It also complicates connection pooling, as connections are tied to roles. Pooler can either operate one pool per role, but with many roles that contradicts the benefits of connection pooling. Or the poooler can use a common role and use SET ROLE to set the "correct" role for each user, and then RESET ROLE when the connection is returned.

This however is a security weakness. There's nothing preventing a user from issuing RESET ROLE + SET ROLE to set arbitrary role. This is known, and the community discusses ways to address this, by implementing some sort of "role sandboxing."

But even if some variant of "role sandboxing" gets committed, the other issues with using roles as a basic for RLS policies will remain.

This extension is an attempt to provide a different type of a trusted context, unrelated from roles. It's based on JSON Web Tokens (JWT), i.e. JSON values protected by digital signatures (this part relies on openssl.

This means the "trust" is established in a different way. With roles, the trust comes from the authentication system. With a JWT context, the trust comes from digital signatures and knowledge of signing keys.

A context is a set of key/value pairs, encoded as a JSON document. The context encodes arbitrary information about the user, which may then be used by RLS policies, etc.

Imagine a comple context, with keys TENANT_ID, and COUNTRY:

{"TENANT_ID": 213234, "COUNTRY": "US"}

The context is generated by a trusted component (may be the application itself), and signed with a secret/private key unknown to the user.

To use the context, it needs to be "set" for a connection. The database verifies the signature (this may require only a public key), and rejects contexts with invalid signatures.

JWT is a (proposed) standard for signing JSON documents. JWT tokens have three parts - a header (identifying the signature algorithm), a body with "claims" about identity, and a signature. Header and body are in JSON format, encoded using Base64url. The encoded result is signed, and signature is appended to the token (also Base64url-encoded).

A signed token may look like this:

eyJhbGciIDogIkhTMjU2In0.eyJpZCI6IDEwMH0.YgbKr2HxskqUCu1GY4yz20cceQ881qZ50LlRNqNWFYk

Digital signatures may use either symmetric algorithms (HMAC-based), or asymmertic algorithms (RSA/ECDSA). With the asymmetric signatures, the database does not need to know the private key - verifying the signature reqyires only the public key.

With the HMAC-based signatures, the secret key is used both for signing and verifying signatures. The database needs to kow it, which is a weakness of this signature type (and a risk).

The keys are set using GUCs called jwt.pubkey and jwt.secret. Both gucs are defined as SUSET, i.e. only superuser can change them. See the later section about GUCs for options where to define the keys.

To set the context, use another GUC called jwt.context:

SET jwt.context = 'eyJhbGciIDogIkhTMjU2In0.eyJpZCI6IDEwMH0.YgbKr2HxskqUCu1GY4yz20cceQ881qZ50LlRNqNWFYk';

When processing the SET command and parsing the value, the database verifies the signature (using the keys set for the session) and accepts it only if the signature matches. If the signature does not match, the SET fails with an error and the context is not set.

If the signature is valid, the context is set for the session, and may be queried using functions jwt() and jwt(key). An example policy might look like this:

CREATE POLICY select_filter_tenant ON mytable
FOR SELECT
USING (mytable.tenant_id = jwt('TENANT_ID'));

The context is stored in process private memory, so it gets discarded after the connection gets closed. When returning the connection to a connection pool, the context gets discarded.

JWT specifies a wide range of signing algorithms. This experimental extension currently supports only HS256 and ES256 signatures. Adding other algorithms should not be very hard.

The extension defines two configuration parameters for setting up the context - the key for signature verification, and the context itself.

This is the public key, generated by OpenSSL EC Key generator, in base64 format (url-encoded). The keypair may be generated outside the database, using the openssl command-line tool.

This is the secret key, used by symmetric (HMAC-based) signatures, like HS256. It's just an array of random bytes, in base64 url-encoding. It can be generated in any way.

Both GUCs are defined in the "superuser" (PGC_SUSET) context, which means only a superuser can set it. This is important, because otherwise the application user could set the GUCs to a key he/she controls, and sign arbitrary contexts.

There are several practical places where to define the keys:

• postgresql.conf - The simplest option. There's a single key for the whole instance, i.e. all databases/users will use the same key.

• ALTER DATABASE / ALTER ROLE - Allows setting keys for individual databases and/or roles. Useful for instances serving multiple apps.

• startup packet / SQL - Allows per-connection / per-session values, if the connecting role is a superuser. But it also has the weakness described earlier, with the user being able to execute RESET ROLE.

• security-definer function - A variant of the SQL option, but the key is installed in a controlled way (i.e. not determined by the user).

Using superuser roles breaks the signing scheme (more in the following sections). This rules out the "startup packet / SQL" option, with the exception of testing.

An option storing the context (a set of key/value pairs). The user can simply do

SET jwt.context = '... token ...';

The system verifies the signature (using jwt.pubkey or jwt.secret, depending on the signature algorithm), parses the value, etc. If the signature matches, the context is remembered for the session.

The context may be reset using RESET when not needed. Connection pools do this automatically before reusing the connection, by executing RESET ALL.

The GUC values are discarded by RESET ALL, and this get discarded when the connection gets returned to a connection pool (assuming the pool initiates the reset, as expected).

The extension defines four functions for generating keys, signing tokens and verifying signatures. Those functions are meant to make testing and experimentation more convenient, not for production use.

Generate public/private key pair, useful for ES256 signatures.

The key pair is returned both in PEM and DER formats. The DER parts are returned encoded using Base64url.

Generate secret key, useful for HS256 signatures.

Returns the generated secret, encoded in Base64url.

Generates a JWT token, with header and body (both JSON values). algo determines the signature algorithm (either HS256 or HS256).

Returns the signed JWT token.

Verifies signature of the JWT token, passed throught context. The secret may specify the key used to verify signatures. If the secret is NULL, the key is taken from jwt.pubkey or jwt.secret, depending on the signature algorithm.

Returns true if the signature is valid, false otherwise.

The threat model depends on what components are involved in handling database connection, and whether those components are trusted or not. To discuss the threat model we need to define a couple basic pieces:

• connection user - The user that'll be using the connection to run SQL queries. The user is unprivileged, i.e. not a Postgres superuser.

• superuser - The privileded database user used to setup environment, define keys, etc. Exempt from many checks.

• secret key - The secret part of the public/private keypair. Known only to the superuser.

• public key - The public part of the keypair. Known to anyone, but only the superuser can set it for the instance/database/role.

• context - The list of key/value pairs, signed by the secret key. Only superuser can sign contexts, anyone can verify the signature.

The security relies on these restrictions, and failures render the scheme insecure. See the following section listing some of the risks.

Another way to look at this is what actions need to happen:

• context signing - signing a context with the secret key

• public key setup - setting the public key for a given connection

• context setup - setting the GUC, with signature verification

• context reset - discarding the context

The components resposible for these actions depend on the architecture. Let's briefly discuss two common cases. First without a connection pool, and with a connection pool.

It's reasonable to assume the connection user is malicious - either intentionally or by accident (e.g. by SQL injection). Otherwise why bother with any of this?

In this architecture the application obtains direct connections to the database, so the actions have to be performed by either the database or the application itself. The question is how trusted the application is.

If the application is trusted, it can handle everything. It can install the public key, either through a security definer function, or even by connecting as superuser and switching to a different role.

This requires that the user not have access to the connection (and can't run arbitrary SQL). This involves an assumption that there are no SQL injection vulnerabilities.

If the application is untrusted, some of the steps have to be handled by the database. In particular, the database needs to setup the public key in a controlled way, so that the user can't pick an arbitrary one. This also requires the user does not connect as a superuser.

The application will still install the signed context, but it needs to be careful not to leak the signed values. The context acts as passwords, and knowing a context is sufficient for impersonation (assuming both are signed by the same key).

With a connection pool (which may include other types of middleware), some of the steps can happen on the connection pool.

We assume the connection pool is more trusted than the application. Otherwise the connection pool could be considered a component of the application, and it'd be the same as the preceding section.

If the connection pool is more trusted, the more steps we can move to the pool the better. It'd be possible for the pool to setup everything, before handing the connection to the user:

• The public key could be set by the pool, unless already done by the database.

• The context can be looked up elsewhere (e.g. using auth_query in PgBouncer) or even constructed on the connection pool itself. This could also be offloaded to some sort of trusted HSM component.

In this case none of the steps would be performed by the application, greatly reducing the attack surface. The responsibilities are moved to the connection pool, which is safer as the users don't have access to that and can't run arbitrary SQL on it.

Here's an overview of risks that could make the context insecure.

The signed context serves similar purpose as a password, but it has a more complex internal structure. This means that if a signed context leaks (e.g. to the server log), it may be used by someone else.

There are restrictions, e.g. the context can be used only in sessions with the same public key (otherwise signature verification fails). And a couple additional protections/mitigations could be added.

Nevertheless, it's probably better to (a) treat signed contexts just like passwords, and (b) use the architecture where contexts are managed by a separate component and not by the application.

If the user is defined as a superuser role, it's trivial to change the public key to a key picked by the user. The user then can construct and sign arbitrary contexts, completely evading the protections.

Moreover, superusers bypass the RLS system entirely, so if the signed context was used for that, it would matter anyway.

The public key and signed context are stored in memory. If the user can gain access to that (through loading a custom C extension or some sort of vulnerability), he could modify those two values.

But with this kind of access to memory, the user could do anything to the server. The fact that it breaks the signed context is a detail.

It might be possible to move the sensitive steps (signature checks and store of the key/context) to some sort of trusted enclave, like Intel SGX or ARM TrustZone. I haven't explored these options in detail, and I'm not sure how resilient it could be assuming C memory access.

Trusted contexts provide little protection when the database itself is malicious (this may include RCE vulnerabilities). A malicious database may ignore signatures, log signed contexts, or simply ignore the policy when accessing the data.

Trusted contexts are not a technique for confidential computing.

While working on the extension, I could think of various improvements. Some of those would help mitigating some of the risks mentioned earlier (perhaps not entirely, but even reducing the risk is useful).

The context could include some sort of expiration, so that a leaked context is not valid forever. A simple timestamp added to the beginning of the context would do. It's not clear if it'd be verified only when setting the context (after which the context remains valid forever), or even when accessing the context later (automated sign-out).

Could work nicely in architectures where the signed context are issued in an automated way, on request from a trusted component.

Note: JWT specification already includes expiration.

The context could be made settable only once, i.e. once it's set for a session, it can't be altered. Seems to be incompatible with connection pooling, unfortunately. Maybe it'd work if a requires the reset to be authorized in some way (e.g. by extra password/superuser)? But that's pretty much the role sandboxing discussed for RESET ROLE.

The context might include additional metadata, e.g. IP address/port or connection timestamp, binding it to a particular connection, to make it harder to reuse a leaked context. But it would also require some sort of automated context signing, because the contexts will be unique for connection. Especially with connection pooling.

A twist on set-once contexts could be a challenge-response protocol, i.e. the server would generates a challenge, and the signed context would have to factor that into the signature somehow.

This can be seen as an extension of additional information in the context, to bind it to a particular TCP connection. It has the same problems with connection pooling, and would benefit from automated context signing.

The application may need to manage keys for a given connection. However, the GUCs for keys are defined as SUSET, so only superuser roles can change them. And using superuser roles for applicaiton are a terrible idea - for example, they are automatically exempt from RLS.

A possible approach is to manage the key using security-definer functions. It must not simply accept the key as an argument, but determine it e.g. by lookup (based on immutable session information).

Postgres now has "login" event trigger (since version 17). This seems it might be quite useful for establishing the context, in a way the user can't interfere with easily.

Probably incompatible with connection pooling, as the trigger runs only once, but the connection is kept and reused (for different users).

Right now each session has a single public key defined, and there's no way to rotate the keys. The key can be changed easily, and already set contexts remain valid, but new connections have to use the new key.

It would be possible to support multiple public keys, and some sort of overlapping validity, for gradual transition to contexts signed by the new key pair.

Installing this is very simple, especially if you're using pgxn client. All you need to do is this:

$ pgxn install jwt_context
$ pgxn load -d mydb jwt_context

and you're done. You may also install the extension manually:

$ make install
$ psql dbname -c "CREATE EXTENSION jwt_context"

And if you're on an older version (pre-9.1), you have to run the SQL script manually

$ psql dbname < `pg_config --sharedir`/contrib/jwt_context--1.0.0.sql

That's all. You'll need to define the public key next.

This software is distributed under the terms of BSD 2-clause license. See LICENSE or http://www.opensource.org/licenses/bsd-license.php for more details.