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 = auth.uid());

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

-- Automatically bind data ownership on insert
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = 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 = auth.uid());

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

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

-- Automatically bind data ownership on insert
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = 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 = auth.uid());

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

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

-- Automatically bind data ownership on insert
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = 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 = 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 = 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 = 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 = 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 = auth.uid());

-- Allow authenticated users to insert data
CREATE POLICY insert_own ON my_table
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = 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 TEXT 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 = auth.uid());

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