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
