Study on Implementing Row Level Security in PostgreSQL

As an addition to Study on the implementation of business logic at the level of PostgreSQL stored functions и mostly for a detailed answer on comment.

The theoretical part is well described in the documentation PostgreSQL — Row protection policies. Below is a practical implementation of a small a specific business task - hiding deleted data. Etude dedicated to the realization Role model using RLS presented separately.

There is nothing new in the article, there is no hidden meaning and secret knowledge. Just a sketch about the practical implementation of a theoretical idea. If anyone is interested, read on. If you're not interested, don't waste your time.

Formulation of the problem

Without diving deep into the subject area, briefly, the task can be formulated as follows: there is a table realizing a certain business entity. Rows in the table can be deleted, but you cannot physically delete the rows, you need to hide them.

For it is said - “Do not delete anything, just rename. The internet stores EVERYTHING

Along the way, it is desirable not to rewrite existing stored functions that work with this entity.

To implement this concept, the table has an attribute is_deleted. Further, everything is simple - you need to make sure that the client can see only the lines in which the attribute is_deleted false. What is the mechanism used for? Row Level Security.

implementation

Create a separate role and schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Create a target table

CREATE TABLE repos.file
(
...
is_del BOOLEAN DEFAULT FALSE
);
CREATE SCHEMA repos

Turn on Row level security

ALTER TABLE repos.file  ENABLE ROW LEVEL SECURITY ;
CREATE POLICY file_invisible_deleted  ON repos.file FOR ALL TO dba_role USING ( NOT is_deleted );
GRANT ALL ON TABLE repos.file to dba_role ;
GRANT USAGE ON SCHEMA repos TO dba_role ;

Service function - deleting a row in a table

CREATE OR REPLACE repos.delete( curr_id repos.file.id%TYPE)
RETURNS integer AS $$
BEGIN
...
UPDATE repos.file
SET is_del = TRUE 
WHERE id = curr_id ; 
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

business function - deleting a document

CREATE OR REPLACE business_functions.deleteDoc( doc_for_delete JSON )
RETURNS JSON AS $$
BEGIN
...
PERFORM  repos.delete( doc_id ) ;
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

The results

The client deletes the document

SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );

After deletion, the client does not see the document

SELECT business_functions.getCFile"( (SELECT json_build_object( 'CId', 3 )) ) ;
-----------------
(0 rows)

But the document is not deleted in the database, only the attribute is changed is_del

psql -d my_db
SELECT  id, name , is_del FROM repos.file ;
id |  name  | is_del
--+---------+------------
 1 |  test_1 | t
(1 row)

What was required in the formulation of the problem.

Сonclusion

If the topic is of interest, in the next etude, you can show an example of the implementation of a role-based data access sharing model using Row Level Security.

Source: habr.com