Row level security - Update Rows-CodePudding

Hi I am working with Postgres, I have one role "my_role", and I want to update records from one table only where my corporate_id is related to other table.

I want to create a Policy to person table, and I have a corporate_id from my corporate table to drive to get this information would be something like these:

SELECT * FROM person p 
INNER JOIN person_brand a ON p.person_id=a.person_id 
INNER JOIN brand b ON a.brand_id=b.brand_id 
INNER JOIN corporate c on b.corporate_id=c.corporate_id
WHERE c.corporate_id=corporate_id

I my policy will be something like these:

ALTER TABLE core.person ENABLE ROW LEVEL SECURITY;
CREATE POLICY person_corporation_all
    ON person
    AS PERMISSIVE
    FOR UPDATE
    TO "my_role"
    USING (EXISTS(SELECT 1 FROM person p 
                  INNER JOIN person_brand a ON p.person_id=a.person_id 
                  INNER JOIN brand b ON a.brand_id=b.brand_id 
                  INNER JOIN corporate c on b.corporate_id=c.corporate_id
                 WHERE c.corporate_id=corporate_id));

But show me this error:

ERROR:  column reference "corporate_id" is ambiguous
SQL state: 42702

What I need to send as variable into my query?

Regards

CodePudding user response：

You will have a nested policy because inside the verification you have the person table again, you will need to remove it, and refers to the columns using the name of the table person, for example:

CREATE POLICY person_corporation_all
    ON person
    AS PERMISSIVE
    FOR UPDATE
    TO "my_role"
    USING (EXISTS(SELECT 1 FROM person_brand a 
                  INNER JOIN brand b ON a.brand_id=b.brand_id 
                  INNER JOIN corporate c on b.corporate_id=c.corporate_id
                 WHERE a.person_id=person.person_id and  c.corporate_id=person.corporate_id));