I've recently been interested in whether PostgreSQL's row security policies could be a good fit for a few different projects. Before committing too strongly to using them someplace important I figured I'd try them out to understand the potential implications to query performance and ergonomics.
Row level security is a mechanism to gate access to data based on policies declared on tables. That is a bit vague because it seems like you can actually do quite a lot with policies. I do not have any kind of grand vision for policy enforcement beyond minimizing the chance that developers may write queries that unintentionally return or access more data than should be allowed or makes sense. As a minimal example I'll create just three tables for some hand-wavey IoT-like thing. We'll have tenants, devices, and readings from devices.
create table tenant (
id integer primary key generated always as identity,
name text
);
create table device (
id integer primary key generated always as identity,
serial_number text,
tenant_id integer references tenant
);
create table reading (
recorded_at timestamptz,
data jsonb,
device_id integer references device,
tenant_id integer references tenant
);I'm not looking for any great volume of data so I'll populate a handful of devices and tenants by hand:
insert into tenant(name) values ('alpha'), ('bravo'), ('charlie'), ('delta');
insert into device(tenant_id, serial_number) values
(1, 'abcd123'),
(1, 'bcde234'),
(2, 'cdef345'),
(3, 'defg456');
Readings though I don't want to do manually as that sounds less than
fun. I do this kind of thing pretty often when performance testing
things so I'll just reach for a pattern I have handy for structuring
an object using jsonb_build_object along with lots of calls to
random and round.
insert into reading (recorded_at, data, device_id, tenant_id)
select
now() - (interval '1 hour' * gs.h) AS recorded_at,
jsonb_build_object(
'temperature_c', round((random() * 100)::numeric, 2),
'humidity_pct', round((random() * 40)::numeric, 2),
'battery_mv', round((random() * 5000)::numeric, 0),
'rssi_dbm', round((-90 + random() * 50)::numeric, 0),
'status', (ARRAY['ok', 'ok', 'ok', 'warn', 'fault'])[floor(random() * 5 + 1)::int]
) as data,
device.id AS device_id,
device.tenant_id
from device
cross join generate_series(0, 167) as gs(h);The produces 24 * 7 entries per device like this:
test=# select * from reading limit 1;
recorded_at | data | device_id | tenant_id
-------------------------------+---------------------------------------------------------------------------------------------------------+-----------+-----------
2026-02-21 15:24:46.352503+00 | {"status": "fault", "rssi_dbm": -77, "battery_mv": 3348, "humidity_pct": 62.82, "temperature_c": 21.00} | 1 | 1
(1 row)
I am surprised at how many times I have found use for "random value
from list of values" construct. Here I'm using repeats to weight the
values more toward "ok". As I was writing this out I went looking
for a way to encapsulate it into a named function. This is the sort
of thing I really miss when working in plain SQLite, that and the
type system which allows for this kind of polymorphic function using
anyarray
and anyelement:
create function pick(anyarray)
returns anyelement
language sql
as $$
select $1[floor(random() * array_length($1, 1) + 1)::int];
$$;
That feels like a tidy little helper function I'll be coming back for:
test=# select pick('{ok, ok, not ok, maybe ok}'::text[]);
pick
----------
maybe ok
(1 row)
Now on to the new stuff. My database up to now doesn't have any
roles or policies to gate access to data. Executing a query
like select * from devices will produce a list of all
the devices, as expected. If you figure "one customer or tenant
shouldn't be able to see another tenant's devices" then you have to
qualify the query with where tenant_id = ?. That isn't
difficult but carrying that across every single query and keeping
that logic across every table is error prone. Instead I'd like to
express this policy on the tables themselves:
create policy tenant_isolation on device
using (current_setting('app.some_id_var', True)::int = tenant_id);
This policy says that a row of the device table must have
a tenant_id value that matches the integer value of a
"current setting" that I've named "some_id_var". This is a little
mysterious but "current setting" is simply a kind of namespace where
explicit calls to "SET" are made into. It is the same system that
can answer for things like database settings, e.g.
test=# select current_setting('work_mem');
current_setting
-----------------
4MB
(1 row)
Because there are already things in the current settings I have to qualify the setting name with a name, here I'm calling it "app" but it can just as easily be something else. I'm not yet going to get into how or where this value may come from in practice. For now it is a variable that I can set and can be gated to things like connections, transactions, etc.
Policies like this don't apply by default to the owner of the table or administrators. I'd like to keep that so I'll make a new role which will be subject to the policies while still having access to the data (the role needs to be able perform queries!):
create role some_app;
grant select, insert, update, delete on tenant, device, reading to public;
alter table tenant enable row level security;
alter table device enable row level security;
alter table reading enable row level security;One thing that is a little too surprising is the error message in the case the variable carrying the tenant ID isn't set:
test=> set role some_app;
SET
test=> select * from tenant;
ERROR: invalid input syntax for type integer: ""
This seems to be a side effect of how I've been testing and how the
policy is written. I have been invoking set and reset to clear the
value, or it is automatically cleared when using set local. When it
is cleared the value of current_setting is actually the empty string
'' which leads to a bad cast like this:
test=> select ''::int;
ERROR: invalid input syntax for type integer: ""
LINE 1: select ''::int;
^create policy tenant_isolation on tenant
using (nullif(current_setting('app.some_id_var', True), '')::int = tenant_id);
create policy tenant_isolation on device
using (nullif(current_setting('app.some_id_var', True), '')::int = tenant_id);
create policy tenant_isolation on reading
using (nullif(current_setting('app.some_id_var', True), '')::int = tenant_id);Which produces null if the current setting is the empty string because casting a null to int correctly returns null instead of producing an error.
test=> set role = some_app;
SET
test=> set app.some_id_var = 1;
SET
test=> select *
from reading
join device on reading.device_id = device.id
order by recorded_at;
recorded_at | data | device_id | tenant_id | id | serial_number | tenant_id
-------------------------------+---------------------------------------------------------------------------------------------------------+-----------+-----------+----+---------------+-----------
2026-02-14 16:24:46.352503+00 | {"status": "ok", "rssi_dbm": -49, "battery_mv": 3281, "humidity_pct": 67.91, "temperature_c": 18.48} | 1 | 1 | 1 | abcd123 | 1
2026-02-14 16:24:46.352503+00 | {"status": "ok", "rssi_dbm": -43, "battery_mv": 3201, "humidity_pct": 78.78, "temperature_c": 23.42} | 2 | 1 | 2 | bcde234 | 1
2026-02-14 17:24:46.352503+00 | {"status": "fault", "rssi_dbm": -88, "battery_mv": 3382, "humidity_pct": 76.40, "temperature_c": 19.44} | 2 | 1 | 2 | bcde234 | 1
2026-02-14 17:24:46.352503+00 | {"status": "fault", "rssi_dbm": -88, "battery_mv": 3545, "humidity_pct": 59.33, "temperature_c": 18.19} | 1 | 1 | 1 | abcd123 | 1
2026-02-14 18:24:46.352503+00 | {"status": "fault", "rssi_dbm": -84, "battery_mv": 3210, "humidity_pct": 72.02, "temperature_c": 18.36} | 1 | 1 | 1 | abcd123 | 1
2026-02-14 18:24:46.352503+00 | {"status": "fault", "rssi_dbm": -84, "battery_mv": 3382, "humidity_pct": 62.92, "temperature_c": 22.78} | 2 | 1 | 2 | bcde234 | 1
2026-02-14 19:24:46.352503+00 | {"status": "warn", "rssi_dbm": -52, "battery_mv": 3149, "humidity_pct": 78.44, "temperature_c": 20.72} | 1 | 1 | 1 | abcd123 | 1
2026-02-14 19:24:46.352503+00 | {"status": "ok", "rssi_dbm": -63, "battery_mv": 3291, "humidity_pct": 57.28, "temperature_c": 30.86} | 2 | 1 | 2 | bcde234 | 1
2026-02-14 20:24:46.352503+00 | {"status": "fault", "rssi_dbm": -47, "battery_mv": 3289, "humidity_pct": 67.57, "temperature_c": 24.60} | 1 | 1 | 1 | abcd123 | 1
2026-02-14 20:24:46.352503+00 | {"status": "warn", "rssi_dbm": -60, "battery_mv": 3542, "humidity_pct": 51.36, "temperature_c": 31.41} | 2 | 1 | 2 | bcde234 | 1
...
This is a really pleasant way to limit visibility to the reading data without qualifying "where tenant_id = 1" and then remembering not to reintroduce data with an errant join. I believe the way this works is the query planner gains the qualifier for "where tenant_id = 1" implicitly and can use it in some pretty typical optimizations. To test that idea out I'll add a few indexes to try inclining the optimizer to doing something smarter than full scans:
create index idx_device_tenant on device(tenant_id, id);
create index idx_reading_tenant on reading(tenant_id, device_id);Now looking at a query plan for the above query shows how the policy was incorporated by the query planner:
explain (analyze) select * from reading join device on reading.device_id = device.id ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=5.02..25.05 rows=84 width=195) (actual time=0.060..0.238 rows=336.00 loops=1)
Buffers: shared hit=35
-> Seq Scan on device (cost=0.00..1.09 rows=1 width=40) (actual time=0.017..0.020 rows=2.00 loops=1)
Filter: (tenant_id = (NULLIF(current_setting('app.some_id_var'::text, true), ''::text))::integer)
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Bitmap Heap Scan on reading (cost=5.02..23.12 rows=84 width=155) (actual time=0.025..0.063 rows=168.00 loops=2)
Recheck Cond: ((tenant_id = (NULLIF(current_setting('app.some_id_var'::text, true), ''::text))::integer) AND (device_id = device.id))
Heap Blocks: exact=32
Buffers: shared hit=34
-> Bitmap Index Scan on idx_reading_tenant (cost=0.00..5.00 rows=84 width=0) (actual time=0.015..0.015 rows=168.00 loops=2)
Index Cond: ((tenant_id = (NULLIF(current_setting('app.some_id_var'::text, true), ''::text))::integer) AND (device_id = device.id))
Index Searches: 2
Buffers: shared hit=2
Planning Time: 0.353 ms
Execution Time: 0.299 msI confirmed the same plan on the table owner role (without row level security) when writing the query with explicit where clauses. That is a nice match to my expectation for how to factor this into any query writing.
The example of a single tenant ID is useful but doesn't match how I would necessarily expect some "tenancy" concepts to be expressed in production settings. Instead I'd like to explore a case of multiple tenant IDs being valid at once. In order to evaluate that kind of design I need a policy that can express such a thing.
create policy multiple_tenant_isolation on reading
using (tenant_id = any(current_setting('app.some_id_var', True)::int[]));
set app.some_id_var = '{1,2}';
This is getting closer, obviously you've got to drop the other policies before putting this one in because the "some_id_var" can't be both an integer and integer array. I haven't yet worked out how I intend to pass (or source!) tenant ID information but whether it is passing a Postgres array, jsonb array, or something really rudimentary like a comma separated string I think it should be pretty easy to construct it in just about any client, for example python.