Generate triggers to apply permissions in sqlite

[magnetised]

Example trigger sql:

-- example perms check for insert with project scoped table
CREATE TRIGGER "__electric_permissions__comments_INSERT"
    BEFORE INSERT ON "comments"
    FOR EACH ROW
    WHEN NOT (
        SELECT CASE
            WHEN (
                WITH __scope__ AS (
                    SELECT "projects"."id" AS pk0
                        FROM "projects"
                        LEFT JOIN "issues" ON
                            "issues"."project_id" = "projects"."id"
                        WHERE
                            "issues"."id" = NEW."issue_id"
                        LIMIT 1
                ) SELECT CASE
                    WHEN (
                        WITH __tomb__ AS (
                            SELECT row_id FROM __electric_local_roles_tombstone
                                WHERE assign_id IS 'assign-1'
                        )
                        SELECT (
                            (
                                ('p1') = (SELECT "pk0" FROM __scope__)
                                AND ('["pm1"]' NOT IN (SELECT row_id FROM __tomb__))
                            )
                        )
                    ) THEN TRUE
                    WHEN (
                        SELECT 1 FROM "__electric_local_roles"
                            WHERE (scope = 'projects')
                            AND (scope_id = (SELECT json_array("pk0") FROM __scope__))
                            AND (role = 'manager')
                    ) THEN TRUE
                    ELSE FALSE
                END
            ) THEN TRUE
            ELSE FALSE
        END
    )
BEGIN
    SELECT RAISE(ROLLBACK, 'does not have matching INSERT permissions on "comments"');
END;

-- validate scope moves
CREATE TRIGGER "__electric_permissions__comments_UPDATE_scope_move"
    BEFORE UPDATE OF "issue_id" ON "comments"
    FOR EACH ROW
    WHEN NOT (
        WITH
             __scope__ AS (
                SELECT "projects"."id" AS pk0
                    FROM "projects"
                    LEFT JOIN "issues" ON
                        "issues"."project_id" = "projects"."id"
                    WHERE
                        "issues"."id" = NEW."issue_id"
                    LIMIT 1
            ),
            "__tomb__assign-1" AS (
                SELECT row_id FROM __electric_local_roles_tombstone WHERE assign_id IS 'assign-1'
            )
        SELECT CASE
            WHEN (
                (('p1') = (SELECT pk0 FROM __scope__))
                AND ('["pm1"]' NOT IN (SELECT row_id FROM "__tomb__assign-1"))
            ) THEN TRUE
            WHEN (
                SELECT 1 FROM "__electric_local_roles"
                    WHERE (scope = 'projects')
                    AND (scope_id = (SELECT json_array("pk0") FROM __scope__))
                    AND (role = 'manager')
            ) THEN TRUE
            ELSE FALSE
        END
    )
BEGIN
    SELECT RAISE(ROLLBACK, 'does not have matching UPDATE permissions in new scope on "comments"');
END;

-- reject updates to table pk

CREATE TRIGGER "__electric_permissions__comments_UPDATE_protect_pk"
    BEFORE UPDATE OF "id" ON "comments"
    FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'invalid update of primary key on "comments"');
END;

-- local role creation based on assigns

CREATE TRIGGER "__electric_permissions__project_memberships_INSERT_assign_assign-1_0"
    BEFORE INSERT ON "project_memberships"
    FOR EACH ROW WHEN (
        (NEW."user_id" IS '92bafe18-a818-4a3f-874f-590324140478')
    )
BEGIN
    INSERT INTO "__electric_local_roles"
        (assign_id, row_id, scope, scope_id, role)
    VALUES (
        'assign-1',
        json_array(NEW."id"),
        'projects',
        json_array(NEW."project_id"),
        NEW."role"
    );
END;

[linear[bot]]

VAX-1713 Experiment with implementing permissions as SQLite triggers

[KyleAMathews]

👋 we've been working the last month on a rebuild of the Electric server over at a temporary repo https://github.com/electric-sql/electric-next/

You can read more about why we made the decision at https://next.electric-sql.com/about

We're really excited about all the new possibilities the new server brings and we hope you'll check it out soon and give us your feedback.

We're now moving the temporary repo back here. As part of that migration we're closing all the old issues and PRs. We really appreciate you taking the time to investigate and create this improvement!