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

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