How to route read-only queries to a non-primary node while using Row-Level Security (RLS)?

I use pgpool in front of several CloudSQL instances (1 primary node, multiple replicas). Most clients only perform simple read-only requests (SELECT), which are perfectly load-balanced by pgpool using statement-level load balancing. Only a few clients perform write requests, which are encapsulated within transactions and routed to the primary node by pgpool.
Now, I want to use PostgreSQL Row-Level Security (RLS) and am wondering: What is the best approach to do so? I’ve tried several methods:

Transaction with role

BEGIN;
SET ROLE ... <- set the role for RLS
SELECT ... <- the actual query
COMMIT;

Transaction with set_config

BEGIN;
SELECT set_config ... <- set config for RLS
SELECT ... <- the actual query
COMMIT;

CTE with set_config

WITH ctx AS (SELECT set_config(...)) <- set config for RLS
SELECT ... FROM ..., ctx WHERE ...;

The problem with all approaches

A SET or set_config always causes the following statements to be routed to the primary node. Even with the CTE solution, which, in my understanding, should not be the case since set_config within a CTE is meant to apply only to the following SELECT statement.
The only solution I found to prevent pgpool from routing everything to the primary is to mark set_config as "read-only" using read_only_function_list or write_function_list.

Question

Can you advise on how to use pgpool with statement-level load balancing enabled alongside row-level security?

Environment

pgpool2 version: 4.7.0
PostgreSQL version: 18.1

In summary, there's no way to send the query to standbys except create a "rad only" function which does "SET" inside as you suggest.
In transaction cases, pgpool thinks SET as "writing query", and prevent load balancing subsequent SELECT until the transaction ends.
In the CTE case, firstly pgpool looks into the CTE subqyery (SELECT set_config... in above), temporarily decides it might be able to load balance, but a subsequent check looks in the whole query and finds set_config, which is a write query.

I wonder what if we allow to add a prefix comment like /FORCE LOAD BALANCE/ so that load balancing is forced for the query. Any better idea?

A special comment to enforce load balancing would probably do the trick.
Another idea: a transaction could be explicit marked as "READ ONLY" to route all requests within the transaction to a replica node.

Another idea: a transaction could be explicit marked as "READ ONLY" to route all requests within the transaction to a replica node.

Queries that can be executed in READ ONLY transaction are not equal to the queries that can be executed in replica. Queries that can be executed in replica is a subset of READ ONLY. For example, listen/notify can execute in READ ONLY but cannot execute in replica.

Navigation Menu

Repository navigation

How to route read-only queries to a non-primary node while using Row-Level Security (RLS)? #152

Transaction with role

Transaction with set_config

CTE with set_config

The problem with all approaches

Question

Environment

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

How to route read-only queries to a non-primary node while using Row-Level Security (RLS)? #152

Description

Transaction with role

Transaction with set_config

CTE with set_config

The problem with all approaches

Question

Environment

Activity

tatsuo-ishii commented on Mar 5, 2026

haininghu commented on Mar 5, 2026

tatsuo-ishii commented on Aug 25, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions