Permify
Powerful get all SQL filtering
Is your feature request related to a problem? Please describe.
Current version of version enables resource authorisation management based on resource ID. This is perfectly fine for API requests having ID like a Get, Add, Edit, Delete. However, it's more difficult for a kind of "get all" API query. In that case, you don't know resource IDs before fetching them from DB.
Describe the solution you'd like
First (bad) solution would be to request DB without any authz check and then run post filtering with returned IDs and using Permify API. This solution can be really slow and doesn't scale. Second (bad) solution would be to use a kind of SQL WHERE IN operator requesting Permify API first to get all IDs. However, it can be incompatible with pagination (cursor or page based). Moreover, if you also need search, filter or ordering on a data column, you will end up with a post SQL query filtering, resulting in poor performance. IMHO, the best solution is to filter SQL query based on Permify data. https://blog.openpolicyagent.org/write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4. For example, I believe JSONB column could be used for each row in database and SELECT * query can be filtered using Permify output to get only data a user has access to. It's not necessary data he is owner, it can be a shared data so we cannot just used a kind of "owner" column.
Describe alternatives you've considered
First or second solution but it can't scale.
Additional context Add any other context or screenshots about the feature request here.
Solution on High Level
Planning to develop an API endpoint that will return an SQL query according to the authorization data you stored, and your authorization model. Basically, with this endpoint you can ask questions in form of “Which resources can user:X do action Y?” And you’ll get a SQL query without any conditions (filter, pagination or sorting etc) attached to it.
You can add conditions depending on your needs after getting the query response. So if you have a list with pagination, after getting the core SQL query from our API request you can add pagination filters to it.
Example Usage
I’ll follow the simplified github access control example since we talked about it a in our meeting a little. So, we want to list your repositories in a page with a pagination and filtering functionalities and want to know,
- Which repositories user Y can push ?
Let’s start with our authorization model,
Modeling Authorization
In Permify standart access decisions are evaluated by stored relational tuples (authorization data) and your authorization model, Permify Schema.
About the Permify Schema, it’s a language that you can model your authorization logic with it. You can define your entities, relations between them and access control decisions with using Permify Schema. So for our example we can prepare a simple model for as follows.
// represents the users in your app
entity user {}
// represents the organization table in your db
entity organization {
// organizational wide roles
relation admin @user
relation member @user
// permissions
action create_repository = admin or member
action delete = admin
} `table:organizations`
// represents the repository table in your db
entity repository {
// relations of the repository entity
relation owner @user `column:owner_id`
relation parent @organization `column:organization_id`
// permissions
action push = owner or parent.admin
} `table:repositories`
You can find more about modeling in our [docs](https://docs.permify.co/docs/getting-started/modeling).
Authorization Data
Permify stores your authorization data in a database you prefer as relation tuples. Relation tuples are the underlying data form that represents object-to-object and object-to-subject relations. Here is a example deconstruction for sample relations tuples,
So for our case, let’s say we have created below stored relational tuples
organization:2#admin@user:1
repository:1#owner@user:1
repository:1#parent@organization:2#...
Explanation of those tuples
- organization:2#admin@user:1 —> ****user 1 admin in organization 2
- repository:1#owner@user:1 —> ****user 1 owner in repository 1
- repository:1#parent@organization:2#... —> repository 1 is belongs to organization 2
Since we have the necessary authorization data and an authorization model configured, let’s turn back to our question
Which repositories can user:1 push?
We thought an endpoint for answering this question.
Request Body
{
"entity": "repository",
"action": "push",
"subject": {
"type": "user",
"id": "1",
}
}
API Endpoint Workflow
1) Checking the action permissions on Model
After the API call, our engine first checks the configured Permify Schema ( your authorization model ) and find the push action statement, which is:
action push = owner or parent.admin
So, result will be the list of repositories that user:1 is either owner or an parent.admin ( parent referencing the organization so its referring the admin relation in organization )
2) Finding the database column
If we start with owner action,
relation owner @user `column:owner_id`
Crucial part in here is determining whether the column ( column:owner_id ) is defined in the owner relation. So above the column defined as owner_id which indicates the exact same column name in your database.
3) Building the SQL query from authorization data
Afterwards, our engine will find the column and ask the question.
which the repositories where user:1 is owner?
and build from a query as
select * from repositories where owner_id = 1
Basically it returns the repository that owner id is 1.
But since the parent.admin ( referencing the organizational admin ) can also have access to push with or operation.So we need to extend our SQL a little with the question:
which the organizations where user:1 is admin?
And with combining these two questions we come up with a query:
Semantics : Return the repositories, where user:1 is the owner and where user:1 has the admin relation in the organization that these repositories belongs.
When we look at the column of parent relation we see the organization_id , so we need to check the organization_id column.
relation parent @organization `column:organization_id`
Only stored data related with this is “repository:1#parent@organization:2#…” So our engine will build the query according to organization.id = 2.
SQL
select * from repositories INNER JOIN organizations
ON repositories.parent_id = organizations.id
where owner_id = 1 or organizations.id = 2
Note: We can have multiple relation tuples, especially if you have multi-tenancy with more than one organization in the application. So in that case “organizations.id = 2” will be updated as
organizations.id in (2, 12, 57, etc)
Finally our API endpoint returns the result SQL. This endpoint will return a query without any sort, filter etc. After you got the response, you can customize the query with the needed conditions, like below:
select * from repositories INNER JOIN organizations
ON repositories.parent_id = organizations.id
where owner_id = 1 or organizations.id = 2 limit 10 offset 15
In this way, we can decouple the condition functionalities from the data filtering.
Cerbos has a similar feature: https://docs.cerbos.dev/cerbos/latest/api/index.html#resources-query-plan
May I ask: As the issue is closed and removed from public roadmap, this feature is not considered anymore?
