Skip to main content

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.

After RLS is enabled, non-admin roles cannot access any rows unless a matching Policy allows them. Write TO anon or TO authenticated explicitly in each Policy to avoid applying it to unnecessary roles and to make permission diagnostics easier.

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 and no explicit WITH CHECK, PostgreSQL reuses the USING expression as the write check by default. For a simple "only modify your own data" policy, this is usually still safe. For complex policies, writing WITH CHECK explicitly is clearer and avoids relying on default reuse semantics that may not match your business intent.

warning

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

UPDATE Requires Visible Target Rows​

An UPDATE operation must match an UPDATE Policy and must also be able to see the target rows through a SELECT Policy. If the user has no corresponding SELECT Policy, the UPDATE may not affect the target data even if the UPDATE Policy appears to allow it.

For client-updatable data, you usually need both:

  • FOR SELECT: allows users to see rows they can operate on.
  • FOR UPDATE: uses USING to limit old rows that can be modified and WITH CHECK to validate new rows after modification.

Null Behavior of auth.uid()​

When a user is not logged in, auth.uid() returns null. In SQL, null = any_value evaluates to null, which does not pass an RLS condition. Therefore, policies based on auth.uid() 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 a (select 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 ((select auth.uid()) IS NOT NULL AND user_id = (select auth.uid()));

Use JWT Claims Safely​

If a policy needs more information from the JWT, you can use auth.jwt() or an equivalent claims helper. Do not use metadata that users can edit themselves, such as nickname, avatar, or profile text, as an authorization source.

Claims written and controlled by the server are better suited for authorization, such as user roles, team IDs, or permission lists. Also remember that JWTs have an expiration time. After a user's permissions change, an old JWT may still carry old claims until it expires. For critical permission changes, combine short token lifetimes, re-authentication, or server-side checks.

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;

If you use PostgreSQL 14 or earlier, the security_invoker option is unavailable. In that case, avoid exposing such Views to anon / authenticated, or place the Views in a schema that frontend clients cannot access directly and expose them only through controlled server-side APIs.

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);

Specify Policy Roles​

If a Policy does not include TO <role>, it applies to all applicable roles. For frontend access, explicitly specify TO authenticated or TO anon, authenticated so the policy is evaluated only for intended roles and the authorization intent stays clear.

-- Recommended: explicitly limit to signed-in users
CREATE POLICY select_own ON my_table
  FOR SELECT
  TO authenticated
  USING (user_id = (select auth.uid()));

Use SECURITY DEFINER Functions Carefully​

Complex Policies may use functions to encapsulate checks such as team membership or role membership. Use SECURITY DEFINER only when the function really needs to read helper tables with the function owner's privileges.

When using SECURITY DEFINER, make sure that:

  • The function sets search_path explicitly to prevent hijacking by same-named objects.
  • The function is not placed in a public schema that frontend clients can call directly.
  • EXECUTE privileges are tightly controlled and granted only to roles that need them.
  • The function body still validates the current user identity and business permissions explicitly.

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.