GraphQL Authorization with Graphene, SQLAlchemy and oso
Tags: graphql, authorization, sqlalchemy, python
Published: 2020-12-18 23:18:47

GraphQL has been seeing a rapid uptick in adoption.  It enables a more expressive interface between frontend and backend code by allowing developers to specify exactly what data is required to render a given page.  While there are numerous wins for frontend engineers, the backend team needs a different mindset.  Instead of writing route handlers that return a fixed set of data (or maybe a few variations), the backend team must write **resolver functions** which return portions of the dataset individually. 

We've seen that many developers will apply route-level checks as a starting point for authorization in their applications.  A common pattern is using decorators or other route level guards that ensure a user meets certain requirements (the user is an admin, the user has a certain role, etc.) before the route code is run.

This approach is easy to get started with, but is typically not feasible in GraphQL.  There is one route in a GraphQL application, and the potential set of data returned is highly variable.  There are many more combinations of fields in a GraphQL query than the number of routes in a typical REST application.

Since GraphQL is oriented around access to individual data elements, a natural place to enforce authorization is during data access.  GraphQL's official documentation acknowledges as much:

> Where should you perform validation and authorization checks? The answer: inside a dedicated business logic layer. Your business logic layer should act as the single source of truth for enforcing *business domain rules* [emphasis added].

