CODEWITHSUNDEEP
postgresqlrow-level-security
cgfm
cgfm

Reputation: 33

Postgresql Row Level Security Checking new data in WITH CHECK in Policy for INSERT

I've a Database with several tables. A user has several objects and an object has several parts.

I want to write a policy that only the creator of the object is allowed to add parts to the object. Therefore I need to get the object a to be inserted part belongs to, but I've no idea how to check the data.

Is there a way to get the data to be inserted in the policy?

Thanks for your effort.

Upvotes: 2

Views: 3971

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246338

Here is an example how to implement something like that with row level security. Adapt it to your need!

CREATE TABLE object(
   id integer PRIMARY KEY,
   name text NOT NULL,
   owner name NOT NULL DEFAULT current_user
);

CREATE TABLE part(
   id integer PRIMARY KEY,
   parent_id integer NOT NULL REFERENCES object(id),
   name text NOT NULL
);

We have to give people some permissions:

GRANT SELECT, INSERT ON object TO PUBLIC;
GRANT SELECT, INSERT ON part TO PUBLIC;

Now we enable row level security and allow only INSERTs in part when the owner in object matches:

ALTER TABLE part ENABLE ROW LEVEL SECURITY;

CREATE POLICY insert_only_owned ON part
   FOR INSERT TO PUBLIC
   WITH CHECK (EXISTS(
                  SELECT 1
                  FROM object o
                  WHERE o.id = parent_id
                    AND owner = current_user
              ));

Upvotes: 3

Related Questions