Mam bazę danych PostgreSQL (9.4), która ogranicza dostęp do rekordów w zależności od bieżącego użytkownika i śledzi zmiany dokonane przez użytkownika. Osiąga się to poprzez widoki i wyzwalacze, i w większości działa to dobrze, ale mam problemy z widokami, które wymagają INSTEAD OFwyzwalaczy. Próbowałem zmniejszyć problem, ale z góry przepraszam, że to wciąż dość długo.
Sytuacja
Wszystkie połączenia z bazą danych są wykonywane z interfejsu internetowego za pomocą jednego konta dbweb. Po połączeniu rola jest zmieniana przez, SET ROLEaby odpowiadała osobie korzystającej z interfejsu internetowego, a wszystkie takie role należą do roli grupy dbuser. (Szczegółowe informacje znajdują się w tej odpowiedzi ). Załóżmy, że użytkownik jest alice.
Większość moich stolików jest umieszczonych w schemacie, do którego tu zadzwonię privatei do którego należę dbowner. Te tabele nie są bezpośrednio dostępne dbuser, ale pełnią inną rolę dbview. Na przykład:
SET SESSION AUTHORIZATION dbowner;
CREATE TABLE private.incident
(
incident_id serial PRIMARY KEY,
incident_name character varying NOT NULL,
incident_owner character varying NOT NULL
);
GRANT ALL ON TABLE private.incident TO dbview;
Dostępność określonych wierszy dla bieżącego użytkownika alicezależy od innych widoków. Uproszczony przykład (który można zmniejszyć, ale należy to zrobić w celu obsługi bardziej ogólnych przypadków) to:
-- Simplified case, but in principle could join multiple tables to determine allowed ids
CREATE OR REPLACE VIEW usr_incident AS
SELECT incident_id
FROM private.incident
WHERE incident_owner = current_user;
ALTER TABLE usr_incident
OWNER TO dbview;
Dostęp do wierszy jest następnie zapewniany przez widok, który jest dostępny dla dbuserról, takich jak alice:
CREATE OR REPLACE VIEW public.incident AS
SELECT incident.*
FROM private.incident
WHERE (incident_id IN ( SELECT incident_id
FROM usr_incident));
ALTER TABLE public.incident
OWNER TO dbview;
GRANT ALL ON TABLE public.incident TO dbuser;
Zauważ, że ponieważ w FROMklauzuli występuje tylko jedna relacja , tego rodzaju widok można aktualizować bez żadnych dodatkowych wyzwalaczy.
Do logowania istnieje inna tabela, która rejestruje, która tabela została zmieniona i kto ją zmienił. Wersja zredukowana to:
CREATE TABLE private.audit
(
audit_id serial PRIMATE KEY,
table_name text NOT NULL,
user_name text NOT NULL
);
GRANT INSERT ON TABLE private.audit TO dbuser;
Jest to wypełniane za pomocą wyzwalaczy umieszczonych w każdej relacji, którą chcę śledzić. Na przykład przykładem private.incidentograniczonym do samych wstawek jest:
CREATE OR REPLACE FUNCTION private.if_modified_func()
RETURNS trigger AS
$BODY$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO private.audit (table_name, user_name)
VALUES (tg_table_name::text, current_user::text);
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION private.if_modified_func() TO dbuser;
CREATE TRIGGER log_incident
AFTER INSERT ON private.incident
FOR EACH ROW
EXECUTE PROCEDURE private.if_modified_func();
Więc teraz, jeśli alicewstawi się public.incident, rekord ('incident','alice')pojawi się w audycie.
Problem
Takie podejście napotyka problemy, gdy widoki stają się bardziej skomplikowane i wymagają INSTEAD OFwyzwalaczy do obsługi wstawek.
Powiedzmy, że mam dwie relacje, na przykład reprezentujące podmioty zaangażowane w jakąś relację wiele do jednego:
CREATE TABLE private.driver
(
driver_id serial PRIMARY KEY,
driver_name text NOT NULL
);
GRANT ALL ON TABLE private.driver TO dbview;
CREATE TABLE private.vehicle
(
vehicle_id serial PRIMARY KEY,
incident_id integer REFERENCES private.incident,
make text NOT NULL,
model text NOT NULL,
driver_id integer NOT NULL REFERENCES private.driver
);
GRANT ALL ON TABLE private.vehicle TO dbview;
Załóżmy, że nie chcę ujawniać szczegółów innych niż nazwa private.driver, a więc mam widok, który łączy tabele i wyświetla elementy, które chcę ujawnić:
CREATE OR REPLACE VIEW public.vehicle AS
SELECT vehicle_id, make, model, driver_name
FROM private.driver
JOIN private.vehicle USING (driver_id)
WHERE (incident_id IN ( SELECT incident_id
FROM usr_incident));
ALTER TABLE public.vehicle OWNER TO dbview;
GRANT ALL ON TABLE public.vehicle TO dbuser;
Aby alicemóc wstawić do tego widoku, należy podać wyzwalacz, np .:
CREATE OR REPLACE FUNCTION vehicle_vw_insert()
RETURNS trigger AS
$BODY$
DECLARE did INTEGER;
BEGIN
INSERT INTO private.driver(driver_name) VALUES(NEW.driver_name) RETURNING driver_id INTO did;
INSERT INTO private.vehicle(make, model, driver_id) VALUES(NEW.make_id,NEW.model, did) RETURNING vehicle_id INTO NEW.vehicle_id;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION vehicle_vw_insert()
OWNER TO dbowner;
GRANT EXECUTE ON FUNCTION vehicle_vw_insert() TO dbuser;
CREATE TRIGGER vehicle_vw_insert_trig
INSTEAD OF INSERT ON public.vehicle
FOR EACH ROW
EXECUTE PROCEDURE vehicle_vw_insert();
Problem polega na tym, że SECURITY DEFINERopcja w funkcji wyzwalacza powoduje, że jest uruchamiany z current_userustawionym na dbowner, więc jeśli alicewstawi nowy rekord do widoku, odpowiedni wpis w private.auditrekordach będzie autorem dbowner.
Czy jest więc sposób na zachowanie current_user, bez zapewnienia dbuserroli grupy bezpośredniego dostępu do relacji w schemacie private?
Częściowe rozwiązanie
Jak sugeruje Craig, używanie reguł zamiast wyzwalaczy pozwala uniknąć zmiany current_user. W powyższym przykładzie zamiast wyzwalacza aktualizacji można użyć następujących elementów:
CREATE OR REPLACE RULE update_vehicle_view AS
ON UPDATE TO vehicle
DO INSTEAD
(
UPDATE private.vehicle
SET make = NEW.make,
model = NEW.model
WHERE vehicle_id = OLD.vehicle_id
AND (NEW.incident_id IN ( SELECT incident_id
FROM usr_incident));
UPDATE private.driver
SET driver_name = NEW.driver_name
FROM private.vehicle v
WHERE driver_id = v.driver_id
AND vehicle_id = OLD.vehicle_id
AND (NEW.incident_id IN ( SELECT incident_id
FROM usr_incident));
)
To zachowuje current_user. RETURNINGKlauzule wspierające mogą być jednak nieco owłosione. Co więcej, nie mogłem znaleźć bezpiecznego sposobu na użycie reguł do jednoczesnego wstawiania do obu tabel, aby obsłużyć użycie sekwencji dla driver_id. Najłatwiejszym sposobem byłoby użycie WITHklauzuli w INSERT(CTE), ale nie są one dozwolone w połączeniu z NEW(error rules cannot refer to NEW within WITH query:), pozostawiając do ucieczki się do tego, lastval()co jest zdecydowanie odradzane .
SET SESSIONmogłoby być jeszcze lepsze, ale myślę, że początkowy użytkownik musiałby mieć uprawnienia administratora, co pachnie niebezpiecznie.