Policies

Learn to use Policies for your Authorization needs

Introduction

If you've followed the previous Guides, you might wonder how your project's database is secured against users accessing database records they should not be able to access or modify. This Guide will clear things up.

Thin Backend uses Postgres Policies to grant access to specific database rows on a per row level. Policies are defined per table. If there’s no policy defined, by default a table is not accessible from the frontend at all.

To keep things simple, we automatically create a policy when a user_id column is added to a table. This auto generated policy allows the user to edit all rows in a table where the user_id field matches the current logged in user id. So basically the user can create, update and delete his own data, but never touch anyone’s else data. All the Guides in the documentation typically have a user_id column. Therefore they automatically use policies, even though we might not explicitly mention it in the docs to keep it simple when starting out.

Understanding the Default Policies

In the Schema Designer you should be able to see policies like this:

The Users can manage their products is just a human readable name for that policy.

The interesting thing is the condition user_id = ihp_user_id():

• Here the user_id refers to the user_id field of a database record
• The ihp_user_id() function is provided by Thin Backend and returns the ID of the current logged in user.

If that condition returns true, read and write operations are allowed. Records where the condition return false will not be visible to a useQuery(..) call from the frontend. Any update or delete operation will also fail if the condition returns false.

Editing Policies

To edit a policy, select it's Table in the Schema Designer and then click on the Policy at the bottom:

This will show the Edit Policy modal:

Here you can adjust the Policy definition. Click "Update Policy" after you're done.

Advanced: Editing Policies via the Code Editor

If you have very specific needs and know your way into SQL syntax, you can use the Code Editor to write your Policy as an SQL statement.

Open the Schema Designer, select the "Code Editor" tab, and look for the CREATE POLICY statement you want to edit.

Here we can e.g. make changes to the Users can manage their tasks policy:

CREATE POLICY "Users can manage their tasks" ON tasks USING (user_id = ihp_user_id()) WITH CHECK (user_id = ihp_user_id());

After you've adjusted the SQL code, click the Save button in the top right corner.

Examples

Policies for a Chat App

Let's asume we're building a Slack-like chat application. We have users, channels and messages. Each message belongs to a channel and is authored by a user.

The schema can look like this:

CREATE FUNCTION set_updated_at_to_now() RETURNS TRIGGER AS $
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$ language plpgsql;
CREATE TABLE users (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    email TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    locked_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    failed_login_attempts INT DEFAULT 0 NOT NULL,
    access_token TEXT DEFAULT NULL,
    confirmation_token TEXT DEFAULT NULL,
    is_confirmed BOOLEAN DEFAULT false NOT NULL,
    name TEXT DEFAULT NULL
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE TABLE channels (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    user_id UUID DEFAULT ihp_user_id() NOT NULL
);
CREATE INDEX channels_created_at_index ON channels (created_at);
CREATE INDEX channels_user_id_index ON channels (user_id);
ALTER TABLE channels ADD CONSTRAINT channels_ref_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE NO ACTION;
ALTER TABLE channels ENABLE ROW LEVEL SECURITY;
CREATE TABLE messages (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    body TEXT NOT NULL,
    user_id UUID DEFAULT ihp_user_id() NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    channel_id UUID NOT NULL
);
CREATE INDEX messages_user_id_index ON messages (user_id);
ALTER TABLE messages ADD CONSTRAINT messages_ref_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE NO ACTION;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
CREATE INDEX messages_created_at_index ON messages (created_at);
CREATE TRIGGER update_messages_updated_at BEFORE UPDATE ON messages FOR EACH ROW EXECUTE FUNCTION set_updated_at_to_now();
CREATE INDEX messages_channel_id_index ON messages (channel_id);
ALTER TABLE messages ADD CONSTRAINT messages_ref_channel_id FOREIGN KEY (channel_id) REFERENCES channels (id) ON DELETE NO ACTION;

Right now the schema has no policies and cannot be used from the frontend. Let's add some policies to the above schema.

Users

First we want the app to be able to display the current users name in the app. For that we need a policy that allows the current user to access his record in the users table:

CREATE POLICY "Users can read their own record" ON users USING (id = ihp_user_id()) WITH CHECK (false);

Channels

Every user should be able to create channels. Users should only be able to edit channels they've created. So we add a policy like this:

CREATE POLICY "Users can edit their channels, but every channel is public" ON channels USING (true) WITH CHECK (user_id = ihp_user_id());

Messages

Everyone should be able to read all messages in a channel, but only the user who has created the message should be able to edit or delete it:

CREATE POLICY "Users can edit their messages" ON messages USING (user_id = ihp_user_id()) WITH CHECK (user_id = ihp_user_id());
CREATE POLICY "Users can delete their messages" ON messages FOR DELETE USING (user_id = ihp_user_id());
CREATE POLICY "Everyone can see messages" ON messages FOR SELECT USING (true);

Policies for Public Readable Content

Let's asume you're building a blog app. You want records stored in the posts table to be public readable to everyone if the is_public field is set to true for a record, or if the posts record was created by the logged in user.

CREATE POLICY "Users can manage their posts" ON messages USING (user_id = ihp_user_id()) WITH CHECK (user_id = ihp_user_id());
CREATE POLICY "Users can read all public posts" ON messages USING (is_public);

With multiple policies, access is granted if atleast one of the policies returns true.

Policies for Teams

Let's asume a user can be in multiple teams, and the user can manage all posts that belong to the team. A possible policy for this would look like this:

CREATE POLICY "Users can manage their teams posts"
    ON messages
    USING (team_id IN (SELECT team_users.team_id FROM team_users WHERE team_users.user_id = ihp_user_id()))
    WITH CHECK (team_id IN (SELECT team_users.team_id FROM team_users WHERE team_users.user_id = ihp_user_id()))
;