Re: FK Constraint with ON DELETE SET DEFAULT cascading as tableowner - Mailing list pgsql-general

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