Bug: Table Permissions Doesn't Seem To Be Working With Embedded Select Statement

[ReziaBanks]

Describe the bug

I'm encountering an issue while configuring security rules for the like table in my database, which consists of three tables: user, post, and like. My goal is to restrict the creation and deletion of likes to the user who created them, utilizing Auth0 for authentication.

To achieve this, I've set up a function that retrieves the current user by fetching the first user from the user table. The user table contains permissions that are determined based on data retrieved from $token.

DEFINE TABLE user SCHEMAFULL TYPE NORMAL
  PERMISSIONS FOR select, update, delete, create
  WHERE
    $scope = "user"
    AND $token.aud CONTAINS "<YOUR_AUTH0_AUDIENCE_VALUE>"
    AND $token.aud CONTAINS "https://<YOUR_AUTH0_DOMAIN>/userinfo"
    AND sub = $token.sub;

In defining the like table, I've specified a FOR CREATE, UPDATE statement with a WHERE clause, intending to restrict access to the creator of the like using fn::current::user::id(), representing the current user's ID. However, this logic doesn't seem to function as expected, and I'm uncertain about the reason behind it.

DEFINE TABLE like SCHEMAFULL
    TYPE RELATION
        IN user
        OUT post
    PERMISSIONS
        FOR SELECT
            WHERE TRUE
        FOR CREATE, DELETE
            WHERE in = (fn::current::user::id());

As part of troubleshooting, I attempted to directly pass the user record instead of fn::current::user::id(), which seemed to resolve the issue. This led me to conclude that the WHERE statement in the permission configuration does not accept an embedded SELECT statement.

For instance, when I replaced the condition with FOR CREATE WHERE in = (SELECT VALUE id FROM ONLY user LIMIT 1), the security rule failed to function as intended.

Steps to reproduce

-- FOLLOW THE GUIDE BELOW TO SETUP AUTH0
-- https://surrealdb.com/docs/surrealdb/tutorials/integrate-auth0-as-authentication-provider

-- YOU SHOULD BE ABLE TO ACHIEVE THE SAME RESULT WHEN USING NORMAL EMAIL & PASSWORD AUTHENTICATION
-- INSTEAD OF USING THE AVAILABLE $auth PARAM, SWITCH TO A FUNCTION SIMILAR TO MINE.

DEFINE TABLE user SCHEMAFULL TYPE NORMAL
  PERMISSIONS FOR select, update, delete, create
  WHERE
    $scope = "user"
    AND $token.aud CONTAINS "<YOUR_AUTH0_AUDIENCE_VALUE>"
    AND $token.aud CONTAINS "https://<YOUR_AUTH0_DOMAIN>/userinfo"
    AND sub = $token.sub;

-- MADE PERMISSIONS FULL AS THIS IS NOT THE AREA OF CONCERN
DEFINE TABLE post SCHEMAFULL TYPE NORMAL PERMISSIONS FULL;
DEFINE FIELD text ON TABLE post TYPE string;
DEFINE FIELD creator ON TABLE post TYPE record<user>;
DEFINE FIELD posted_at ON TABLE post TYPE datetime DEFAULT time::now() READONLY;

DEFINE TABLE like SCHEMAFULL
    TYPE RELATION
        IN user
        OUT post
    PERMISSIONS
        FOR SELECT
            WHERE TRUE
        FOR CREATE, DELETE
            WHERE in = (fn::current::user::id());
DEFINE FIELD liked_at ON TABLE like TYPE datetime DEFAULT time::now() READONLY; 

DEFINE FUNCTION fn::current::user::id() {
    IF $scope != 'user' {
        THROW 'Can only be accessed by a scoped user.';
    };
    RETURN (SELECT VALUE id FROM ONLY user LIMIT 1);
};

-- THIS STATEMENT SHOULD RUN BUT IT DOESN'T
-- ASSUMING post:7831 IS AN AVAILABLE RECORD
RELATE (fn::current::user::id())->like->post:7831;

Expected behaviour

The RELATE (fn::current::user::id())->like->post:7831 statement should run successfully.

