Database Data APIs

Scenarios

This section describes how client-side applications connect to and use the database services provided by Huawei Cloud RDS Supabase.

• Traditional approach: The frontend does not connect directly to the database. Instead, you build your own backend services, write data access code for each table, and encapsulate it into APIs. The frontend accesses the database indirectly through these APIs. The more tables you have, the more repetitive work is required. Whenever a table changes, the corresponding APIs must also be updated, causing maintenance costs to accumulate as the business grows.
• RDS Supabase: Built on managed PostgreSQL, RDS Supabase automatically generates Data APIs based on table schemas. Each new table can be accessed through an API, although there may be a cache delay of a few seconds on the first access. APIs for subsequently added tables are generated as well. The frontend only needs the Supabase JavaScript SDK to access these APIs and perform create, read, update, and delete (CRUD) operations. Read operations can use the anon role, whereas write operations require a login state. This eliminates repetitive development at the source, so applications do not need to build their own backend APIs or write data access code for each table.

These APIs are generated by the PostgREST framework and can be accessed directly through the frontend SDK for read and write operations (subject to RLS policies and login state constraints). Row-level security (RLS) policies control access at the row level.

Related Concepts

Huawei Cloud RDS Supabase provides a managed PostgreSQL database and automatically generates Data APIs based on table schemas. Developers can perform CRUD operations without manually writing backend APIs. RLS also provides fine-grained data access control and isolation.

• PostgreSQL Database

  Huawei Cloud RDS Supabase provides an independent, managed PostgreSQL database. It is a full PostgreSQL database, not a simplified version. You can use the full range of PostgreSQL capabilities without building or maintaining database servers yourself, including advanced features such as JSONB, full-text search, views, stored procedures, and triggers. You can also view and manage data through a graphical SQL interface.

  When you create a Supabase application, the following two databases are automatically created on the managed PostgreSQL DB instance. For details, see Table 1.

  Table 1 Database descriptions

  Database	Purpose
  supabase_db	Application database that stores business tables and data.
  _supabase	Platform metadata database that stores the Supabase platform's own configuration. Do not modify it.

• Automatically Generated Data APIs

  After database tables are created, Supabase automatically generates corresponding HTTP endpoints for each table based on its schema. No backend code needs to be written manually.

  These endpoints are generated by the PostgREST framework and support all CRUD operations. Creating a table provides a complete set of data access endpoints for that table. (There may be a cache delay of a few seconds the first time; to refresh the schema immediately, execute NOTIFY pgrst, 'reload schema'.) APIs for newly added tables are generated as well. Developers can focus on business logic without writing a data access object (DAO) layer or backend APIs, eliminating repetitive development from the source.

• Database Roles

  Table 2 lists the database roles exposed by RDS Supabase for application-layer access. Applications and SDKs choose their access identities based on these roles.

  Table 2 Role descriptions

  Role	Description
  anon	Role for anonymous users; accessing using an API key, subject to RLS policies
  authenticated	Role for authenticated users; accessing using a JWT, subject to RLS policies
  service_role	Server-side role that bypasses RLS. Do not use it on the client side.

  Among the three roles, anon and authenticated roles are intended for frontend use and are both subject to RLS policies. Their read and write permissions depend on the user's login state. The service_role role is intended for server-side use and bypasses RLS, and it must never be used on the client side.

• RLS Policies

  RLS is a row-level access control mechanism built into PostgreSQL. When RLS is enabled on a table, all access requests are governed by policies. Policies define which rows a role can view or modify for each operation. Operations that do not match an applicable policy are denied by default. RDS Supabase uses RLS to isolate data between tenants and users in a shared database. Specifically, access through the anon and authenticated roles is subject to RLS policies, whereas the service_role role bypasses RLS. RLS policies must be used together with GRANT privileges.

• GRANT Privileges

  GRANT is a PostgreSQL statement that assigns privileges to database roles. Table-level privileges determine whether a role can perform operations such as SELECT, INSERT, UPDATE, and DELETE on a table. These privileges provide table-level access control and are checked before RLS policies are applied: A role must first have the required privileges through GRANT, after which RLS determines which rows it can access. In RDS Supabase, the authenticated role has no privileges on newly created tables by default and must be explicitly granted privileges before use.

• Relationship Between RLS Policies and GRANT Privileges

  RLS policies and GRANT privileges must be used together. Both are required for access.

  Table 3 Comparison of GRANT and RLS mechanisms

  Mechanism	Layer	Function
  GRANT	Table-level	It determines whether a role can perform SELECT, INSERT, UPDATE, and DELETE operations.
  RLS policies	Row-level	After GRANT privileges are granted, RLS determines which rows can be viewed or modified.

  Execution flow: Request → GRANT check → RLS policy check → Return results

  • GRANT without RLS policies: When RLS is enabled, access to rows is denied by default. Queries return empty results.
  • RLS policies without GRANT privileges: The role lacks table-level privileges, so requests are rejected.

Table Creation and Permissions Configuration

The following example uses a product pricing scenario. The products table stores product information, and the product_pricings table stores pricing tiers and prices for each product. The two tables are linked by product_id. When a product is deleted, its associated pricing records are also deleted. The example defines access boundaries based on business roles: Visitors can browse data, authenticated operations staff can manage it, and backend services can perform administrative operations. Based on these requirements, the following steps provide a complete configuration of tables, RLS policies, and GRANT privileges.

The following steps provide the complete SQL statements for table creation and permissions configuration. You can execute the statements individually or all at once in the SQL Editor of the Supabase Dashboard.

