Database

Column Level Security

PostgreSQL's Row Level Security (RLS) gives you granular control over who can access rows of data. However, it doesn't give you control over which columns they can access within rows. Sometimes you want to restrict access to specific columns in your database. Column Level Privileges allows you to do just that.

Policies at the Row Level

Policies in Row Level Security (RLS) are used to restrict access to rows in a table. Think of them like adding a WHERE clause to every query.

For example, let's assume you have a posts table with the following columns:

  • id
  • user_id
  • title
  • content
  • created_at
  • updated_at

You can restrict updates to just the user who created it using RLS, with the following policy:


_10
create policy "Allow update for owners" on posts for
_10
update
_10
using ((select auth.uid()) = user_id);

However, this gives the post owner full access to update the row, including all of the columns.

Privileges at the Column Level

To restrict access to columns, you can use Privileges.

There are two types of privileges in PostgreSQL:

  1. table-level: Grants the privilege on all columns in the table.
  2. column-level Grants the privilege on a specific column in the table.

You can have both types of privileges on the same table. If you have both, and you revoke the column-level privilege, the table-level privilege will still be in effect.

By default, our table will have a table-level UPDATE privilege, which means that the authenticated role can update all the columns in the table.


_10
revoke
_10
update
_10
on table public.posts
_10
from
_10
authenticated;
_10
_10
grant
_10
update
_10
(title, content) on table public.posts to authenticated;

In the above example, we are revoking the table-level UPDATE privilege from the authenticated role and granting a column-level UPDATE privilege on just the title and content columns.

If we want to restrict access to updating the title column:


_10
revoke
_10
update
_10
(title) on table public.posts
_10
from
_10
authenticated;

This time, we are revoking the column-level UPDATE privilege of the title column from the authenticated role. We didn't need to revoke the table-level UPDATE privilege because it's already revoked.

Manage column privileges in the Dashboard

You can view and edit the privileges in the Supabase Studio.

Column level privileges

Manage column privileges in Migrations

While you can manage privileges directly from the Dashboard, as your project grows you may want to manage them in your migrations. Read about database migrations in the Local Development guide.

1

Create a migration file

To get started, generate a new migration to store the SQL needed to create your table along with row and column-level privileges.


_10
supabase migration new create_posts_table

2

Add the SQL to your migration file

This creates a new migration: supabase/migrations/<timestamp> _create_posts_table.sql.

To that file, add the SQL to create this posts table with row and column-level privileges.


_21
create table
_21
posts (
_21
id bigint primary key generated always as identity,
_21
user_id text,
_21
title text,
_21
content text,
_21
created_at timestamptz default now()
_21
updated_at timestamptz default now()
_21
);
_21
_21
-- Add row-level security
_21
create policy "Allow update for owners" on posts for
_21
update
_21
using ((select auth.uid()) = user_id);
_21
_21
-- Add column-level security
_21
revoke
_21
update
_21
(title) on table public.posts
_21
from
_21
authenticated;

Considerations when using column-level privileges

  • If you turn off a column privilege you won't be able to use that column at all.
  • All operations (insert, update, delete) as well as using select * will fail.