FK Constraint with ON DELETE SET DEFAULT cascading as table owner

[Brad Leupen <qcompson1@xxxxxxxxx>]

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