Time Travel, Row Level Security and Dynamic views
A core component of an MDM system is the ability to time travel. Seeing how the state of data has
changed between iterations. This is achieved in postgres by changing the default behavior of
crud operations using views, indexes and triggers. User information are contained in JWT tokens. User
credentials are dynamically injected into dynamic views to create the proper RLS restraints. Below is an
example of creating RLS and as of queries with the provider_effort table. This approach is added to each
table individually.
--base table structure for storing all effort data
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE provider_effort
(
id big serial, --record id should not be changed
"valid" tstzrange, --maintains record version
user_id big integer, --user_id in this context should be a provider
effective_date date, --when the cfte values should take precedence
cfte_def_id big integer --the cfte definition id from the cfte_definitions table
cfte json, --contains the cfte information.
last_modified_by big integer --the user who made the last change
EXCLUDE USING gist (id WITH =, valid WITH &&)
);
/*
EXCLUDE USING gist (id WITH =, valid WITH &&)
EXCLUDE: Indicates that an exclusion constraint is being defined.
USING gist: Specifies that the constraint will use a GiST index.
(id WITH =, valid WITH &&): Defines the exclusion condition. In this case, it's a combination of two conditions:
id WITH =: Specifies that the id column values must be equal for the rows being compared.
valid WITH &&: Specifies that the valid column values must have overlapping ranges. The && operator is the "overlaps" operator for range types.
*/
--the current valid cfte records
CREATE VIEW current_provider_effort as
SELECT user_id, effective_date, cfte_def_id, cfte, last_modified_by
FROM provider_effort
WHERE current_timestamp <@ valid
/*
The version trigger overwrites the basic insert, update and delete operations inorder to maitain
the provider_effort historical record.
*/
CREATE FUNCTION version_trigger() RETURNS trigger AS
$$
BEGIN
IF TG_OP = 'UPDATE'
THEN
IF NEW.id <> OLD.id
THEN
RAISE EXCEPTION 'the ID must not be changed';
END IF;
UPDATE provider_effort
SET valid = tstzrange(lower(valid), current_timestamp)
WHERE id = NEW.id
AND current_timestamp <@ valid;
IF NOT FOUND THEN
RETURN NULL;
END IF;
END IF;
IF TG_OP IN ('INSERT', 'UPDATE')
THEN
INSERT INTO provider_effort (id, valid, user_id, effective_date, cfte_def_id, cfte, last_modified_by)
VALUES (NEW.id,
tstzrange(current_timestamp, TIMESTAMPTZ 'infinity'),
NEW.user_id,
NEW.effective_date,
NEW.cfte_def_id,
NEW.cfte,
NEW.last_modified_by
);
RETURN NEW;
END IF;
IF TG_OP = 'DELETE'
THEN
UPDATE provider_effort
SET valid = tstzrange(lower(valid), current_timestamp)
WHERE id = OLD.id
AND current_timestamp <@ valid;
IF FOUND THEN
RETURN OLD;
ELSE
RETURN NULL;
END IF;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER vtrigger
INSTEAD OF INSERT OR UPDATE OR DELETE
ON current_effort
FOR EACH ROW
EXECUTE PROCEDURE version_trigger();
/*
Using the current_settings overload the view and transaction below provides a method to view
the cfte records at any given point in time.
*/
CREATE VIEW as_of_provider_effort AS
SELECT user_id, effective_date, cfte_def_id, cfte, last_modified_by
FROM provider_effort
WHERE current_setting('app.as_of_time')::timestamptz <@ valid;
--Run Query
BEGIN;
SET app.as_of_time = {datetime value};
SELECT * FROM as_of_provider_effort;
COMMIT;
/*
Updatable views provide a way to cleanly enforce column and row level security. Any data modification that
invalidated the where condition will be rejected. This has three conditional change requirements
1.) The user is a manager of a provider. The user was a manager of a department when the provider was employed by
the department. The effective date used falls withing that overlap.
2.) The user modifiying the data is the provider
3.) The user is an admin.
*/
CREATE VIEW user_department as
SELECT * FROM department_employees
WHERE user_id = current_setting('app.user_id');
CREATE VIEW provider_effort_user_perms
SELECT * FROM current_effort
WHERE
(
EXISTS
(
--provider in users department
SELECT 1
FROM users
WHERE current_effort.provider_id = users.id
AND users.is_provider = true
AND current_effort.provider_id IN
(SELECT user_id
FROM department_employees
WHERE department_id IN (select department_id FROM user_department )
AND current_effort.effective_date BETWEEN department_employees.start_date AND department_employees.end_date
)
)
AND
EXISTS (
--is manager
SELECT 1
FROM users
WHERE users.id = current_setting('app.user_id') AND users.is_manager = true
)
) OR
EXISTS (
--user is provider
SELECT 1
FROM users
WHERE users.id = provider_id AND users.id = current_setting('app.user_id')
) OR
current_setting('app.user_is_admin') = true
--enforces rls permissions on all modification queries
BEGIN;
SET app.user_id = {user_id};
--crud_statement i.e. select/insert/update/delete--
COMMIT;
