Row-level security (RLS) is a great feature. It allows restricting access to rows by applying filters defined by a policy. It’s a tool useful for cases when the data set can’t be split into separate databases.

Sadly, using RLS may be quite cumbersome. RLS requires some sort of “trusted context” for the RLS policies. The policies need to filter using data the user can’t change. If the filter uses some sort of “tenant ID”, and the user can change it to an arbitrary value, that would break the RLS concept.

This is why solutions like using GUCs are flawed, because the access control for GUC is very limited. The traditional solution is to use roles, which derives the trust from authentication.

It occurred to me it should be possible to build a trusted context on cryptography, independently of authentication. I’ll explain the basic idea, and discuss a couple interesting variations. I’ve also published an experimental extension jwt_context, implementing this using JWT.

I’m interested in all kinds of feedback. Is it a good idea to use JWT this way, as a basis for RLS context? Did I miss some fundamental issue? Are there interesting improvements?

Note: Whenever you see “context,” imagine a set of key/value pairs. The RLS policies can “query” the context for a key, and use the value in RLS policies to filter rows.

RLS vs. roles

RLS requires a trusted context - set of values that may be referenced in policies. The values need to be set in a safe way, and the user must not be able to modify them arbitrarily. This is why most RLS examples rely on current_user. It identifies the user, and it’s authenticated, so the user can’t change the value easily.

But it also means there has to be a role per application user. And if you have managed systems doing that, you probably see it may be challenging. Roles are database objects and need to be managed at that level (it’s more a task for a DBA than an app developer).

Roles also complicate pooling. The current_user role for a connection is set during the initial authentication. It may be changed later using SET ROLE (to one of “member roles”), and poolers often leverage that. Unfortunately, an user with SQL access can do the RESET ROLE + SET ROLE dance too. That may be a security weakness in general, but it definitely makes this approach unsafe for RLS.

There’s an interesting thread proposing to address this by locking down RESET ROLE. It mentions some already-existing approaches.

Note: The RESET ROLE is an issue only if the user can execute custom SQL. It might be thanks to having access to the raw connection, or through some SQL injection vulnerability. If you assume this can’t happen, then this should be fine. But it’s a weakness.

Note: Postgres 17 added support for login event triggers. With a login trigger it should be possible to create a trusted context similar to Oracle VDB. That seems useful for some use cases, but it still has the same incompatibility with connection pooling.

Cryptography-based context

To be useful for RLS, a context needs to be “trusted.” The values need to come from a trusted source, and the user must not be able to subvert them in some way. With current_user the trust comes from being tied to authentication.

But what if we allowed the context to be “generated” by a trusted system, and could verify that? Imagine some sort of authentication or authorization system. If the database can verify the context really comes from this “source of truth” system, and wasn’t tampered with, it could use it in RLS policies.

Digital signatures allow us to do this. The context may be signed, and the database may verify the signature. It could even do some additional checks on the context data, if needed.

Imagine a process like this:

1. The trusted system verifies (authenticates?) a user, generates a context, signs it and passes it to the user.

2. The user opens a connection, provides the signed context.

3. The database verifies the signature, validates the context in other ways (e.g. expiration), and sets it into a read-only value.

4. The RLS policies use the context to restrict which rows are visible.

There are multiple ways to do digital signatures. It can be done using symmetric or asymmetric cryptography, with different trade-offs.

The symmetric approach (HMAC) is somewhat simpler, but both sides know the secret key. If it leaks, it can be used to sign arbitrary contexts. Perhaps the database itself could become malicious, but at that point it’s mostly game over. It can ignore the RLS policies and just access the data. Leaking the secret key is a bigger concern.

The asymmetric approach (RSA/ECDSA) helps with this. The database needs to only verify the signature, and the public key is enough for that. But this key is not particularly sensitive - if it “leaks” it’s fine, it can’t be used to forge contexts. Only the system that signs the contexts needs to know the private key.

JWT

The PoC version of this idea used a custom key/value format, and the signatures were done using libsodium. Then I realized there already is a (proposed) standard for this: JSON Web Token (JWT). So I ditched my custom stuff in favor of JWT.

This has many benefits.

JWT uses JSON to serialize the key/value context, I don’t need to worry about something custom. JWT specifies supported algorithms, so I don’t need to worry about picking that either. I’m unlikely to do a better job than JWT.

Using JWT means it should be easier to integrate this with other systems. It does not matter which system generated the context. If it contains the necessary information, and the database can verify the signature, it should be usable.

jwt_context

The extension is available here: jwt_context. It’s still an early experimental version, but hopefully good enough for testing and trying things out. This blog post is not meant to be a tutorial/guide for the extension, so see the README for instructions.

It’s not very complicated, though. The core of the extension is three GUCs (and you only need to set two) for defining the key/context. And then two functions for querying the context for keys.

There’s also a couple functions for generating keys, signing tokens and verifying signatures. But those exist to make testing and experimentation more convenient, not for “production” use.

The extension is fairly limited - it supports only two token types (HS256 and ES256), doesn’t check token expiration, and so on.

