A Study on Implementing Row Level Security in PostgreSQL

As a complement to A Study on Implementing Business Logic at the Level of Stored Functions in PostgreSQL and mainly for a detailed response to saying, "So how did it all end up?". In response to my detailed answer, I heard, "This deserves an article." Well, if it deserves, then there will be an article. Maybe someone will find it useful. From it, the reader will learn some facts about the design of QEMU code generation backends, as well as how to write a Just-in-Time compiler for a web application..

The theoretical part is well described in the documentation Postgres Pro — Row Protection Policies. Below, a practical implementation of a small specific business task — hiding deleted data. A study dedicated to the implementation of a Role Model Using RLS is presented separately.

The article contains nothing new, no hidden meanings or secret knowledge. It is simply a sketch about the practical implementation of a theoretical idea. If anyone is interested — read on. If not, don’t waste your time.

Task Definition

Without delving deeply into the subject area, briefly, the task can be formulated as follows: there is a table implementing a certain business entity. Rows in the table can be deleted, but they cannot be physically removed, they must be hidden.

For it is said — "Do not delete anything, only rename it. The internet stores EVERYTHING"

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

To implement this concept, the table has an attribute is_deleted. Next, it's simple — you need to ensure that the client can see only the rows where the attribute is_deleted is false. This is where the mechanism of Row Level Security is used.

Implementation

We create a separate role and schema

CREATE ROLE repos;
CREATE SCHEMA repos;

We create the target table

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

We enable 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 the 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;

Results

The client deletes the document

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

After deletion, the client cannot 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_del

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

Which is exactly what was required in the task.

Summary

If the topic is of interest, the next study can demonstrate an example of implementing a role-based access model using Row Level Security.

Source: habr.com