Row level security

[Thomas Simpson]

Hi,

I'm trying to implement a system which requires row level security on 
some key data tables (most do not require RLS).  The data tables will 
grow substantially (rows likely > +100M/year - the system is > 80% data 
insert plus < 20% updates and by design, no deletes).

Some queries are likely to brush past many rows before being eliminated 
by the RLS policy, so I'm trying to find the most efficient way that 
does not compromise query times.  I also want to have a unified approach 
across all the RLS data to make the policy implementation as 
straightforward as possible too because I know there will be future 
expansion of the RLS rules.

My thought currently is that table inheritance could possibly be one way 
forward.  Specifically the base table holding just the RLS attributes, 
such as site group, site ID, customer group, customer ID as some initial 
examples (I expect company division, department may be future needs too).

With the RLS attributes on the base table, I can add future needs to 
that table and they automatically propagate to the child tables holding 
the RLS data.  Policies on the child tables can enforce row visibility 
based on session tokens assigned at login (a future problem avoided just 
now for simplicity).

I have a small prototype working, with the policy function comparing the 
columns (from the base table) to the user tokens to permit/deny row 
access.  This allows this to be as in-memory and hopefully as fast as 
possible as it avoids needing to do any lookups to other tables or 
anything more expensive than some 'permissionColumn IN 
listOfTokensHeldByTheSession' checks.

My concern is the base table will grow substantially faster than the 
child data tables as that receives a new row for every row inserted in 
any of the child tables, so could easily be +300M rows/year and this 
could become some performance fence.  Some of the child tables have a 
clear partition key available so inherited & partitioned is also 
appealing but could possibly amplify any performance issue further.

Does this approach sound viable or are there pitfalls or a different 
more recommended approach?

Thanks

Jim