Safely Use Dynamic Tenant GUCs with RLS and YSQL Connection Manager

Jim Knicely

A common multi-tenant design is to use a shared application database role and identify the active tenant with a custom YSQL configuration parameter, or GUC, such as:

				
					SET LOCAL app.tenant_id = '101';

A Row-Level Security (RLS) policy can then use that value to determine which rows the application is allowed to read or modify.

This approach works with YSQL Connection Manager (YCM), but there is an important edge case to account for.

With YSQL Connection Manager, a custom GUC can be reset to an empty string (''). If the RLS policy then casts that value directly to bigint, the query fails with:

				
					ERROR: invalid input syntax for type bigint: ""

The solution is to design the RLS policy to safely handle both:

  • ● A completely missing GUC
  • ● A GUC whose value is an empty string
Recommended Pattern

Use current_setting(..., true) together with NULLIF(..., '') before casting the tenant identifier:

NULLIF(current_setting('app.tenant_id', true), '')::bigint

The true argument to current_setting() tells PostgreSQL/YSQL to return NULL when the setting does not exist instead of raising an error.

NULLIF(..., '') handles the second case by converting an empty string to NULL.

The result is a fail-closed RLS policy: if the application does not establish a valid tenant context, no rows are visible instead of the query failing with a type-conversion error. This is the same basic workaround described in the source.

First, Verify That You Are Actually Using YCM

Before troubleshooting Connection Manager behavior, verify that your client connection is actually going through YCM.

Related YugabyteDB Tip

Want to verify that your connection is actually using YCM? See Am I Actually Using YCM? for several quick ways to check.

One useful check is:

				
					SHOW yb_is_client_ysqlconnmgr;

If it returns:

				
					on

the current client connection is going through YSQL Connection Manager.

Why the Direct Cast Is Fragile

Consider this RLS policy:

				
					CREATE POLICY tenant_isolation_policy
ON tenant_orders
FOR ALL
USING (
  tenant_id = current_setting('app.tenant_id')::bigint
)
WITH CHECK (
  tenant_id = current_setting('app.tenant_id')::bigint
);

It works while app.tenant_id contains a valid value such as:

				
					101

But the policy becomes fragile when the setting is missing or contains an empty string.

Tenant Context Value Direct Cast Safe Expression
Tenant set '101' Returns 101 Returns 101
Parameter missing NULL with missing_ok=true Can raise an error Returns NULL
Parameter reset '' Invalid bigint cast Returns NULL

Use a Fail-Closed Policy

Define the policy like this instead:

				
					CREATE POLICY tenant_isolation_policy
ON tenant_orders
FOR ALL
TO tenant_app
USING (
  tenant_id =
    NULLIF(current_setting('app.tenant_id', true), '')::bigint
)
WITH CHECK (
  tenant_id =
    NULLIF(current_setting('app.tenant_id', true), '')::bigint
);

There are two safeguards:

				
					current_setting('app.tenant_id', true)

returns NULL if the setting does not exist.

And:

				
					NULLIF(..., '')

converts an empty string into NULL.

Casting NULL to bigint is safe:

				
					SELECT NULL::bigint;

it does not evaluate to TRUE, so the row is not allowed through the RLS policy.

Demo

Step 1: Start YugabyteDB with YSQL Connection Manager

For a local yugabyted environment:

				
					./bin/yugabyted start \
  --tserver_flags="enable_ysql_conn_mgr=true" \
  --ui false

Connect over TCP:

				
					./bin/ysqlsh -h 127.0.0.1 -p 5433

Step 2: Verify YCM

Use this SHOW command: 
				
					SHOW yb_is_client_ysqlconnmgr;

Expected:

				
					.yb_is_client_ysqlconnmgr
--------------------------
 on
(1 row)

If the value is off, stop here. The session is not testing through YSQL Connection Manager.

Step 3: Create the Demo Objects

Run the following as an administrative user:

				
					DROP TABLE IF EXISTS tenant_orders CASCADE;
DROP ROLE IF EXISTS tenant_app;

CREATE ROLE tenant_app LOGIN;

CREATE TABLE tenant_orders (
    order_id     BIGINT PRIMARY KEY,
    tenant_id    BIGINT NOT NULL,
    order_detail TEXT NOT NULL
);

INSERT INTO tenant_orders VALUES
    (1, 101, 'Order A for Tenant 101'),
    (2, 101, 'Order B for Tenant 101'),
    (3, 102, 'Order A for Tenant 102');

ALTER TABLE tenant_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy
ON tenant_orders
FOR ALL
TO tenant_app
USING (
    tenant_id =
      NULLIF(current_setting('app.tenant_id', true), '')::bigint
)
WITH CHECK (
    tenant_id =
      NULLIF(current_setting('app.tenant_id', true), '')::bigint
);

GRANT SELECT, INSERT, UPDATE, DELETE
ON tenant_orders
TO tenant_app;
Why Use a Separate Application Role?

Do not test the RLS behavior as the table owner. Table owners normally bypass Row-Level Security. Using a separate tenant_app role ensures that the demo is actually exercising the RLS policy.

Step 4: Connect as the Application Role

Open a new connection through YCM:

				
					./bin/ysqlsh \
  -h 127.0.0.1 \
  -p 5433 \
  -U tenant_app \
  -d yugabyte

