Basic Permissions

CloudBase PostgreSQL database implements basic data permission control through Row Level Security (RLS).

RLS allows you to define access policies at the database level, precisely controlling read and write permissions for each row of data, ensuring users can only access authorized data.

Introduction to RLS

PostgreSQL's RLS mechanism controls row-level data access by creating security policies on tables. The core workflow is:

1. Enable RLS: Turn on row-level security for the target table
2. Create Policies: Define which operations (SELECT, INSERT, UPDATE, DELETE) users can perform and which rows they can access
3. Automatic Filtering: The database automatically filters data rows based on policies during query execution, completely transparent to the application layer

In CloudBase, auth.uid() retrieves the current user's identity. You can compare it with a custom user identifier column in your table to implement data ownership control.

CloudBase assigns the following database roles to users:

• anon: Role assigned to unauthenticated users
• authenticated: Role assigned to logged-in users

You can combine roles with auth.uid() in RLS policies for more flexible permission control.

Configuration Method

Configure table permission policies by executing SQL statements. Below are Policy creation examples for several common scenarios.

tip

The examples use user_id as the data ownership column name. You can replace it with any other column based on your actual table structure.

Read All Data, Modify Own Data

Suitable for scenarios such as user comments and public user profiles — authenticated users can view all data, but only data owners can modify their own data.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to read all data
CREATE POLICY select_all ON my_table
  FOR SELECT
  TO authenticated
  USING (true);

-- Only allow users to modify their own data
CREATE POLICY update_own ON my_table
  FOR UPDATE
  TO authenticated
  USING (user_id = (select auth.uid()))
  WITH CHECK (user_id = (select auth.uid()));

-- Only allow users to delete their own data
CREATE POLICY delete_own ON my_table
  FOR DELETE
  TO authenticated
  USING (user_id = (select auth.uid()));

-- Automatically bind data ownership on insert
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = (select auth.uid()));

Read and Modify Own Data

Suitable for scenarios such as user personal settings and order management — users can only view and operate on their own data.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- Only allow users to read their own data
CREATE POLICY select_own ON my_table
  FOR SELECT
  TO authenticated
  USING (user_id = (select auth.uid()));

-- Only allow users to modify their own data
CREATE POLICY update_own ON my_table
  FOR UPDATE
  TO authenticated
  USING (user_id = (select auth.uid()))
  WITH CHECK (user_id = (select auth.uid()));

-- Only allow users to delete their own data
CREATE POLICY delete_own ON my_table
  FOR DELETE
  TO authenticated
  USING (user_id = (select auth.uid()));

-- Automatically bind data ownership on insert
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = (select auth.uid()));

Public Read Access (Including Unauthenticated Users)

Suitable for scenarios such as announcements and help documents — anyone, including unauthenticated users, can view the data without logging in.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- Allow everyone (including unauthenticated users) to read data
CREATE POLICY select_anon ON my_table
  FOR SELECT
  TO anon, authenticated
  USING (true);

Read All Data, No Modifications Allowed

Suitable for scenarios such as product information and system configuration — authenticated users can view all data, but modifications from the client side are not allowed.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to read all data
CREATE POLICY select_all ON my_table
  FOR SELECT
  TO authenticated
  USING (true);

-- No INSERT / UPDATE / DELETE policies are created
-- Once RLS is enabled, operations not permitted by any policy are denied by default

Conditional Access Based on Status

Suitable for scenarios such as article publishing and product listing — authenticated users can view all published data, and authors can also view and modify their own drafts.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- Authenticated users can view published data or their own drafts
CREATE POLICY select_published_or_own ON my_table
  FOR SELECT
  TO authenticated
  USING (status = 'published' OR user_id = (select auth.uid()));

-- Only allow users to modify their own data
CREATE POLICY update_own ON my_table
  FOR UPDATE
  TO authenticated
  USING (user_id = (select auth.uid()))
  WITH CHECK (user_id = (select auth.uid()));

-- Only allow users to delete their own data
CREATE POLICY delete_own ON my_table
  FOR DELETE
  TO authenticated
  USING (user_id = (select auth.uid()));

-- Automatically bind data ownership on insert
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = (select auth.uid()));

Team Shared Data

Suitable for multi-tenant and team collaboration scenarios — members of the same team can view and operate on each other's data.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- Allow users to view data from the same team
CREATE POLICY select_team ON my_table
  FOR SELECT
  TO authenticated
  USING (team_id IN (SELECT team_id FROM team_members WHERE user_id = (select auth.uid())));

-- Allow users to modify data from the same team
CREATE POLICY update_team ON my_table
  FOR UPDATE
  TO authenticated
  USING (team_id IN (SELECT team_id FROM team_members WHERE user_id = (select auth.uid())))
  WITH CHECK (team_id IN (SELECT team_id FROM team_members WHERE user_id = (select auth.uid())));

-- Allow users to insert data within their team
CREATE POLICY insert_team ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (team_id IN (SELECT team_id FROM team_members WHERE user_id = (select auth.uid())));

-- Allow users to delete data from the same team
CREATE POLICY delete_team ON my_table
  FOR DELETE
  TO authenticated
  USING (team_id IN (SELECT team_id FROM team_members WHERE user_id = (select auth.uid())));

Insert Only, No Modifications or Deletions

Suitable for scenarios such as user feedback and operation logs — authenticated users can submit data, but cannot modify or delete it after submission.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to view their own submitted data
CREATE POLICY select_own ON my_table
  FOR SELECT
  TO authenticated
  USING (user_id = (select auth.uid()));

-- Allow authenticated users to insert data
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = (select auth.uid()));

-- No UPDATE / DELETE policies are created
-- Data cannot be modified or deleted after submission

No Permissions

