Re: Row level security

Hello Jim,

Your approach of using table inheritance in PostgreSQL for implementing row-level security (RLS) has some interesting aspects, but there are potential pitfalls and alternatives that you should consider. Below, I'll outline some key points to

Table Inheritance and Performance Concerns

* RLS and Inheritance -  In PostgreSQL, RLS policies are applied per table. If you use inheritance, RLS policies defined on the parent table won’t automatically apply to the child tables. You’ll have to set up RLS policies on each child table separately.

* Growing Base Table  - The base table, getting a new row for every row inserted in the child tables, will grow really fast. Managing a table with hundreds of millions of rows per year could become a serious performance problem.

* Partitioning - Partitioning can help manage big tables by breaking them into smaller parts. But if your base table becomes a bottleneck, partitioning the child tables alone might not solve the problem.

Alternative Approach: Use Partitioned Tables Directly with RLS

Given your needs, here's a different approach that leverages PostgreSQL's partitioning and indexing features along with RLS:

* Directly Partitioned Tables - Instead of inheritance, create partitioned tables directly for each type of data. Partition these tables based on a logical key (like time, site ID, or customer ID) so each partition stays manageable:
Example:
     CREATE TABLE data (
         id SERIAL PRIMARY KEY,
         site_id INT,
         customer_id INT,
         division_id INT,
         department_id INT,
         data_payload JSONB,
         created_at TIMESTAMPTZ
     ) PARTITION BY RANGE (created_at);


* RLS Policies on Partitions - Set up RLS policies on each partition. Since partitions are smaller, RLS policy checks should be more efficient.
Example:
     CREATE POLICY rls_policy ON data
     USING (site_id = current_setting('app.current_site_id')::INT);
     ENABLE ROW LEVEL SECURITY;

* Session Variables - Using PostgreSQL session variables to store user-specific info (like app.current_site_id) is convenient, but has potential security risks. If a client can set these variables, they could manipulate them to gain unauthorized access. To mitigate this, ensure that only trusted parts of your application can set these variables. Consider using server-side functions or application logic to securely set these variables based on the authenticated user's information.
Example:
     SET app.current_site_id = '123';

* Indexing  - Make sure you index columns used in RLS policies and queries, like site_id and customer_id.
     CREATE INDEX idx_site_id ON data (site_id);
     CREATE INDEX idx_customer_id ON data (customer_id);
     CREATE INDEX idx_site_customer ON data (site_id, customer_id);
     CREATE INDEX idx_created_at ON data (created_at);

* Using Stored Procedures - using stored procedures can centralize security logic, but it can also add complexity. Here's a brief look:

Advantages:
   - Centralized security logic.
   - Additional layer of security as logic is hidden from end-users.
   - Can include data validation and business logic.

2Disadvantages:
   - Increased complexity in development and maintenance.
   - Potential performance overhead for complex procedures.
   - Less flexibility for ad-hoc queries.

Example:
   CREATE OR REPLACE FUNCTION insert_data(
       p_site_id INT,
       p_customer_id INT,
       p_division_id INT,
       p_department_id INT,
       p_data_payload JSONB,
       p_created_at TIMESTAMPTZ
   ) RETURNS VOID AS $$
   BEGIN
       IF current_setting('app.current_site_id')::INT = p_site_id THEN
           INSERT INTO data (site_id, customer_id, division_id, department_id, data_payload, created_at)
           VALUES (p_site_id, p_customer_id, p_division_id, p_department_id, p_data_payload, p_created_at);
       ELSE
           RAISE EXCEPTION 'Access Denied';
       END IF;
   END;
   $$ LANGUAGE plpgsql;

* Final Thoughts

Using direct partitioning and applying RLS policies to each partition should help with performance issues linked to a growing base table. This approach also keeps things flexible for future expansions and avoids the hassle of managing inheritance hierarchies. Proper indexing with RLS policies in mind can greatly improve query performance in large tables. Just make sure to handle session variables securely to avoid potential security issues.

If you have more questions or need further advice on implementation, just let me know!

Cheers,  
Andy

On 25-Jun-24 00:28, Thomas Simpson wrote: