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.
This is an advanced feature. We do not recommend using column-level privileges for most users. Instead, we recommend using RLS policies in combination with a dedicated table for handling user roles.
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:
_10create policy "Allow update for owners" on posts for_10update_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:
- table-level: Grants the privilege on all columns in the table.
- 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.
_10revoke_10update_10 on table public.posts_10from_10 authenticated;_10_10grant_10update_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:
_10revoke_10update_10 (title) on table public.posts_10from_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.
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.
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.
_10supabase migration new create_posts_table
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.
_21create table_21posts (_21id bigint primary key generated always as identity,_21user_id text,_21title text,_21content text,_21created_at timestamptz default now()_21updated_at timestamptz default now()_21);_21_21-- Add row-level security_21create policy "Allow update for owners" on posts for_21update_21using ((select auth.uid()) = user_id);_21_21-- Add column-level security_21revoke_21update_21(title) on table public.posts_21from_21authenticated;
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.