– [https://graphql.org/learn/thinking-in-graphs/#business-logic-layer](https://graphql.org/learn/thinking-in-graphs/#business-logic-layer)

Great. Easy.  But how does that actually work?  How do we enforce a business domain rule? Is there a simple abstraction for specifying them?

In this post, we'll look at using oso—an open source authorization library—for enforcing authorization rules.  We'll see how to declaratively specify authorization rules in Polar, oso's policy language, and how to integrate oso into a GraphQL application in only a few lines of code.

We'll use SQLAlchemy as our ORM, and Graphene — a popular Python GraphQL library.  The `sqlalchemy-oso` [library](https://docs.osohq.com/using/frameworks/sqlalchemy.html#installation) will provide the glue between oso, GraphQL and SQLAlchemy, allowing us to enforce authorization consistency.

# Getting setup

We'll start with a basic Flask app representing an expense management application.  To follow along, [clone the project on GitHub](https://github.com/osohq/oso-graphene-sqlalchemy-app).  We'll walk through the parts of this app relevant to authorization, but the full code is available in the project. We'll assume some familiarity with Flask, SQLAlchemy and GraphQL.  Each section of this post is based on a commit in the repository, and we'll link to the relevant commit at the beginning of the section.

If you plan on following along and trying out the examples as you read, make sure to follow the [README](https://github.com/osohq/oso-graphene-sqlalchemy-app/blob/main/README.md) to install the dependencies so you can run the code. This section covers the [initial commit](https://github.com/osohq/oso-graphene-sqlalchemy-app/tree/922af81fc265efc82f9572e174833689f051ae9a).

Our app has a few models:

- **Expense:** An expense created by a user.
- **User:** A user accessing the application.

We'll start with these, and add some more later.  Here are our initial models from `models.py`. These are regular SQLAlchemy models.  We used the [flask_sqlalchemy](https://flask-sqlalchemy.palletsprojects.com/en/2.x/) library to integrate with Flask and make our setup a bit easier.

```python
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy.orm import relationship

db = SQLAlchemy()

class Expense(db.Model):
    __tablename__ = 'expenses'

    id = db.Column(db.Integer, primary_key=True)
    amount = db.Column(db.Integer)

    created_by_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_by = relationship("User")

    description = db.Column(db.Text)

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(256))
```

An Expense is created by a user, available at the `Expense.created_by` property.

We also have a GraphQL schema defined using Graphene over these models in `app/schema.py`:

```python
import graphene
from graphene import relay
from graphene_sqlalchemy import SQLAlchemyConnectionField, SQLAlchemyObjectType

from flask import g

from . import models

class Expense(SQLAlchemyObjectType):
    class Meta:
        model = models.Expense
        interfaces = (relay.Node,)

class User(SQLAlchemyObjectType):
    class Meta:
        model = models.User
        interfaces = (relay.Node,)

class Query(graphene.ObjectType):
    expenses = SQLAlchemyConnectionField(Expense.connection)
    user = graphene.Field(User)
    node = graphene.relay.Node.Field()

    def resolve_user(parent, info):
        return (g.current_user
                if isinstance(g.current_user, models.User)
                else None)

# ... snip ...

schema = graphene.Schema(query=Query)
```

This schema uses the [Graphene-SQLAlchemy library](https://docs.graphene-python.org/projects/sqlalchemy/en/latest/) to create Graphene schema objects for our SQLAlchemy models.  This is a [standard approach](https://docs.graphene-python.org/projects/sqlalchemy/en/latest/tutorial/#defining-our-models) for using SQLAlchemy with Graphene.

We can make a simple query (I'm using the awesome [GraphiQL](https://github.com/graphql/graphiql) tool for this, but you can also just go to [http://localhost:5000/graphql](http://localhost:5000/graphql) in your browser) and see that our app is working:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/l7bbf9p6av86ofmjmoky.png)

## Adding authorization

At this point, we have a basic GraphQL app, without authorization.  Let's set that up. Here's the [commit on GitHub](https://github.com/osohq/oso-graphene-sqlalchemy-app/commit/8bf8c4a411f83e640fdd3537ddcc3d59049edecf).

Our initial authorization rule will be based on the concept of data owners: *users can view their own expenses.*

To implement this rule, we would need to perform filtering at the SQLAlchemy level. Which means:

1. Adding a custom resolver for Expense
2. Checking all other places the GraphQL schema might access expenses
3. Implementing the custom SQLAlchemy query
4. Repeating for every other model and authorization check.

This authorization rule is based on each database record—sometimes referred to as object-level permissions.  It can be challenging to abstract as middleware, and is certainly more complex than just adding the simple decorator we discussed earlier. 

Instead, we'll use the `sqlalchemy-oso` library.  We need to make two changes to set this up:

1. Add an instance of `Oso` to our application.
2. Write a policy containing our authorization rules.

To add oso, we'll modify the `create_app` function:

```python
from oso import Oso
from sqlalchemy_oso import register_models

def create_app():
    # snip ...

    # Create oso instance which will hold our policy.
    oso = Oso()  
    # Make SQLAlchemy models available in the policy.
    register_models(oso, db.Model)  
    # Load policy file into oso.
    oso.load_file(Path(__file__).parent / "policy.polar")   

    # ...
```

Then, we swap out the `SQLAlchemy` object in our app with `AuthorizedSQLAlchemy`. In `models.py`:

```python
from sqlalchemy_oso.flask import AuthorizedSQLAlchemy

db = AuthorizedSQLAlchemy(
    get_oso=lambda: current_app.oso,
    get_user=lambda: getattr(g, "current_user", None),
    get_action=lambda: "read"
)
```

We use this object instead of `flask_sqlalchemy.SQLAlchemy`.  It provides a [SQLAlchemy Session object](https://docs.sqlalchemy.org/en/13/orm/session.html) that filters queries to only return objects that are authorized for the current user and action.

Our policy file, `app/policy.polar` is empty for now.

Now, let's check our query results:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/mlbwai71zbo4altjurey.png)

Nothing's returned anymore.  Why? The policy file is empty.  Polar is deny by default, so we'll need to add some rules.  Before we do that, let's take a look at how this is working.  Our app has an endpoint that will log the SQL used to execute our last query.  If you're following along, run the app with:

```bash
$ FLASK_RUN_EXTRA_FILES=app/policy.polar FLASK_DEBUG=1 flask run
```

Run a query in GraphiQL.  Below is a good starting point.

```graphql
{
  expenses {
    edges {
      node {
        id
        description
        createdBy {
          email
        }
      }
    }
  }
}
```

Now visit, [http://localhost:5000/sql](http://localhost:5000/sql).

Here, we see:

```sql
SELECT count(*) AS count_1
  FROM (
        SELECT expenses.id AS expenses_id,
               expenses.amount AS expenses_amount,
               expenses.created_by_id AS expenses_created_by_id,
               expenses.description AS expenses_description
          FROM expenses
         WHERE 0 = 1
         ORDER BY expenses.id ASC
       ) AS anon_1
```

Notice our WHERE clause: `0 = 1`.  Where's that coming from? `sqlalchemy-oso` enforces our Polar policy by translating rules from the policy into SQL filters.  `0 = 1` is always false, which causes no records to be returned from the database.

## Adding some rules

Now that we have oso integrated, let's finish implementing our authorization rule.  Keep the app running, the `FLASK_RUN_EXTRA_FILES` environment variable we set will ensure that Flask reloads the app when you change the policy.

Let's try the following (in `app/policy.polar`):

```python
allow(_actor, _action, _resource);
```

This policy allows the user to access any object.  In Polar, each statement in the policy is called a rule.  The `allow` rule is a special rule that is used as the entrypoint to policies.  It has three arguments, the **actor** (who's making the request), the **action** (what the request will do), and the **resource** (what the request is operating over).  Our policy has one rule.  Each parameter starts with `_` which indicates an anonymous variable (one that matches anything because there are no constraints on it).

Rerun the query, and check out the SQL (Just refresh [http://localhost:5000/sql](http://localhost:5000/sql). You'll see a few statements there, I've picked out the primary SQL query used to resolve the GraphQL query):

```sql
SELECT expenses.id AS expenses_id,
       expenses.amount AS expenses_amount,
       expenses.created_by_id AS expenses_created_by_id,
       expenses.description AS expenses_description
  FROM expenses
 WHERE 1 = 1
 ORDER BY expenses.id ASC
 LIMIT 4
OFFSET 0
```

Now, we see `WHERE 1 = 1`.  This is always true! As a result, everything's been returned.  

Let's go a little further:

```python
allow(_: User, "read", _: Expense);
```

This policy is saying all users can read all expenses. Again, the `_` denotes an anonymous or ignored variable, but the `: User` syntax is checking that the input data matches a specific type. In this case, the User and Expense models from our [models.py](http://models.py/) file.

Here's the returned data:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/gslz33tyz854pcf72v8i.png)

Notice that the `createdBy` field of each expense is now `null`.  Since we have no rule for the `User` resource, the user cannot view any expenses.  Add a rule for `User` now:

```python
allow(_: User, "read", _: Expense);
allow(_: User, "read", _: User);
```

The above policy is in [this commit on GitHub.](https://github.com/osohq/oso-graphene-sqlalchemy-app/commit/972fa730a7bf957f4f50fce3820555b2775bb21b)

Now, let's get to our rule: users can view their own expenses.  Polar rules can have a body, indicated by the `if` keyword.  Here's a rule that will do what we want: *a user can read an expense if the expense was created by the user*:

```python
allow(user: User, "read", expense: Expense) if
    expense.created_by = user;

allow(_: User, "read", _: User);
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/6qj0r945zguipj6fc08y.png)

Let's look at the SQL:

```sql
SELECT expenses.id AS expenses_id,
       expenses.amount AS expenses_amount,
       expenses.created_by_id AS expenses_created_by_id,
       expenses.description AS expenses_description
  FROM expenses
 WHERE 1 = expenses.created_by_id
 ORDER BY expenses.id ASC
 LIMIT 2
OFFSET 0
```

Now, we see `WHERE 1 = expenses.created_by_id`.  `1` is the id of the current user.  Our policy has translated into SQL!  Check out [this commit to run it](https://github.com/osohq/oso-graphene-sqlalchemy-app/commit/eb2586f1d5bfe587e8efade57db14a4e203163d4)!

## Going further

So far, we've showed a small policy example. oso gives us a consistent abstraction for expressing authorization rules even though we can't just use simple decorators with GraphQL. To implement it, we didn't have to alter our resolvers, or write any new ones.  Polar and GraphQL are both declarative approaches to their problem domains: Polar provides an abstraction for declaring authorization rules, while GraphQL provides a declarative abstraction for obtaining data from a backend.  

If you want to see a bit more, we've taken this policy a bit further in the example app [here](https://github.com/osohq/oso-graphene-sqlalchemy-app/commit/5405736f601c8e5876593dd6d83f30c6d4438bad).  This policy shows:

- A new authorization rule that uses relationships: *users can view expenses for projects they are in.* Polar supports expressive rules involving relationships, which are extremely common in authorization policies (relationships often group users & resources together in ways that reflect the business domain).
- Generalization of authorization concepts (like creation) using rules. Writing new rules allows you to abstract functionality, and keep your policy DRY—equivalent to lifting common functionality into a function in the rest of your code (Polar's just code after all!).

To learn more about what we walked through:

- Check out our documentation on [List Filtering](https://docs.osohq.com/getting-started/list-filtering/index.html) & [SQLAlchemy](https://docs.osohq.com/getting-started/list-filtering/sqlalchemy.html).
- [Install](https://docs.osohq.com/using/frameworks/sqlalchemy.html#installation) the `sqlalchemy-oso` library.
- [Join our Slack](http://join-slack.osohq.com/) to chat with our engineering team. We're rapidly expanding the features discussed in this post, and would love to hear about your use cases!