I'm using RLS in a multi-tenancy model. No problems for the first several tables I enabled. Then I add RLS to a new table and suddenly I cannot insert a record to that table.
Here is a table that works:
CREATE TABLE wtr.adjustment (
id uuid UNIQUE NOT NULL DEFAULT uuid_generate_v1(),
created_at timestamp NOT NULL DEFAULT current_timestamp,
updated_at timestamp NOT NULL DEFAULT current_timestamp,
vendor_id uuid NOT NULL,
reporting_period_id uuid NOT NULL,
inventory_lot_id uuid NOT NULL,
adjustment_date date NOT NULL,
quantity_delta NUMERIC(50,2) NOT NULL,
adjustment_type wtr.adjustment_type NOT NULL,
comments text,
CONSTRAINT pk_adjustment PRIMARY KEY (id)
);
--||--
GRANT select, insert, update, delete ON TABLE wtr.adjustment TO wtr_user;
--||--
ALTER TABLE wtr.adjustment ADD CONSTRAINT fk_adjustment_vendor FOREIGN KEY ( vendor_id ) REFERENCES wtr.vendor( id );
--||--
ALTER TABLE wtr.adjustment ADD CONSTRAINT fk_adjustment_reporting_period FOREIGN KEY ( reporting_period_id ) REFERENCES wtr.reporting_period( id );
--||--
ALTER TABLE wtr.adjustment ADD CONSTRAINT fk_adjustment_inventory_lot FOREIGN KEY ( inventory_lot_id ) REFERENCES wtr.inventory_lot( id );
--||--
ALTER TABLE wtr.adjustment ENABLE ROW LEVEL SECURITY;
--||--
CREATE POLICY select_adjustment ON wtr.adjustment FOR SELECT
USING (vendor_id = wtr.current_vendor_id());
--||--
CREATE FUNCTION wtr.fn_timestamp_update_adjustment() RETURNS trigger AS $$
BEGIN
NEW.updated_at = current_timestamp;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
--||--
CREATE TRIGGER tg_timestamp_update_adjustment
BEFORE UPDATE ON wtr.adjustment
FOR EACH ROW
EXECUTE PROCEDURE wtr.fn_timestamp_update_adjustment();
--||--
and the associated function that works:
CREATE OR REPLACE FUNCTION wtr.build_adjustment(
_reporting_period_id uuid,
_inventory_lot_gov_id text,
_strain_name text,
_room_name text,
_inventory_type_name text,
_adjustment_date text,
_quantity_delta NUMERIC(50,2),
_adjustment_type wtr.adjustment_type,
_comments text
)
RETURNS wtr.adjustment as $$
DECLARE
_vendor_id uuid;
_inventory_lot wtr.inventory_lot;
_adjustment wtr.adjustment;
_inventory_type_id uuid;
BEGIN
_vendor_id := wtr.current_vendor_id();
_inventory_lot := wtr.find_or_build_existing_inventory_lot(
_strain_name,
_room_name,
_inventory_lot_gov_id,
_inventory_type_name
);
INSERT INTO wtr.adjustment(
vendor_id,
reporting_period_id,
inventory_lot_id,
adjustment_date,
quantity_delta,
adjustment_type,
comments
)
SELECT
_vendor_id,
_reporting_period_id,
_inventory_lot.id,
_adjustment_date::DATE,
_quantity_delta,
_adjustment_type,
_comments
RETURNING *
INTO _adjustment;
RETURN _adjustment;
END;
$$ language plpgsql strict security definer;
--||--
GRANT execute ON FUNCTION wtr.build_adjustment(
uuid,
text,
text,
text,
text,
text,
numeric,
wtr.adjustment_type,
text
) TO wtr_user;
here is the table that fails:
CREATE TABLE wtr.received_inventory_transfer (
id uuid UNIQUE NOT NULL DEFAULT uuid_generate_v1(),
created_at timestamp NOT NULL DEFAULT current_timestamp,
updated_at timestamp NOT NULL DEFAULT current_timestamp,
vendor_id uuid NOT NULL,
reporting_period_id uuid NOT NULL,
inventory_lot_id uuid NOT NULL,
transfer_date DATE NOT NULL,
quantity_received NUMERIC(50,2) NOT NULL,
CONSTRAINT pk_received_inventory_transfer PRIMARY KEY (id)
);
--||--
GRANT select, insert, update, delete ON TABLE wtr.received_inventory_transfer TO wtr_user;
--||--
ALTER TABLE wtr.received_inventory_transfer ADD CONSTRAINT fk_received_inventory_transfer_vendor FOREIGN KEY ( vendor_id ) REFERENCES wtr.vendor( id );
--||--
ALTER TABLE wtr.received_inventory_transfer ADD CONSTRAINT fk_received_inventory_transfer_reporting_period FOREIGN KEY ( reporting_period_id ) REFERENCES wtr.reporting_period( id );
--||--
ALTER TABLE wtr.received_inventory_transfer ADD CONSTRAINT fk_received_inventory_transfer_inventory_lot FOREIGN KEY ( inventory_lot_id ) REFERENCES wtr.inventory_lot( id );
--||--
ALTER TABLE wtr.received_inventory_transfer ENABLE ROW LEVEL SECURITY;
--||--
CREATE POLICY select_received_inventory_transfer ON wtr.received_inventory_transfer FOR SELECT USING (vendor_id = wtr.current_vendor_id());
--||--
CREATE FUNCTION wtr.fn_timestamp_update_received_inventory_transfer() RETURNS trigger AS $$
BEGIN
NEW.updated_at = current_timestamp;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
--||--
CREATE TRIGGER tg_timestamp_update_received_inventory_transfer
BEFORE UPDATE ON wtr.received_inventory_transfer
FOR EACH ROW
EXECUTE PROCEDURE wtr.fn_timestamp_update_received_inventory_transfer();
--||--
and the associated failing function:
CREATE OR REPLACE FUNCTION wtr.build_received_inventory_transfers(
_reporting_period_id uuid,
_transfer_date text
-- _received_inventory_transfers jsonb
)
RETURNS wtr.received_inventory_transfer as $$
DECLARE
_vendor_id uuid;
_inventory_lot wtr.inventory_lot;
_received_inventory_transfer_info jsonb;
_received_inventory_transfer wtr.received_inventory_transfer;
_quantity numeric(50,2);
BEGIN
_vendor_id := wtr.current_vendor_id();
-- this call is currently hard coded for debug purposes
_inventory_lot := wtr.find_or_build_existing_inventory_lot(
'tacos',
'N/A',
'1234123412341234',
'Hash'
);
-- again, this is hard-coded for debug purposes
RAISE EXCEPTION 'v: %, rp: %, il: %, td: %, q: %',
_vendor_id,
_reporting_period_id,
_inventory_lot.id,
_transfer_date::DATE,
20
;
-- this is the call that fails
INSERT INTO wtr.received_inventory_transfer(
vendor_id,
reporting_period_id,
inventory_lot_id,
transfer_date,
quantity_received
)
SELECT
_vendor_id,
_reporting_period_id,
_inventory_lot.id,
_transfer_date::DATE,
20
RETURNING *
INTO _received_inventory_transfer;
RETURN _received_inventory_transfer;
END;
$$ language plpgsql;
--||--
GRANT execute ON FUNCTION wtr.build_received_inventory_transfers(
uuid,
text
-- jsonb
) TO wtr_user;
the current_vendor_id function uses a jwt token claim that is passed in by the server, which is postgraphile.
At the insert statement, the call fails with:
new row violates row-level security policy for table
\"received_inventory_transfer\"",
This info is all that the logs will show me.
I think my real question is - how can I further debug RLS policy? If I only enable RLS, but create no select policy, I get the same failure.