1. Create data tables.

   Define the products table and the product_pricings table, linking them through a product_id foreign key and cascading the deletion of pricing records when a product is deleted.

   -- products: Product table
   CREATE TABLE products (
     id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     name         TEXT NOT NULL,
     category     TEXT,
     description  TEXT,
     created_at   TIMESTAMPTZ DEFAULT now(),
     updated_at   TIMESTAMPTZ DEFAULT now()
   );
   
   -- product_pricings: Product pricing table
   CREATE TABLE product_pricings (
     id             BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     product_id     BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
     tier           TEXT NOT NULL,
     price          NUMERIC(10,2) NOT NULL,
     unit           TEXT DEFAULT 'CNY/month',
     effective_from DATE DEFAULT current_date,
     created_at     TIMESTAMPTZ DEFAULT now()
   );

   Field descriptions:

   • id: Primary key with a value generated automatically by the database. GENERATED BY DEFAULT allows explicit ID values to be specified in scenarios such as upsert. The default in the official template is GENERATED ALWAYS, which is stricter because it prohibits manual insertion of id.
   • product_id: A foreign key referencing products(id). ON DELETE CASCADE means that deleting a product also deletes its associated pricing records.
   • price: NUMERIC(10,2) allows up to 10 digits in total, including 2 decimal places, leaving up to 8 digits before the decimal point.
   • updated_at: The default value is set only when a row is inserted. To update this field automatically when a row changes, you must configure a trigger separately.
2. Enable RLS.

   Once RLS is enabled, access to each table is subject to RLS policies. Operations are denied by default if no applicable policy is configured.

   ALTER TABLE products ENABLE ROW LEVEL SECURITY;
   ALTER TABLE product_pricings ENABLE ROW LEVEL SECURITY;

   Enabling RLS on the product and pricing tables is like locking both tables. From this point on, which products and prices each role can view or modify is determined by the RLS policies configured in the next step. Product and pricing data are no longer openly accessible to all users, and access is controlled row by row according to the user's role.

3. Configure RLS policies.

   Configure policies by role and operation. The anon role (anonymous users) is granted read access only, whereas the authenticated role (authenticated users) is granted permission to perform CRUD operations.

   -- anon (anonymous users): read-only
   CREATE POLICY "anon_select_products"
     ON public.products FOR SELECT TO anon USING (true);
   
   CREATE POLICY "anon_select_product_pricings"
     ON public.product_pricings FOR SELECT TO anon USING (true);
   
   -- authenticated (authenticated users): CRUD
   CREATE POLICY "authenticated_select_products"
     ON public.products FOR SELECT TO authenticated USING (true);
   
   CREATE POLICY "authenticated_select_product_pricings"
     ON public.product_pricings FOR SELECT TO authenticated USING (true);
   
   CREATE POLICY "authenticated_insert_products"
     ON public.products FOR INSERT TO authenticated WITH CHECK (true);
   
   CREATE POLICY "authenticated_insert_product_pricings"
     ON public.product_pricings FOR INSERT TO authenticated WITH CHECK (true);
   
   CREATE POLICY "authenticated_update_products"
     ON public.products FOR UPDATE TO authenticated USING (true) WITH CHECK (true);
   
   CREATE POLICY "authenticated_update_product_pricings"
     ON public.product_pricings FOR UPDATE TO authenticated USING (true) WITH CHECK (true);
   
   CREATE POLICY "authenticated_delete_products"
     ON public.products FOR DELETE TO authenticated USING (true);
   
   CREATE POLICY "authenticated_delete_product_pricings"
     ON public.product_pricings FOR DELETE TO authenticated USING (true);

   These policies define the operations permitted for each role on the product and pricing tables. All policies allow access to every row, so the restrictions apply to the types of operations rather than to specific rows.

   • Visitors (anon) can only view products and prices.
   • Authenticated users (authenticated) can create, update, and delete products and pricing records.
   • service_role is not included in these policies because it bypasses RLS. For details, see 4.
4. Grant table-level privileges (GRANT).

   GRANT determines whether a role can perform a particular table-level operation, whereas RLS determines which rows the role can access. Both are required for access.

   -- anon: Read-only; grant SELECT only
   GRANT SELECT ON TABLE public.products TO anon;
   GRANT SELECT ON TABLE public.product_pricings TO anon;
   
   -- authenticated: CRUD; grant all four operation privileges
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.products TO authenticated;
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.product_pricings TO authenticated;
   
   -- service_role: Bypasses RLS; grant all privileges
   GRANT ALL ON ALL TABLES IN SCHEMA public TO service_role;
   

   The permissions for each role on the product and pricing tables are summarized below.

   Table 4 Example permission design

   Role	products (Product Table)	product_pricings (Product Pricing Table)
   anon (visitor)	Read-only	Read-only
   authenticated (logged-in user)	CRUD	CRUD
   service_role (server-side)	All (bypassing RLS)	All (bypassing RLS)

   From a business perspective, the product and pricing tables contain catalog data displayed to users. Visitors (anon) can only browse products and prices, preventing unauthorized changes to the catalog. Authenticated operations staff (authenticated) can publish or unpublish products and adjust prices to support routine operations.

5. Refresh the schema cache.

   Execute the following statement to immediately refresh the PostgREST schema cache, allowing newly created tables to be accessed instantly by the Data API without waiting for the periodic automatic refresh.

   NOTIFY pgrst, 'reload schema';

SDK Operations

Reference

The following open-source Supabase documents are provided for reference only.

• Supabase Database Overview
• Supabase JavaScript SDK — select
• Supabase JavaScript SDK — insert
• Supabase JavaScript SDK — update
• Supabase JavaScript SDK — upsert
• Supabase JavaScript SDK — delete
• Postgres REST API Management