FK Constraint with ON DELETE SET DEFAULT cascading as table owner

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

FK Constraint with ON DELETE SET DEFAULT cascading as table owner

Brad Leupen

Hello,

 

We are using RLS on Postgres 11 to implement multi tenancy in our application. We have a tenant table whose id matches the tenant’s user role. Each table has a tenant_id FKA that defaults to “current_user”. All of our foreign key constraints are multipart (tenant_id + row_id). So far this works great except when we try to clean up FKA references on deletion. Here’s a script that demonstrates the issue in an empty database:

 

 

CREATE ROLE tenant1;

 

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO tenant1;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO tenant1;

 

CREATE TABLE tenant (

    id TEXT NOT NULL DEFAULT user PRIMARY KEY

);

 

CREATE TABLE foo (

    tenant TEXT REFERENCES tenant NOT NULL DEFAULT user,

    id SERIAL NOT NULL,

    default_bar INT,

    PRIMARY KEY (tenant, id)

);

 

CREATE TABLE bar (

    tenant TEXT REFERENCES tenant NOT NULL DEFAULT user,

    id SERIAL NOT NULL,

    foo_id INT,

    PRIMARY KEY (tenant, id),

    constraint foo FOREIGN KEY (tenant, foo_id) REFERENCES foo (tenant, id) ON DELETE CASCADE

);

 

ALTER TABLE foo ADD CONSTRAINT default_bar FOREIGN KEY (tenant, default_bar) REFERENCES bar (tenant, id) ON DELETE SET DEFAULT;

 

ALTER TABLE foo ENABLE ROW LEVEL SECURITY;

ALTER TABLE bar ENABLE ROW LEVEL SECURITY;

   

CREATE POLICY tenant_tenant on tenant USING (id = current_user) WITH CHECK (id = current_user);

CREATE POLICY foo_tenant on foo USING (tenant = current_user) WITH CHECK (tenant = current_user);

CREATE POLICY bar_tenant on bar USING (tenant = current_user) WITH CHECK (tenant = current_user);

 

SET ROLE tenant1;

 

INSERT INTO tenant DEFAULT VALUES;

INSERT INTO foo DEFAULT VALUES;

INSERT INTO bar ( foo_id ) (SELECT id FROM foo );

UPDATE foo SET default_bar = ( SELECT id FROM bar );

DELETE FROM bar;

 

This script winds up failing because the “user” default value on foo.tenant evaluates to the table owner, not the active user role of “tenant1”. Is this the expected behavior? The desired outcome, after deleting from bar, would be for foo’s tenant to remain “tenant1” and its default_bar value be set to null. Is there another, cleaner way to achieve this?

 

Thank you!

Brad

Reply | Threaded
Open this post in threaded view
|

Re: FK Constraint with ON DELETE SET DEFAULT cascading as table owner

rob stone-2
Hello,

On Fri, 2019-02-01 at 14:01 +0000, Brad Leupen wrote:

> Hello,
>  
> We are using RLS on Postgres 11 to implement multi tenancy in our
> application. We have a tenant table whose id matches the tenant’s
> user role. Each table has a tenant_id FKA that defaults to
> “current_user”. All of our foreign key constraints are multipart
> (tenant_id + row_id). So far this works great except when we try to
> clean up FKA references on deletion. Here’s a script that
> demonstrates the issue in an empty database:
>  
>  
> CREATE ROLE tenant1;
>  
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON
> TABLES TO tenant1;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON
> SEQUENCES TO tenant1;
>  
> CREATE TABLE tenant (
>     id TEXT NOT NULL DEFAULT user PRIMARY KEY
> );
>  
> CREATE TABLE foo (
>     tenant TEXT REFERENCES tenant NOT NULL DEFAULT user,
>     id SERIAL NOT NULL,
>     default_bar INT,
>     PRIMARY KEY (tenant, id)
> );
>  
> CREATE TABLE bar (
>     tenant TEXT REFERENCES tenant NOT NULL DEFAULT user,
>     id SERIAL NOT NULL,
>     foo_id INT,
>     PRIMARY KEY (tenant, id),
>     constraint foo FOREIGN KEY (tenant, foo_id) REFERENCES foo
> (tenant, id) ON DELETE CASCADE
> );
>  
> ALTER TABLE foo ADD CONSTRAINT default_bar FOREIGN KEY (tenant,
> default_bar) REFERENCES bar (tenant, id) ON DELETE SET DEFAULT;
>  
> ALTER TABLE foo ENABLE ROW LEVEL SECURITY;
> ALTER TABLE bar ENABLE ROW LEVEL SECURITY;
>    
> CREATE POLICY tenant_tenant on tenant USING (id = current_user) WITH
> CHECK (id = current_user);
> CREATE POLICY foo_tenant on foo USING (tenant = current_user) WITH
> CHECK (tenant = current_user);
> CREATE POLICY bar_tenant on bar USING (tenant = current_user) WITH
> CHECK (tenant = current_user);
>  
> SET ROLE tenant1;
>  
> INSERT INTO tenant DEFAULT VALUES;
> INSERT INTO foo DEFAULT VALUES;
> INSERT INTO bar ( foo_id ) (SELECT id FROM foo );
> UPDATE foo SET default_bar = ( SELECT id FROM bar );
> DELETE FROM bar;
>  
> This script winds up failing because the “user” default value on
> foo.tenant evaluates to the table owner, not the active user role of
> “tenant1”. Is this the expected behavior? The desired outcome, after
> deleting from bar, would be for foo’s tenant to remain “tenant1” and
> its default_bar value be set to null. Is there another, cleaner way
> to achieve this?
>  
> Thank you!
> Brad



I ran your script. Output:-

postgres 11.1 => select * from tenant;
   id    
---------
 tenant1
(1 row)

postgres 11.1 => select * from foo;
 tenant  | id | default_bar
---------+----+-------------
 tenant1 |  1 |           1
(1 row)

postgres 11.1 => select * from bar;
 tenant  | id | foo_id
---------+----+--------
 tenant1 |  1 |      1
(1 row)

postgres 11.1 => delete from bar;
ERROR:  insert or update on table "foo" violates foreign key constraint
"foo_tenant_fkey"
DETAIL:  Key is not present in table "tenant".
postgres 11.1 =>


Your foreign key constraint is defined as:-

default_bar ==> FOREIGN KEY (tenant, default_bar) REFERENCES
bar(tenant, id) ON DELETE SET DEFAULT

If you don't specify a "default" it uses NULL.
There is no tenant.id that is NULL.
So, the foreign key validation fails.

AFAICT, it is working as intended.

Cheers,
Robert



Reply | Threaded
Open this post in threaded view
|

FK Constraint with ON DELETE SET DEFAULT cascading as table owner

David G Johnston
On Friday, February 1, 2019, rob stone <[hidden email]> wrote:
Hello,

On Fri, 2019-02-01 at 14:01 +0000, Brad Leupen wrote:
> CREATE TABLE foo (
>     tenant TEXT REFERENCES tenant NOT NULL DEFAULT user,

> This script winds up failing because the “user” default value on
> foo.tenant evaluates to the table owner, not the active user role of
> “tenant1”. Is this the expected behavior? 

If you don't specify a "default" it uses NULL.
There is no tenant.id that is NULL.
So, the foreign key validation fails.

AFAICT, it is working as intended.

It working as intended but your analysis is wrong (for one, there is a default).

Cascading triggers use the role of the table owner instead of the statement executing role to handle various authorization configurations.  You may not be able leverage on delete/update here given your requirement.

David J.