Basic architecture

The basic architecture / flow looks something like this:

1. The user authenticates to the application.

2. Application generates a token with the key/value pairs, signs it.

3. Application installs the token for the connection (sets the GUC).

4. The database verifies the signature, using a key defined either for the instance, database or user.

5. If the signature is valid, the key/value pairs are stored in memory.

6. The user runs queries on tables with RLS policies, referencing keys from the trusted context.

This assumes the application is trusted enough to perform the signing. That requires access to the private key, building the context, etc.

Advanced architectures

The “basic architecture” has a couple limitations.

For example, it does not work with connection pooling. The context is “per session”, and it gets discarded when the connection gets reused. It’s stored in a GUC, and so subject to RESET ALL. That’s correct behavior, we don’t want to accidentally reuse the context for a different user. But it also means the connection gets effectively broken.

It assumes the application is trusted enough to generate signatures. What if it’s not trusted? Maybe the goal is to defend against issues like SQL injection vulnerabilities, with RLS as a mitigation? Or maybe there is no “application” and the client simply gets a raw connection? In this case the application can’t do the signing.

There could be additional components involved. The context could be generated by a separate system, to limit access to the private key and minimize the attack surface. Or maybe the authentication system is responsible for this?

The architecture could be modified in various ways to address this. Consider for example this flow, with a connection pool:

1. The user authenticates to the application.

2. Application generates a token with the key/value pairs, signs it.

3. Application requests a connection from the pool (the context is sent using “pgoptions” in the connection request).

4. The connection pool sets up the context in the new connection, and ensures it gets used by any connection backing the client connection.

5. The database verifies the signature, using a key defined either for the whole instance, database or user.

6. If the signature is valid, the key/value pairs are stored in memory.

7. The user runs queries on tables with RLS policies, referencing keys from the trusted context.

This is just an example. The important questions are:

• Which component is responsible for generating / signing the context?

• Which component is responsible for installing the context?

• Which components need to be aware of the context?

The components with access to the private key (e.g. to sign a context) need to be trusted. Components that only manipulate signed contexts may be untrusted, or even unaware of the contexts at all.

This also depends on the exact thread model, i.e. what kind of attacks we expect, and need to defend against.

Changes to some components may be required. A connection pool may need to track the per-session context value, and restore it after reusing the connection.

Key location

The keys used to verify signatures are set using two GUCs defined by the extension:

• jwt.secret - secret key for symmetric signatures (HMAC)
• jwt.pubkey - public key for asymmetric signatures (RSA, ECDSA)

The GUCs are defined as SUSET, which is explained like this:

SUSET options can be set at postmaster startup, with the SIGHUP mechanism, or from the startup packet or SQL if you’re a superuser.

This means a regular user can’t change the key - that would be a fatal flaw, as the user could use an arbitrary key. SUSET options may also be defined using ALTER USER and ALTER DATABASE. There does not need to be a single key for the whole instance, each database/user may use a different key.

The options may be set at the same time. The HMAC signatures will use jwt.secret, RSA/ECDSA signatures will use jwt.pubkey.

The jwt.pubkey value is not sensitive, and is visible to anyone. The jwt.secret value is sensitive, and is shown only to superusers. For regular users it’s replaced by the “(hidden)” string.

Missing features

The extension implements only some of the features included in the JWT specification. Some of those features seem quite useful, and can be implemented later. An example of such a missing JWT feature is “expiration” time, limiting the validity of the signed context.

There are other features, even outside the JWT specification. There’s no convenient way to manage/rotate the keys, for example. Perhaps it’d be better to allow multiple keys at once, or using certificates instead of plain keys.

Weaknesses

Every system has a weakness, and signed contexts are not an exception. What matters is whether the weakness(es) affect your use cases. Here’s a couple of the main weaknesses I can think of:

• superuser access - The keys are defined using a SUSET GUC, so a superuser connection can set an arbitrary key, making the signatures useless. This applies to the regular privilege checks too, though. Don’t use superuser roles for application connections.

• custom C - Run custom C code (e.g. through functions defined in an extension) means access to process memory. The function could read the key, or even modify it. Again, this is not unique to signed context, but a general weakness. Make sure to audit any extensions with direct access to memory.

• token leaks - Signed tokens may be sensitive. Knowing the JWT token reveals the payload (the JSON document), and it may also allow impersonating other users. This may be mitigated in various ways. JWE can encrypt/hide the token payload. There may be expiration time, limiting the validity of a token. There may be additional validation, to restrict token reuse.

I’m sure there are more concerns, but I’d expect most of them to be considered in the JWT (JWS/JWE) specification.

Summary

The extension is still just an experimental version, with a lot of open questions and room for improvement. But it seems like an interesting application of JWT tokens.

I’ve been thinking also about improvements that would require changes to PGBouncer and/or Postgres code. It might be useful to support this at the protocol level (as an optional feature), instead of having to set the context only through SET statements. That would make it easier for connection pools to manage contexts.

But extending the protocol is … not easy.