Using JWT to establish a trusted context for Row Level Security
12 days ago
- #Row-Level Security
- #JWT
- #PostgreSQL
- Row-level security (RLS) is a feature that restricts access to rows by applying filters defined by a policy.
- RLS requires a trusted context, which is a set of key/value pairs that RLS policies can query to filter rows.
- Traditional solutions for RLS context rely on roles, which derive trust from authentication but complicate pooling and management.
- A cryptography-based context using digital signatures (like JWT) can establish trust independently of authentication.
- JWT (JSON Web Token) is a proposed standard for signed contexts, offering benefits like JSON serialization and algorithm support.
- The experimental extension 'jwt_context' implements this idea, allowing RLS policies to use JWT-signed contexts.
- Basic architecture involves application-generated tokens, database verification, and RLS policy application.
- Advanced architectures may involve separate systems for context generation and connection pooling considerations.
- Keys for verifying signatures are set via GUCs (jwt.secret for HMAC, jwt.pubkey for RSA/ECDSA), with security considerations.
- Weaknesses include superuser access risks, custom C code vulnerabilities, and token leaks.
- Future improvements could involve protocol-level support for easier connection pool integration.