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