Prevent self-referencing relation in SurrealDB

Question

I am trying to apply the table permissions system SurrealDB offers to enable row-level security in its tables, but am seeming to fail with a simple example:

DEFINE TABLE node SCHEMAFULL TYPE NORMAL;

DEFINE TABLE edge SCHEMAFULL TYPE RELATION IN node TO node
PERMISSIONS
    FOR create
        WHERE in != out;

CREATE node:foo;
RELATE node:foo->edge->node:foo; -- this shouldn't be possible

Testing this on SurrealDB v2.0.0-alpha.8 shows that it succeeds when I don't think it should - but I'm not sure if RELATE isn't necessarily the same thing as a create event, and that's why it succeeds.

Answer 1

One method that could work is defining each field (in and out) to assert that it isn't equal to the other (here $value means the field while $this means the parent table/record):

DEFINE FIELD in ON TABLE edge ASSERT $value != $this.out;
DEFINE FIELD out ON TABLE edge ASSERT $value != $this.in;

In 2.0.0 this will require using ALTER instead of DEFINE, as now DEFINE won't work on a table that already exists. ALTER was just merged last week for ALTER TABLE, with the rest of the syntax to soon follow. But I think in a short time you'll be able to do this.

ALTER FIELD in  ON TABLE edge ASSERT $value != $this.out;
ALTER FIELD out ON TABLE edge ASSERT $value != $this.in;