Setting up the permissions database table

Restricting our API so that movie data can only be accessed and edited by activated users is useful, but sometimes you might need a more granular level of control. For example, in our case we might be happy for ‘regular’ users of our API to read the movie data (so long as they are activated), but we want to restrict write access to a smaller subset of trusted users.

In this chapter we’re going to introduce the concept of permissions to our application, so that only users with a specific permission can perform specific operations. In our case, we’ll create two permissions: a movies:read permission which will allow a user to fetch and filter movies, and a movies:write permission which will allow users to create, edit and delete movies.

The required permissions will align with our API endpoints like so:

Relationship between permissions and users

The relationship between permissions and users is a great example of a many-to-many relationship. One user may have many permissions, and the same permission may belong to many users.

The classic way to manage a many-to-many relationship in a relational database like PostgreSQL is to create a joining table between the two entities. I’ll quickly explain how this works.

Let’s say that we are storing our user data in a users table which looks like this:

And our permissions data is stored in a permissions table like this:

Then we can create a joining table called users_permissions to store the information about which users have which permissions, similar to this:

In the example above, the user alice@example.com (user ID 1) has the movies:read (permission ID 1) permission only, whereas bob@example.com (user ID 2) has both the movies:read and movies:write permissions.

Just like the one-to-many relationship that we looked at earlier in the book, you may want to query this relationship from both sides in your database models. For example, in your database models you might want to create the following methods:

PermissionModel.GetAllForUser(user)       → Retrieve all permissions for a user
UserModel.GetAllForPermission(permission) → Retrieve all users with a specific permission

Creating the SQL migrations

Let’s put this into practice and make a SQL migration which creates new permissions and users_permissions tables in our database, following the pattern that we’ve just described above.

Go ahead and run the following command to create the migration files:

$ migrate create -seq -ext .sql -dir ./migrations add_permissions
/home/alex/Projects/greenlight/migrations/000006_add_permissions.up.sql
/home/alex/Projects/greenlight/migrations/000006_add_permissions.down.sql

And then add the following SQL statements to the ‘up’ migration file:

CREATE TABLE IF NOT EXISTS permissions (
    id bigserial PRIMARY KEY,
    code text NOT NULL
);

CREATE TABLE IF NOT EXISTS users_permissions (
    user_id bigint NOT NULL REFERENCES users ON DELETE CASCADE,
    permission_id bigint NOT NULL REFERENCES permissions ON DELETE CASCADE,
    PRIMARY KEY (user_id, permission_id)
);

-- Add the two permissions to the table.
INSERT INTO permissions (code)
VALUES 
    ('movies:read'),
    ('movies:write');

There are a couple of important things to point out here:

• The PRIMARY KEY (user_id, permission_id) line sets a composite primary key on our users_permissions table, where the primary key is made up of both the user_id and permission_id columns. Setting this as the primary key essentially means that the same user/permission combination can only appear once in the table and cannot be duplicated.

• When creating the users_permissions table we use the REFERENCES users syntax to create a foreign key constraint against the primary key of our users table, which ensures that any value in the user_id column has a corresponding entry in our users table. And likewise, we use the REFERENCES permissions syntax to ensure that the permission_id column has a corresponding entry in the permissions table.

Let’s also add the necessary DROP TABLE statements to the ‘down’ migration file, like so:

DROP TABLE IF EXISTS users_permissions;
DROP TABLE IF EXISTS permissions;

Now that’s done, please go ahead and run the migration:

$ migrate -path ./migrations -database $GREENLIGHT_DB_DSN up
6/u add_permissions (22.74009ms)