Suitable for scenarios such as backend transaction logs and internal audit logs — client-side users cannot access the data directly; operations are only allowed through server-side logic such as cloud functions.

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- No policies are created
-- Once RLS is enabled, all client-side operations are denied by default

tip

For tables with "no permissions", you can still access the data through server-side methods such as cloud functions. Server-side access uses admin privileges and is not restricted by RLS.

Best Practices

Use DEFAULT to Auto-Fill user_id

When creating a table, set DEFAULT auth.uid() on the user_id column. The database will automatically fill in the current user's identity on insert, eliminating the need for the client to pass this value and preventing forgery risks:

CREATE TABLE my_table (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id VARCHAR(64) NOT NULL DEFAULT auth.uid(),
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

This way, the client does not need to specify user_id when inserting data — the database automatically binds the current logged-in user:

// No need to pass user_id, the database fills it automatically
const { error } = await db
  .from("my_table")
  .insert({ content: "hello world" });

Combine with RLS to Prevent Tampering

Even with DEFAULT set, a malicious client could still explicitly pass a forged user_id during insertion. By combining it with the WITH CHECK constraint in an INSERT policy, you can ensure the written user_id must match the current logged-in user:

CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = (select auth.uid()));

When a client attempts to pass another user's user_id, the policy will reject the write.

Understanding the Difference Between USING and WITH CHECK

In RLS policies, USING and WITH CHECK serve different roles:

• USING: Controls which existing rows a user can read or operate on. Used for SELECT, UPDATE (filtering rows that can be modified), and DELETE.
• WITH CHECK: Controls what conditions new data must satisfy when written. Used for INSERT and UPDATE (validating the modified row).

For UPDATE operations, using both together is particularly important:

CREATE POLICY update_own ON my_table
  FOR UPDATE
  TO authenticated
  -- USING: can only modify own data
  USING (user_id = (select auth.uid()))
  -- WITH CHECK: user_id must still be own after modification
  WITH CHECK (user_id = (select auth.uid()));

If an UPDATE policy only has USING without WITH CHECK, the user can only modify their own rows but could change user_id to another user's value, resulting in data ownership tampering. Adding WITH CHECK ensures the database validates that the modified row still satisfies the condition, otherwise the operation is rejected.

warning

All UPDATE policies should include both USING and WITH CHECK to ensure data satisfies permission constraints both before and after modification.

Null Behavior of auth.uid()

When a user is not logged in, auth.uid() returns null. Since null = any_value in SQL always evaluates to false (not true or null), policies based on auth.uid() will automatically deny access when the user is not logged in, which is secure.

However, be aware of the following scenarios:

• If your policy uses != comparison (e.g., user_id != (select auth.uid())), null != value also returns false, which may not match your expectations
• If you need to explicitly distinguish between "not logged in" and "logged in but not matching", you can add an auth.uid() IS NOT NULL check

-- Explicitly require user to be logged in and data belongs to them
CREATE POLICY select_own ON my_table
  FOR SELECT
  TO authenticated
  USING (auth.uid() IS NOT NULL AND user_id = (select auth.uid()));

Views and RLS Interaction

Views in PostgreSQL execute with definer privileges (security_definer) by default. This means that when querying data through a View, RLS policies will not take effect, potentially leading to data leaks.

In PostgreSQL 15 and above, you can use the security_invoker option to make Views execute with invoker privileges, allowing RLS policies to work normally:

-- Create a secure View (PostgreSQL 15+)
CREATE VIEW public_posts
  WITH (security_invoker = true)
  AS SELECT id, title, content, user_id FROM posts;

warning

If you use Views and have RLS enabled on the underlying tables, make sure to verify the View's security mode. Views without security_invoker = true will bypass RLS, allowing all users to access all data through that View.

Performance Optimization

RLS policies are executed with every query. Improper implementation can seriously impact query performance. Here are key optimization recommendations.

Cache auth.uid() with Subquery

In RLS policies, calling auth.uid() directly causes PostgreSQL to invoke the function for every row in the result set. Wrapping it in a subquery with (select auth.uid()) allows PostgreSQL to recognize it as a constant and compute it only once:

-- ❌ Unoptimized: calls auth.uid() for every row
CREATE POLICY select_own ON my_table
  FOR SELECT TO authenticated
  USING (user_id = auth.uid());

-- ✅ Recommended: use subquery, computed only once
CREATE POLICY select_own ON my_table
  FOR SELECT TO authenticated
  USING (user_id = (select auth.uid()));

For large datasets, this optimization can improve query performance by orders of magnitude.

tip

All examples in this documentation already use the optimized syntax. When writing custom policies, always use (select auth.uid()) instead of auth.uid().

Create Indexes for Policy Columns

Columns used for filtering in RLS policies (such as user_id, team_id) should have indexes. Otherwise, every query requires a full table scan:

-- Create indexes for commonly used policy columns
CREATE INDEX idx_my_table_user_id ON my_table (user_id);
CREATE INDEX idx_my_table_team_id ON my_table (team_id);

For policies with compound conditions, you can create composite indexes:

-- Suitable for "conditional access based on status" scenarios
CREATE INDEX idx_my_table_status_user ON my_table (status, user_id);

Add Explicit Filters in Queries

Even with RLS policies configured, it's recommended to add explicit filter conditions in client-side queries. This helps the PostgreSQL query optimizer generate more efficient execution plans:

// ❌ Relying solely on RLS filtering
const { data } = await db
  .from("my_table")
  .select();

// ✅ Add explicit filters to help the optimizer
const { data } = await db
  .from("my_table")
  .select()
  .eq("user_id", userId);

RLS policies serve as a security safeguard at the query plan level, while explicit filter conditions allow the optimizer to narrow down the scan range earlier. Using both together achieves the best results.