Row Level Secutity Implementation Study in PostgreSQL

As a supplement to the Etude on the implementation of business logic at the level of PostgreSQL stored functions and mainly for a detailed answer to the comment .



The theory is well described in the PostgreSQL documentation - Row Security Policies . Below is a practical implementation of a small specific business task - hiding deleted data. A study on the implementation of the Role Model using RLS is presented separately.

The article is nothing new, 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. Who is not interested - do not waste your time.

Formulation of the problem

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



For it is said - “Don't delete anything, just rename it. The Internet stores EVERYTHING "



Along the way, it is advisable not to rewrite the already existing stored functions working with this entity.



To implement this concept, the table has the is_deleted attribute . Then everything is simple - you need to make it so that the client can see only lines in which the is_deleted attribute is false. For what the Row Level Security mechanism is used .

Implementation

Create a separate role and schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Create the 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 - document deletion

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

results

Client deletes 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 has not been deleted in the database, only the is_del attribute has been changed

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

This is what was required in the formulation of the problem.

Outcome

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