Verify YCM again:

				
					SHOW yb_is_client_ysqlconnmgr;

Expected:

				
					on

Step 5: Query as Tenant 101

Set the tenant context for the current transaction:

				
					BEGIN;

SET LOCAL app.tenant_id = '101';

SELECT *
FROM tenant_orders
ORDER BY order_id;

COMMIT;

Expected result:

				
					.order_id | tenant_id |      order_detail
----------+-----------+-------------------------
        1 |       101 | Order A for Tenant 101
        2 |       101 | Order B for Tenant 101
(2 rows)

Tenant 102 is hidden by RLS.

Step 6: Query as Tenant 102

Again, set the tenant context for the current transaction:

				
					BEGIN;

SET LOCAL app.tenant_id = '102';

SELECT *
FROM tenant_orders
ORDER BY order_id;

COMMIT;

Expected:

				
					.order_id | tenant_id |      order_detail
----------+-----------+-------------------------
        3 |       102 | Order A for Tenant 102
(1 row)

The same application user can therefore access different tenants without requiring a separate database user for every tenant.

Reproduce the Empty-String Problem

To demonstrate why the direct cast is unsafe, explicitly set the custom GUC to an empty string:

				
					SET app.tenant_id = '';

Now try the unsafe expression:

				
					SELECT current_setting('app.tenant_id')::bigint;

Expected:

				
					ERROR: invalid input syntax for type bigint: ""

This is the condition the safer expression is designed to handle.

Instead of an error, the result is NULL.

				
					SELECT *
FROM tenant_orders
ORDER BY order_id;

Expected:

				
					(0 rows)

The application does not receive an invalid bigint conversion error, and it does not gain access to another tenant’s rows.

Fail Closed Instead of Failing with an Exception

If the application does not establish a valid tenant context, converting the missing or empty value to NULL causes the RLS predicate to reject every row. This is generally safer than allowing a stale tenant context to determine access.

Verify WITH CHECK Protection

The same policy prevents tenant 101 from inserting a row belonging to tenant 102.

				
					BEGIN;

SET LOCAL app.tenant_id = '101';

INSERT INTO tenant_orders
VALUES (4, 102, 'This insert should fail');

The insert should be rejected by the RLS policy.

				
					ERROR:  new row violates row-level security policy for table "tenant_orders"

Clean up the failed transaction:

				
					ROLLBACK;

This is why the policy includes both:

				
					USING (...)

and:

				
					WITH CHECK (...)

USING controls which existing rows the tenant can access.

WITH CHECK controls which rows the tenant can create or modify.

SET or SET LOCAL?

A custom tenant context can be set with:

				
					SET app.tenant_id = '101';

The source material notes that a custom GUC does not itself make the Connection Manager connection sticky.

For request-oriented multi-tenant applications, however, SET LOCAL is usually the cleaner pattern:

				
					BEGIN;

SET LOCAL app.tenant_id = '101';

-- Application queries for tenant 101

COMMIT;

SET LOCAL limits the setting to the current transaction

Recommended Application Pattern
BEGIN; SET LOCAL app.tenant_id = '101';

-- All work for this tenant

COMMIT;

What About UUID Tenant IDs?

The same technique applies when the tenant identifier is a UUID.

For example:

				
					tenant_id =
  NULLIF(current_setting('app.tenant_id', true), '')::uuid

The important part is converting the missing or empty value to NULL before attempting the cast.

Invalid Values Still Fail

This pattern handles:

				
					NULL

and:

				
					''

It does not hide invalid application values.

For example:

				
					SET LOCAL app.tenant_id = 'not-a-number';

will still fail when cast to bigint.

That is generally useful because it indicates that the application supplied an invalid tenant identifier rather than simply failing to establish tenant context.

Final Takeaway

The fragile version of an RLS policy is:

				
					current_setting('app.tenant_id')::bigint

The safer version is:

				
					NULLIF(
  current_setting('app.tenant_id', true),
  ''
)::bigint

Combine that with:

				
					SET LOCAL app.tenant_id = '101';

and you get a straightforward multi-tenant pattern:

  • ● A valid tenant ID exposes only that tenant’s rows.
  • ● Tenant context can be scoped to the current transaction.
  • ● A missing tenant context fails closed.
  • ● An empty custom GUC does not cause a numeric type-casting error.
  • ● WITH CHECK prevents one tenant from inserting or updating another tenant’s rows.
  • ● YSQL Connection Manager can still pool connections without requiring a database user for every application tenant.

References

Reference Description
YSQL Connection Manager YugabyteDB documentation for YSQL Connection Manager.
PostgreSQL Configuration Settings Functions Documents current_setting() and its missing_ok argument.
PostgreSQL Row Security Policies Explains Row-Level Security policy behavior.
Am I Actually Using YSQL Connection Manager? (YCM) Shows how to verify that a YSQL session is actually connected through YCM.

Have Fun!

Every year I say, “This is the year I’m finally going to the Pennsylvania Renaissance Faire!” …and every year I don’t. 😂

So here we go again: If I’m still in Pittsburgh and haven’t made the move to Dallas yet, I’m going! 🏰⚔️🍗

Apparently it takes moving 1,200 miles away to finally motivate me to attend something in my own backyard. 😂