Support fine grained user permissions

[sriramsub]

This proposal is subjected to change. We are documenting the design publicly to get feedback and design together with the community.

User authorization

Nile provides an easy way to add basic authorization to your application.

In the future, we’ll provide the ability to add more sophisticated permissions (see design below).

Basic authorization

Currently, Nile supports simple user authorization via SQL, the SDK, and NextJS middleware, which seamlessly integrates authentication with authorization. To authorize by user, you must set a user context.

💡 Due to current limitations in Nile, you must set a tenant context (link to tenant isolation) before setting the user context.

With both the user and tenant context set, subsequent queries return data that only the specified user can access, only if they’re part of the specified tenant.

In SQL

The user context is set via the nile.user_id variable.

set nile.tenant_id = '...'
set nile.user_id = '...'

SDK

import ...

await db("todos").withUser(tenantId, userId);

Permissions

In the future, Nile will provide the ability to configure advanced permissions for your application. Below is an initial design. Suggestions and feedback are greatly appreciated.

Terminology

There are several key concepts in Nile permissions:

Resource - What is being accessed. Initially, we plan to support rows.

Principal - Who accesses a resource. We plan to start with users but hope to support many different kinds of principals in the future (i.e. developers, service accounts, etc).

Action - Something a principal does on a resource (i.e. close on issues, read on repos).

Role - A named grouping of actions and optional conditions, like maintainer, or admin.

High-level overview

The steps to setting up permissions in Nile are:

1. Defining actions (i.e. what can users do?)
2. Creating policies
3. Creating roles
4. Assigning policies to principals and/or roles

Creating tables

Let’s imagine we’re GitHub and would like to implement access control in our application.

First, we need to create some tables:

CREATE TABLE repos (
	tenant_id   uuid REFERENCES tenants,
	id          uuid,
  name        VARCHAR(255) NOT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
	private     boolean,
	PRIMARY KEY (tenant_id, id)
);

CREATE TABLE issues (
  tenant_id   uuid REFERENCES tenants(id),
	id          uuid,
  title       VARCHAR(255) NOT NULL,
  description TEXT,
  created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
  creator     uuid,
  repo        uuid         NOT NULL REFERENCES repos,
	closed      boolean,
	stale       boolean,
	FOREIGN KEY (tenant_id, creator) 
		REFERENCES tenant_users (tenant_id, user_id),
	PRIMARY KEY (tenant_id, id)
);

-- Repo memberships table
CREATE TABLE repos_users (
	tenant_id uuid references tenants,
	repo_id uuid references repos,
	user_id uuid,
	FOREIGN KEY (tenant_id, user_id)
		REFERENCES tenant_users (tenant_id, user_id),
	FOREIGN KEY (tenant_id, repo_id),
		REFERENCES repos (tenant_id, id)
);

Defining actions

In Nile, permissions are enforced on each SQL query. When Nile receives a query like UPDATE issues SET closed = true WHERE id = '123', it has to know what to enforce access control for. Defining actions on resources is the first step to setting up access control in Nile.

API

Using the API, we’ll define a close action on issues that get enforced on queries that try to update the closed column to true:

POST /workspaces/{workspace}/access/actions

POST /workspaces/github/access/actions

{
	"name": "close",
	"resource": "issues",
	"definition": {
		"operation": "update",
		"columns": [
			{	
				"name": "closed",
				"value": true
			}
		]
	}
}

💡 *Every resource gets `create`, `read`, `update`, and `delete` actions out of the box.* The following is the SQL mapping for the built-in actions:

• create → INSERT
• read → SELECT
• update → UPDATE
• delete → DELETE

Creating policies

A policy is how you define permissions in Nile and consists of actions and conditions.

A policy consists of allow and deny blocks. allow permissions mean a principal is allowed to perform that action, and permissions in “deny” means a principal is denied (even if a matching allow permission is found).

Here’s a simple policy that allows deleting issues:

POST /workspaces/{workspace}/access/policies

{
	"name": "delete issues",
	"allow": [
		{
			"resource": "issues",
			"action": "delete"
		}
	]
}

Here’s a policy that allows a principal to close issues where the stale field is true. Conditions are expressed in SQL (think what comes after WHERE).

POST /workspaces/{workspace}/access/policies

{
	"name": "close stale issues",
	"allow": [
		{
			"resource": "issues",
			"action": "close",
			"condition": "stale is true"
		}
	]
}

Assigning policies

You can assign policies directly principals. In the future, we hope to support more flexible policy assignments using conditions.

POST /workspaces/{workspace}/access/policies/{policy_id}/tenants/{tenant_id}/assignments
POST /workspaces/{workspace}/access/policies/some_policy_id/tenants/some_tenant/assignments

{
	"principal": "some_user_uuid"
}

Creating roles

Roles are a useful way to group policies. Here’s an issue maintainer role that allows a principal to delete and close stale issues:

POST /workspaces/{workspace}/access/roles

{
	"name": "issue maintainer",
	"policies": [
		"delete issues",
		"close stale issues",
	]
}

Assigning roles

You can assign roles to individual principals. In the future, we hope to support more flexible role assignments using conditions.

POST /workspaces/{workspace}/access/roles/{role_id}/tenants/{tenant_id}/assignments
POST /workspaces/{workspace}/access/roles/issue_maintainer_uuid/some_tenant/assignments

{
	"principal": "some_user_uuid"
}