YSQL and YCQL: Enforcing Uniform Credential Expiration Across API Boundaries

Question

Enforcing Uniform Credential Expiration Across YSQL and YCQL
The goal is to implement a consistent least‑privilege policy that revokes user access automatically after a specified time, regardless of whether the client connects via YSQL (PostgreSQL‑compatible) or YCQL (Cassandra‑compatible). In YSQL, roles can be created with a VALID UNTIL timestamp, and authentication fails once that time is reached. YCQL, however, lacks a documented equivalent, so an external mechanism would be needed to enforce expiration on its keyspace and table permissions.
Key constraints include the need to maintain the same expiration semantics for both APIs, avoid lock‑out scenarios during cluster upgrades, and support SCRAM‑SHA‑256 password encryption for secure authentication. The uncertainty centers on how best to propagate or synchronize the expiration information between YSQL and YCQL, and whether YugabyteDB provides a unified approach or requires third‑party tooling.
Specific questions:

Does YugabyteDB offer a built‑in mechanism to set expiration on YCQL roles or permissions, analogous to YSQL’s VALID UNTIL?
Can a shared metadata table or external scheduler be used to enforce expiration across both APIs, and what are the recommended best practices?
What impact does enabling authentication on only one API have on credential expiration handling, and how can cross‑API consistency be verified?

Tasadduq Burney · Answer

Short answer
No. As of the YugabyteDB releases I know (through the 2.20/2024 series — verify against your version, since YCQL auth features have evolved), YCQL has no documented equivalent of YSQL's VALID UNTIL. YSQL inherits the PostgreSQL role attribute, checked when a password-authenticated connection is established. YCQL role management is Cassandra-derived: the role record (can_login, is_superuser, salted_hash, member_of) carries no expiry field, and no YugabyteDB catalog joins the two auth stores (pg_authid vs. system_auth.roles). Uniform expiration must therefore be enforced from outside the database.
Established behavior vs. verify-on-your-release
Treat as established:

VALID UNTIL gates new YSQL connections only; existing sessions persist until they disconnect or are terminated.
YCQL has no per-role or per-permission expiry. Disabling a principal means revoking permissions, rotating its password, or dropping it.

Confirm on your release before committing to a design: the exact auth flag names (YSQL has used ysql_enable_auth; YCQL auth has been gated by use_cassandra_authentication), the YSQL SCRAM-SHA-256 support level, and the current system_auth schema.
A pattern that works for both APIs
This answers your second question: yes, a shared registry plus an idempotent reconciler is the practical approach.

Keep one source of truth in YSQL:
CREATE TABLE security.credential_expiry (
  role_name  text,
  api        text NOT NULL CHECK (api IN ('ysql','ycql')),
  expires_at timestamptz NOT NULL,
  status     text NOT NULL DEFAULT 'active',
  PRIMARY KEY (role_name, api)
);
YSQL action — set a past timestamp rather than dropping the role. It is reversible, preserves grants, and survives a partially applied run:
ALTER ROLE app_reporting VALID UNTIL '2026-01-01 00:00:00+00';
YCQL action — rotate the password to a fresh random value and/or revoke its keyspace permissions (Cassandra-style syntax; details vary slightly by release). Both are reversible; avoid DROP ROLE so a failed run during an upgrade window can be undone:
ALTER ROLE app_reporting WITH PASSWORD = '<random>';
If expiry must cut live sessions at the instant, terminate the role's YSQL backends via pg_terminate_backend() filtered on pg_stat_activity.usename. YCQL has no session-kill, so bound exposure with short client-side connection-pool lifetimes.
Upgrade safety: pause the reconciler during rolling upgrades (or restrict it to version-agnostic statements) and rehearse on a staging cluster that mirrors the auth configuration. Reversible actions are what make this safe.
Run the job under a dedicated admin identity per API — not the default cassandra superuser — with credentials from a secret manager, on a 5–15 minute cycle.

Authentication on one API only
If auth is enabled on YSQL but not YCQL (or vice versa), expiration is decorative on the open API: any client connects without credentials, so there is nothing to expire and no consistency to verify. Cross-API checks are meaningful only with both enabled — then attempt a login with expired credentials on each API and expect rejection on both.
Verification checklist

YSQL: create a test role with VALID UNTIL one minute out; confirm psql login fails after expiry and that a pre-existing session survives until terminated.
YCQL: after the reconciler fires, confirm the old password is rejected and system_auth.roles reflects the rotated hash or revoked permissions.
Audit each run: compare the registry against pg_authid.rolvaliduntil and system_auth.roles; alert on drift.
SCRAM: password_encryption = 'scram-sha-256' changes how YSQL passwords are stored and verified, not when they expire — VALID UNTIL applies regardless of the exchange. YCQL manages its own salted hash and does not expose the algorithm.

One detail would change this recommendation: do expired principals need live sessions cut at the expiry instant, or is blocking new logins within a few minutes sufficient? The first forces pg_terminate_backend() plus connection-pool TTLs on both APIs; the second needs only the reconciler.

YSQL and YCQL: Enforcing Uniform Credential Expiration Across API Boundaries

Enforcing Uniform Credential Expiration Across YSQL and YCQL

1 answer

Short answer

Established behavior vs. verify-on-your-release

A pattern that works for both APIs

Authentication on one API only

Verification checklist

Your perspective belongs here.

0 question comments