SurrealDB version

1.4.2

Contact Details

reziafelixjnr@gmail.com

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[gguillemas]

Hi @ReziaBanks! Thank you for taking the time to write a detailed report for this issue you are experiencing. I will try to reproduce your scenario whenever I have a moment. For the time being, may I ask why not use FOR CREATE, DELETE WHERE $scope = 'user' AND in = $auth.id? Using $auth.id is the default way of accessing the record identifier of the authenticated user. Is there a particular reason why you prefer to rely on a custom function for that?

[ReziaBanks]

I'm using Auth0 as my Authentication Provider, therefore the $auth variable returns NONE. Snippet from the Auth0 implementation tutorial.

It is also important to note that the $auth variable accessible from SurrealQL will not contain any values in this case, as it requires the id claim to be added to the JWT, containing the value of the identifier of a SurrealDB record. For the current example, the $auth variable will not be necessary.

[gguillemas]

Got it! I missed that detail when reading the issue.

I understand you approach. You are relying on the permissions clause for SELECT only allowing the user to query their own record in order to retrieve only their user with ONLY and LIMIT 1 in the function.

Unfortunately, the current behavior in SurrealDB (#2161) leads to permissions clauses being able to read any data, causing your statement to simply return the first user record instead of the record belonging to the authenticated user. We are discussing this behavior as well as some related bugs in the context of a security issue in GHSA-9722-9j67-vjcr, and a fix that may change this behavior is currently being developed by the team responsible.

Thank you again for reporting this, I will keep this issue open and update it when this is resolved.

For the time being, a potential alternative could be to use another unique user identifier already present on the token (e.g. email) to use internally as either a custom record identifier for user or just as a normal field of the user record that is also present in post and like in order to allow the relation.

---

For future reference, here is a simplified scenario that reproduces this issue:

USE NS test DB test;
DEFINE TABLE user PERMISSIONS FOR SELECT WHERE name = $auth.name;
DEFINE TABLE post PERMISSIONS FOR SELECT WHERE user = (SELECT id FROM ONLY user LIMIT 1).id;
CREATE user:a CONTENT {"name": "a"};
CREATE user:b CONTENT {"name": "b"};
CREATE post:a CONTENT {"user": user:a};
CREATE post:b CONTENT {"user": user:b};
DEFINE SCOPE user
  SESSION 1d
  SIGNIN (SELECT * FROM user WHERE name = $name)
  SIGNUP (CREATE user CONTENT {"name": $name})
;

Then, I sign in as user:a and run the following:

SELECT * FROM post;
[
	{
		id: post:a,
		user: user:a
	}
]

I get the correct post for that user, great!
Then, I sign in as user:b and run the following:

SELECT * FROM post;
[
	{
		id: post:a,
		user: user:a
	}
]

I get the same post. It seems that the SELECT id FROM ONLY user LIMIT 1 statement is always returning the first user.
To test this hypothesis, we create a new user with a numeric identifier and their own post from a root session:

CREATE user:1 CONTENT {"name": 1};
[[{ id: user:1, name: 1 }]]
CREATE post:1 CONTENT {"user": user:1};
[[{ id: post:1, user: user:1 }]]

If our hypothesis is correct, selecting posts as any scope user should now return the post of user:1.
We sign in again as user:a and run the following:

SELECT * FROM post;
[
	{
		id: post:1,
		user: user:1
	}
]

We sign in again as user:b and run the following:

SELECT * FROM post;
[
	{
		id: post:1,
		user: user:1
	}
]

It seems that our hypothesis is correct. The permissions clause has access to all users and will always return the first one.

[ReziaBanks]

Thanks for the breakdown, adding a WHERE clause to the CURRENT USER function solved the issue.

I think it would be helpful to add context (doesn't use scope permission in a scoped connection) on PERMISSIONS in the documentation.

[gguillemas]

I agree that this behavior should be documented. Since we are currently in the process of redefining exactly how we want PERMISSIONS clauses to behave, I will make sure that the outcome is documented whenever a decision is made 👍

Thank you again for opening this issue!