# Security Best Practices Standards for Supabase
This document outlines security best practices for developing applications using Supabase. It aims to provide clear, actionable guidelines to protect against common vulnerabilities and foster secure coding patterns specific to the Supabase ecosystem.
## 1. Authentication and Authorization
### 1.1. Row Level Security (RLS)
**Standard:** *Always* enforce Row Level Security (RLS) policies on all tables containing sensitive data.
**Why:** RLS provides a declarative way to control data access at the row level, preventing unauthorized users from accessing or modifying data they shouldn't. Lack of RLS implementation is one of the largest issues with Supabase setups
**Do This:**
* Enable RLS on tables containing sensitive data.
* Define granular policies based on user roles, ownership, or other relevant criteria.
* Test RLS policies thoroughly to ensure they function as expected.
**Don't Do This:**
* Disable RLS on sensitive tables.
* Rely solely on client-side filtering for data access control.
* Use overly permissive or vague RLS policies.
**Code Example (PostgreSQL):**
"""sql
-- Enable RLS on the 'profiles' table
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
-- Create a policy that allows a user to select their own profile
CREATE POLICY "Users can select their own profile."
ON profiles
FOR SELECT
USING (auth.uid() = user_id);
-- Create a policy that allows a user to update their own profile
CREATE POLICY "Users can update their own profile."
ON profiles
FOR UPDATE
USING (auth.uid() = user_id);
"""
**Anti-Pattern:**
Disabling RLS "for convenience" during development and forgetting to re-enable it in production. This is a high severity risk since it exposes PII.
### 1.2. Authentication Providers
**Standard:** Utilize Supabase's built-in authentication providers (e.g., email/password, OAuth) or integrate with external identity providers (IdPs) for secure user authentication. Avoid custom authentication schemes unless absolutely necessary and security audited.
**Why:** Supabase provides secure and well-tested authentication flows out-of-the-box, reducing the risk of common authentication vulnerabilities. External IdPs provide added security (such as MFA).
**Do This:**
* Leverage Supabase's "auth" client library for authentication workflows.
* Enable Multi-Factor Authentication (MFA) where possible.
* Consider using social providers (Google, GitHub, etc.) for simplified signup and login.
* Implement strong password policies if using email/password authentication.
* Review the Supabase Auth documentation for the latest features and best practices.
**Don't Do This:**
* Store passwords in plain text.
* Implement custom authentication schemes without proper security expertise.
* Expose sensitive authentication tokens client-side.
* Disable rate-limiting on authentication endpoints.
**Code Example (JavaScript):**
"""javascript
import { createClient } from '@supabase/supabase-js'
const supabaseUrl = 'YOUR_SUPABASE_URL'
const supabaseKey = 'YOUR_SUPABASE_ANON_KEY'
const supabase = createClient(supabaseUrl, supabaseKey)
async function signInWithEmail(email, password) {
const { data, error } = await supabase.auth.signInWithPassword({
email: email,
password: password,
})
if (error) {
console.error("Authentication error:", error);
} else {
console.log("Signed in:", data);
}
}
signInWithEmail('test@example.com', 'securePassword');
async function signOut() {
const { error } = await supabase.auth.signOut()
if (error) {
console.error("Sign out error:", error);
} else {
console.log("Signed out");
}
}
signOut();
"""
**Anti-Pattern:** Storing the "supabaseKey" directly in client-side JavaScript code without any protection is dangerous since any user could use it to execute arbitrary requests. Use server-side code or environment variables where possible.
### 1.3. JWT (JSON Web Token) Handling
**Standard:** Handle JWTs securely, verifying their authenticity and integrity before granting access to resources. Use the Supabase client library's built-in JWT handling mechanisms.
**Why:** JWTs are used to authenticate and authorize users. Improper handling of JWTs can lead to impersonation and unauthorized access. Supabase provides methods to make this straightforward.
**Do This:**
* Use the "auth.getUser()" function to retrieve and verify the current user's information from the JWT.
* Store JWTs securely (e.g., in HTTP-only cookies).
* Implement proper JWT refresh mechanisms to prevent token expiration issues.
* Configure a short JWT expiration time to limit the impact of compromised tokens.
**Don't Do This:**
* Trust JWTs without verification.
* Store JWTs in local storage (prone to XSS attacks).
* Expose the JWT signing key.
* Disable JWT verification.
**Code Example (JavaScript - Edge Function):**
"""javascript
// Example of verifying a JWT in an Edge Function
import { createClient } from '@supabase/supabase-js'
export async function handler(req, context) {
const authHeader = req.headers.get('Authorization');
if (!authHeader || !authHeader.startsWith('Bearer ')) {
return new Response("Unauthorized", { status: 401 });
}
const token = authHeader.substring(7); // Remove "Bearer " prefix
const supabaseUrl = process.env.SUPABASE_URL;
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY; // Use Service Role Key in Edge Functions
const supabase = createClient(supabaseUrl, supabaseKey, {
auth: {
persistSession: false // Disable persistence in edge functions
}
});
const { data: user, error } = await supabase.auth.getUser(token);
if (error) {
console.error("JWT Verification Error:", error);
return new Response("Unauthorized", { status: 401 });
}
// User is authenticated, proceed with protected logic
console.log("User ID:", user.user.id);
return new Response("Hello, User ${user.user.id}!");
}
"""
**Anti-Pattern:** Using the "anon" key to authenticate instead of a user JWT or the "service_role" key. The "anon" key should only be used for public assets.
## 2. Database Security
### 2.1. Preventing SQL Injection
**Standard:** *Always* use parameterized queries or prepared statements to prevent SQL injection vulnerabilities. Avoid constructing SQL queries by concatenating strings.
**Why:** SQL injection can allow attackers to execute arbitrary SQL code, potentially compromising the entire database.
**Do This:**
* Use Supabase's "from()" and "select()" methods to build queries.
* Use the "sql" template literal tag (or similar ORM features) for more complex queries.
**Don't Do This:**
* Concatenate user input directly into SQL queries.
* Disable parameter binding.
**Code Example (JavaScript):**
"""javascript
import { createClient } from '@supabase/supabase-js'
const supabaseUrl = 'YOUR_SUPABASE_URL'
const supabaseKey = 'YOUR_SUPABASE_ANON_KEY'
const supabase = createClient(supabaseUrl, supabaseKey)
async function fetchProfile(userId) {
const { data, error } = await supabase
.from('profiles')
.select('*')
.eq('user_id', userId); // Parameterized query
if (error) {
console.error("Error fetching profile:", error);
} else {
console.log("Profile:", data);
}
}
fetchProfile('some-user-id');
"""
**Anti-Pattern:**
"""javascript
//VULNERABLE CODE - DO NOT USE
async function fetchProfileUnsafe(userId) {
const query = "SELECT * FROM profiles WHERE user_id = '${userId}'"; //DO NOT DO THIS!
const { data, error } = await supabase.from('profiles').select('*').filter('raw_query', 'eq', query);
if (error) {
console.error("Error fetching profile:", error);
} else {
console.log("Profile:", data);
}
}
fetchProfileUnsafe("'; DROP TABLE profiles; --"); //SQL INJECTION!
"""
### 2.2. Limiting Database User Privileges
**Standard:** Grant database users the *least necessary* privileges required for their tasks. Avoid using the "service_role" key in client applications or untrusted environments.
**Why:** Limiting privileges reduces the potential impact of compromised credentials or SQL injection vulnerabilities.
**Do This:**
* Create separate database users for different application components.
* Grant only "SELECT", "INSERT", "UPDATE", and "DELETE" privileges as needed.
* Use the "service_role" key only in trusted environments (e.g., server-side code, Edge Functions).
* Create custom roles with specific permissions.
**Don't Do This:**
* Grant the "superuser" role to application users.
* Use the same database credentials for all application components.
* Embed the "service_role" key in client-side code.
**Code Example (PostgreSQL):**
"""sql
-- Create a new role for application users
CREATE ROLE app_user;
-- Grant SELECT privileges on the 'profiles' table
GRANT SELECT ON profiles TO app_user;
-- Grant INSERT privileges on the 'profiles' table
GRANT INSERT ON profiles TO app_user;
-- Create a new user and grant them the 'app_user' role
CREATE USER myapp WITH PASSWORD 'secure_password';
GRANT app_user TO myapp;
"""
### 2.3. Data Encryption
**Standard:** encrypt sensitive data both in transit and at rest.
**Why:** To ensure confidentiality and compliance with data privacy regulations.
**Do This:**
* Enable TLS/SSL for all database connections using "require_ssl = true;" in "postgresql.conf".
* Use column-level encryption for highly sensitive data (e.g., using "pgcrypto" extension).
* Consider using transparent data encryption (TDE) features if available.
**Don't Do This:**
* Store sensitive data in plain text without encryption.
* Disable TLS/SSL for database connections.
**Code Example (PostgreSQL with "pgcrypto"):**
"""sql
-- Install the pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Add an encrypted column to the 'profiles' table
ALTER TABLE profiles ADD COLUMN email_encrypted BYTEA;
-- Function to encrypt the email address
CREATE OR REPLACE FUNCTION encrypt_email(email TEXT, key TEXT)
RETURNS BYTEA AS $$
BEGIN
RETURN pgp_sym_encrypt(email, key);
END;
$$ LANGUAGE plpgsql STRICT;
-- Function to decrypt the email address
CREATE OR REPLACE FUNCTION decrypt_email(email_encrypted BYTEA, key TEXT)
RETURNS TEXT AS $$
BEGIN
RETURN pgp_sym_decrypt(email_encrypted, key);
END;
$$ LANGUAGE plpgsql STRICT;
-- Example usage:
-- Inserting encrypted data
UPDATE profiles SET email_encrypted = encrypt_email('test@example.com', 'my_secret_key') WHERE user_id = 'some-user-id';
-- Decrypting data
SELECT decrypt_email(email_encrypted, 'my_secret_key') FROM profiles WHERE user_id = 'some-user-id';
"""
**Important:** Manage encryption keys carefully, and never store them directly in the database or application code. Use a dedicated secrets management solution.
### 2.4 Auditing
**Standard:** Setup database auditing to monitor and log access to sensitive data, track changes, and detect suspicious activity.
**Why:** Helps in forensic analysis of security incidents, compliance reporting, and identifying potential security breaches.
**Do This:**
* Enable PostgreSQL's audit logging using extensions like "pgaudit".
* Configure audit logging to capture relevant events like data access, modifications, and authentication attempts.
* Regularly review audit logs for anomalies, suspicious patterns, and unauthorized access attempts.
* Set up alerts for critical security events.
"""sql
-- Install the pgaudit extension if it's not already installed
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- Configure pgaudit to log all read and write activity on tables
ALTER SYSTEM SET pgaudit.log = 'all';
-- Specify which tables to audit. In this example, we're auditing the 'profiles' table.
ALTER TABLE profiles AUDIT ALL;
-- Restart PostgreSQL to apply the changes
SELECT pg_reload_conf();
"""
**Anti-Pattern:** Failing to regularly review the collected logs makes the effort of capturing logs pointless, and can lead to security issues going unnoticed.
## 3. API Security
### 3.1. Rate Limiting
**Standard:** Implement rate limiting on API endpoints to prevent abuse and Denial-of-Service (DoS) attacks.
**Why:** Rate limiting protects your API from being overwhelmed by excessive requests, ensuring its availability and stability.
**Do This:**
* Use a rate-limiting middleware or service (e.g., Kong, Nginx with "limit_req_zone") to restrict the number of requests from a single IP address or user within a specific time window.
* Implement different rate limits for different API endpoints based on their criticality and resource consumption.
* Return informative error messages to clients when rate limits are exceeded.
**Don't Do This:**
* Expose API endpoints without any rate limiting.
* Use overly generous rate limits that allow for abuse.
* Fail to handle rate limit errors gracefully.
**Code Example (Edge Function with Rate Limiting):**
"""javascript
import { createClient } from '@supabase/supabase-js';
import { Ratelimit } from '@upstash/ratelimit';
import { Redis } from '@upstash/redis';
const redis = new Redis({
url: process.env.UPSTASH_REDIS_REST_URL,
token: process.env.UPSTASH_REDIS_REST_TOKEN,
})
const ratelimit = new Ratelimit({
redis: redis,
limiter: Ratelimit.slidingWindow(5, "10 s"), // 5 requests in 10 seconds
analytics: true,
})
export const corsHeaders = {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
}
export async function handler(req, context) {
// Apply rate limiting
const ipIdentifier = req.headers.get('x-forwarded-for') ?? req.ip ?? '127.0.0.1'; // Extract IP address
const { success, reset } = await ratelimit.limit(ipIdentifier);
if (!success) {
const now = Date.now()
const retryAfter = Math.min(reset * 1000 - now, 60000) / 1000;
return new Response("Rate limit exceeded", {
status: 429,
headers: {
...corsHeaders,
'Retry-After': retryAfter.toString(),
}
});
}
// Your Supabase logic here
const supabaseUrl = process.env.SUPABASE_URL;
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
const supabase = createClient(supabaseUrl, supabaseKey, {
auth: {
persistSession: false
}
});
const { data, error } = await supabase.from('profiles').select('*');
if (error) {
console.error("Supabase Error:", error);
return new Response(JSON.stringify({ error: error.message }), {
status: 500,
headers: { ...corsHeaders, 'Content-Type': 'application/json' },
});
}
return new Response(JSON.stringify(data), {
headers: { ...corsHeaders, 'Content-Type': 'application/json' },
});
}
"""
This uses Upstash for Redis integration. You will need an Upstash account and to set corresponding environment variables for it to work.
### 3.2. Input Validation and Sanitization
**Standard:** *Always* validate and sanitize all user input before processing it, both on the client and server sides.
**Why:** Input validation prevents attackers from injecting malicious data or exploiting vulnerabilities in your application.
**Do This:**
* Use strong data type validation to ensure that input conforms to expected formats.
* Sanitize input to remove or escape potentially harmful characters. Supabase automatically sanitizes inputs on many operations.
* Enforce length limits to prevent buffer overflows.
* Use regular expressions to validate complex input formats.
**Don't Do This:**
* Trust user input without validation.
* Rely solely on client-side validation.
* Disable input sanitization.
**Code Example (JavaScript - Input Validation):**
"""javascript
function validateEmail(email) {
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
return emailRegex.test(email);
}
function validatePassword(password) {
return password.length >= 8; // Minimum 8 characters
}
async function signUp(email, password) {
if (!validateEmail(email)) {
throw new Error("Invalid email address");
}
if (!validatePassword(password)) {
throw new Error("Password must be at least 8 characters long");
}
//Proceed with Supabase sign up logic
}
"""
### 3.3. Output Encoding
**Standard:** Encode output data properly to prevent Cross-Site Scripting (XSS) vulnerabilities.
**Why:** XSS vulnerabilities allow attackers to inject malicious scripts into your application, potentially stealing user credentials or performing other harmful actions.
**Do This:**
* Use context-aware encoding to escape special characters based on the output context (e.g., HTML, JavaScript, URL).
* Use templating engines with automatic escaping enabled.
* Set the "Content-Security-Policy" (CSP) header to restrict the sources of scripts and other resources that can be loaded by your application.
**Don't Do This:**
* Output user input directly into HTML without encoding.
* Disable output encoding.
**Code Example (Setting CSP Header in Edge Function):**
"""javascript
export const corsHeaders = {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
'Content-Security-Policy': "default-src 'self'; script-src 'self' https://cdn.supabase.com; style-src 'self' 'unsafe-inline';",
}
"""
## 4. Storage Security
### 4.1. Access Control for Buckets
**Standard:** Implement strict access control policies for Supabase Storage buckets to prevent unauthorized access to stored files.
**Why:** Improperly configured bucket permissions can expose sensitive data to the public.
**Do This:**
* Use RLS policies to control access to files based on user roles or ownership.
* Limit the usage of PUBLIC buckets. If not needed, use PRIVATE buckets.
* Use signed URLs for temporary access to private files.
* Rotate storage API keys periodically.
**Don't Do This:**
* Grant public read or write access to buckets containing sensitive data, unless absolutely necessary and with careful consideration.
* Store sensitive data in plain text in storage buckets.
* Expose storage API keys client-side.
**Code Example (Storage RLS Policy):**
"""sql
-- Enable RLS on the storage.objects table
ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;
-- Create a policy that allows a user to select their own files
CREATE POLICY "Users can select their own files."
ON storage.objects
FOR SELECT
USING (auth.uid() = owner);
-- Create a policy that allows a user to insert a file if they are the owner
CREATE POLICY "Users can insert their own files."
ON storage.objects
FOR INSERT
WITH CHECK (auth.uid() = owner);
"""
### 4.2. File Upload Validation
**Standard:** Validate file uploads to prevent malicious files from being stored in Supabase Storage.
**Why:** Malicious files can be used to exploit vulnerabilities in your application or other systems.
**Do This:**
* Check file extensions against an allowlist of permitted types.
* Verify file content types using magic numbers or other techniques.
* Scan uploaded files for malware.
* Limit file sizes.
**Don't Do This:**
* Trust file extensions alone.
* Store executable files in storage buckets without proper security measures.
**Code Example (JavaScript file upload validation):**
"""javascript
async function uploadFile(file) {
const allowedExtensions = ['jpg', 'jpeg', 'png', 'gif'];
const fileExtension = file.name.split('.').pop().toLowerCase();
if (!allowedExtensions.includes(fileExtension)) {
throw new Error("Invalid file type");
}
if (file.size > 10 * 1024 * 1024) { // 10MB limit
throw new Error("File size exceeds the limit");
}
//Upload to Supabase storage...
}
"""
## 5. Infrastructure Security
### 5.1. Network Security
**Standard:** Secure your Supabase infrastructure by implementing network security measures.
**Why:** Network security protects your Supabase instance from unauthorized access and attacks.
**Do This:**
* Use a firewall to restrict network access to your Supabase instance.
* Configure network policies to allow only necessary traffic.
* Use a Virtual Private Cloud (VPC) to isolate your Supabase instance from the public internet.
* Monitor network traffic for suspicious activity.
**Don't Do This:**
* Expose your Supabase instance directly to the public internet.
* Use default firewall rules.
### 5.2. Regular Security Audits
**Standard:** Conduct regular security audits of your Supabase application and infrastructure.
**Why:** Security audits help identify and address vulnerabilities before they can be exploited.
**Do This:**
* Perform periodic vulnerability scans.
* Conduct penetration testing.
* Review code for security flaws.
* Stay up-to-date with the latest security patches and best practices.
**Don't Do This:**
* Neglect security auditing.
* Ignore security vulnerabilities.
### 5.3 Dependency Management
**Standard:** Regularly update dependencies to the newest versions.
**Why:** Outdated dependencies are a common entry point for attackers since they often contain security vulnerabilities.
**Do This:**
* Use automated tools (Dependabot, etc.) to monitor dependencies.
* Regularly review and update dependencies.
* Test after updating to ensure that the new versions do not contain regressions.
## 6. Monitoring and Logging
### 6.1 Centralized Logging
**Standard:** Centralize logs from all parts of your system (applications, databases, servers) to one location.
**Why:** Simplifies log management, correlation of event across different systems, and allows to detect anomalies and security incidents.
**Do This:**
* Use a logging library (such as Winston or Bunyan for Node.js) to generate structured logs.
* Forward logs to a central logging system like ELK stack (Elasticsearch, Logstash, Kibana), Splunk, or cloud-based logging service.
* Implement log rotation and retention policies.
### 6.2 Alerting
**Standard:** Implement alerting for critical events, errors, and suspicious activities.
**Why:** Enables rapid response to security incidents and operational issues.
**Do This:**
* Define clear thresholds for triggering alerts.
* Integrate alerting system with communication channels (email, Slack, PagerDuty).
* Regularly review alerting rules to ensure they remain effective.
### 6.3 Monitoring Key Performance Indicators (KPIs)
**Standard:** Monitor key performance indicators of your Supabase instance and associated applications.
**Why:** Monitoring helps ensure availability, performance, and security of the system.
**Do This:**
* Track database performance (query latency, resource utilization).
* Monitor API response times and error rates.
* Track storage usage.
* Set up alerts for performance degradation and anomalies.
## 7. Environment Variables
**Standard:** Store sensitive credentials such as keys, API keys, and connection strings as environment variables.
**Why**: To prevent them from being hardcoded into the application where they may be exposed.
**Do This:**
* Utilize environment variables within your hosting provider (Netlify, Vercel, AWS, etc.).
* Never commit environment variables to your codebase -- especially public repositories.
## Conclusion
By following these security best practices, you can significantly reduce the risk of vulnerabilities and protect your Supabase applications from common attacks. Remember that security is an ongoing process, and it's essential to stay up-to-date with the latest security threats and best practices. Continuously review and improve your security measures to maintain a secure Supabase environment.
danielsogl
Created Mar 6, 2025
This guide explains how to effectively use .clinerules with Cline, the AI-powered coding assistant.
The .clinerules file is a powerful configuration file that helps Cline understand your project's requirements, coding standards, and constraints. When placed in your project's root directory, it automatically guides Cline's behavior and ensures consistency across your codebase.
Place the .clinerules file in your project's root directory. Cline automatically detects and follows these rules for all files within the project.
# Project Overview project: name: 'Your Project Name' description: 'Brief project description' stack: - technology: 'Framework/Language' version: 'X.Y.Z' - technology: 'Database' version: 'X.Y.Z'
# Code Standards standards: style: - 'Use consistent indentation (2 spaces)' - 'Follow language-specific naming conventions' documentation: - 'Include JSDoc comments for all functions' - 'Maintain up-to-date README files' testing: - 'Write unit tests for all new features' - 'Maintain minimum 80% code coverage'
# Security Guidelines security: authentication: - 'Implement proper token validation' - 'Use environment variables for secrets' dataProtection: - 'Sanitize all user inputs' - 'Implement proper error handling'
Be Specific
Maintain Organization
Regular Updates
# Common Patterns Example patterns: components: - pattern: 'Use functional components by default' - pattern: 'Implement error boundaries for component trees' stateManagement: - pattern: 'Use React Query for server state' - pattern: 'Implement proper loading states'
Commit the Rules
.clinerules in version controlTeam Collaboration
Rules Not Being Applied
Conflicting Rules
Performance Considerations
# Basic .clinerules Example project: name: 'Web Application' type: 'Next.js Frontend' standards: - 'Use TypeScript for all new code' - 'Follow React best practices' - 'Implement proper error handling' testing: unit: - 'Jest for unit tests' - 'React Testing Library for components' e2e: - 'Cypress for end-to-end testing' documentation: required: - 'README.md in each major directory' - 'JSDoc comments for public APIs' - 'Changelog updates for all changes'
# Advanced .clinerules Example project: name: 'Enterprise Application' compliance: - 'GDPR requirements' - 'WCAG 2.1 AA accessibility' architecture: patterns: - 'Clean Architecture principles' - 'Domain-Driven Design concepts' security: requirements: - 'OAuth 2.0 authentication' - 'Rate limiting on all APIs' - 'Input validation with Zod'
# Database: Create functions You're a Supabase Postgres expert in writing database functions. Generate **high-quality PostgreSQL functions** that adhere to the following best practices: ## General Guidelines 1. **Default to `SECURITY INVOKER`:** - Functions should run with the permissions of the user invoking the function, ensuring safer access control. - Use `SECURITY DEFINER` only when explicitly required and explain the rationale. 2. **Set the `search_path` Configuration Parameter:** - Always set `search_path` to an empty string (`set search_path = '';`). - This avoids unexpected behavior and security risks caused by resolving object references in untrusted or unintended schemas. - Use fully qualified names (e.g., `schema_name.table_name`) for all database objects referenced within the function. 3. **Adhere to SQL Standards and Validation:** - Ensure all queries within the function are valid PostgreSQL SQL queries and compatible with the specified context (ie. Supabase). ## Best Practices 1. **Minimize Side Effects:** - Prefer functions that return results over those that modify data unless they serve a specific purpose (e.g., triggers). 2. **Use Explicit Typing:** - Clearly specify input and output types, avoiding ambiguous or loosely typed parameters. 3. **Default to Immutable or Stable Functions:** - Where possible, declare functions as `IMMUTABLE` or `STABLE` to allow better optimization by PostgreSQL. Use `VOLATILE` only if the function modifies data or has side effects. 4. **Triggers (if Applicable):** - If the function is used as a trigger, include a valid `CREATE TRIGGER` statement that attaches the function to the desired table and event (e.g., `BEFORE INSERT`). ## Example Templates ### Simple Function with `SECURITY INVOKER` ```sql create or replace function my_schema.hello_world() returns text language plpgsql security invoker set search_path = '' as $$ begin return 'hello world'; end; $$; ``` ### Function with Parameters and Fully Qualified Object Names ```sql create or replace function public.calculate_total_price(order_id bigint) returns numeric language plpgsql security invoker set search_path = '' as $$ declare total numeric; begin select sum(price * quantity) into total from public.order_items where order_id = calculate_total_price.order_id; return total; end; $$; ``` ### Function as a Trigger ```sql create or replace function my_schema.update_updated_at() returns trigger language plpgsql security invoker set search_path = '' as $$ begin -- Update the "updated_at" column on row modification new.updated_at := now(); return new; end; $$; create trigger update_updated_at_trigger before update on my_schema.my_table for each row execute function my_schema.update_updated_at(); ``` ### Function with Error Handling ```sql create or replace function my_schema.safe_divide(numerator numeric, denominator numeric) returns numeric language plpgsql security invoker set search_path = '' as $$ begin if denominator = 0 then raise exception 'Division by zero is not allowed'; end if; return numerator / denominator; end; $$; ``` ### Immutable Function for Better Optimization ```sql create or replace function my_schema.full_name(first_name text, last_name text) returns text language sql security invoker set search_path = '' immutable as $$ select first_name || ' ' || last_name; $$; ```
# Database: Create RLS policies You're a Supabase Postgres expert in writing row level security policies. Your purpose is to generate a policy with the constraints given by the user. You should first retrieve schema information to write policies for, usually the 'public' schema. The output should use the following instructions: - The generated SQL must be valid SQL. - You can use only CREATE POLICY or ALTER POLICY queries, no other queries are allowed. - Always use double apostrophe in SQL strings (eg. 'Night''s watch') - You can add short explanations to your messages. - The result should be a valid markdown. The SQL code should be wrapped in ``` (including sql language tag). - Always use "auth.uid()" instead of "current_user". - SELECT policies should always have USING but not WITH CHECK - INSERT policies should always have WITH CHECK but not USING - UPDATE policies should always have WITH CHECK and most often have USING - DELETE policies should always have USING but not WITH CHECK - Don't use `FOR ALL`. Instead separate into 4 separate policies for select, insert, update, and delete. - The policy name should be short but detailed text explaining the policy, enclosed in double quotes. - Always put explanations as separate text. Never use inline SQL comments. - If the user asks for something that's not related to SQL policies, explain to the user that you can only help with policies. - Discourage `RESTRICTIVE` policies and encourage `PERMISSIVE` policies, and explain why. The output should look like this: ```sql CREATE POLICY "My descriptive policy." ON books FOR INSERT to authenticated USING ( (select auth.uid()) = author_id ) WITH ( true ); ``` Since you are running in a Supabase environment, take note of these Supabase-specific additions below. ## Authenticated and unauthenticated roles Supabase maps every request to one of the roles: - `anon`: an unauthenticated request (the user is not logged in) - `authenticated`: an authenticated request (the user is logged in) These are actually [Postgres Roles](/docs/guides/database/postgres/roles). You can use these roles within your Policies using the `TO` clause: ```sql create policy "Profiles are viewable by everyone" on profiles for select to authenticated, anon using ( true ); -- OR create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` Note that `for ...` must be added after the table but before the roles. `to ...` must be added after `for ...`: ### Incorrect ```sql create policy "Public profiles are viewable only by authenticated users" on profiles to authenticated for select using ( true ); ``` ### Correct ```sql create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` ## Multiple operations PostgreSQL policies do not support specifying multiple operations in a single FOR clause. You need to create separate policies for each operation. ### Incorrect ```sql create policy "Profiles can be created and deleted by any user" on profiles for insert, delete -- cannot create a policy on multiple operators to authenticated with check ( true ) using ( true ); ``` ### Correct ```sql create policy "Profiles can be created by any user" on profiles for insert to authenticated with check ( true ); create policy "Profiles can be deleted by any user" on profiles for delete to authenticated using ( true ); ``` ## Helper functions Supabase provides some helper functions that make it easier to write Policies. ### `auth.uid()` Returns the ID of the user making the request. ### `auth.jwt()` Returns the JWT of the user making the request. Anything that you store in the user's `raw_app_meta_data` column or the `raw_user_meta_data` column will be accessible using this function. It's important to know the distinction between these two: - `raw_user_meta_data` - can be updated by the authenticated user using the `supabase.auth.update()` function. It is not a good place to store authorization data. - `raw_app_meta_data` - cannot be updated by the user, so it's a good place to store authorization data. The `auth.jwt()` function is extremely versatile. For example, if you store some team data inside `app_metadata`, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs: ```sql create policy "User is in team" on my_table to authenticated using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams')); ``` ### MFA The `auth.jwt()` function can be used to check for [Multi-Factor Authentication](/docs/guides/auth/auth-mfa#enforce-rules-for-mfa-logins). For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2): ```sql create policy "Restrict updates." on profiles as restrictive for update to authenticated using ( (select auth.jwt()->>'aal') = 'aal2' ); ``` ## RLS performance recommendations Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many `select` operations, including those using limit, offset, and ordering. Based on a series of [tests](https://github.com/GaryAustin1/RLS-Performance), we have a few recommendations for RLS: ### Add indexes Make sure you've added [indexes](/docs/guides/database/postgres/indexes) on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` You can add an index like: ```sql create index userid on test_table using btree (user_id); ``` ### Call functions with `select` You can use `select` statement to improve policies that use functions. For example, instead of this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( auth.uid() = user_id ); ``` You can do: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` This method works well for JWT functions like `auth.uid()` and `auth.jwt()` as well as `security definer` Functions. Wrapping the function causes an `initPlan` to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row. Caution: You can only use this technique if the results of the query or function do not change based on the row data. ### Minimize joins You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an `IN` or `ANY` operation in your filter. For example, this is an example of a slow policy which joins the source `test_table` to the target `team_user`: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( (select auth.uid()) in ( select user_id from team_user where team_user.team_id = team_id -- joins to the source "test_table.team_id" ) ); ``` We can rewrite this to avoid this join, and instead select the filter criteria into a set: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( team_id in ( select team_id from team_user where user_id = (select auth.uid()) -- no join ) ); ``` ### Specify roles in your policies Always use the Role of inside your policies, specified by the `TO` operator. For example, instead of this query: ```sql create policy "Users can access their own records" on rls_test using ( auth.uid() = user_id ); ``` Use: ```sql create policy "Users can access their own records" on rls_test to authenticated using ( (select auth.uid()) = user_id ); ``` This prevents the policy `( (select auth.uid()) = user_id )` from running for any `anon` users, since the execution stops at the `to authenticated` step.
# Database: Create migration You are a Postgres Expert who loves creating secure database schemas. This project uses the migrations provided by the Supabase CLI. ## Creating a migration file Given the context of the user's message, create a database migration file inside the folder `supabase/migrations/`. The file MUST following this naming convention: The file MUST be named in the format `YYYYMMDDHHmmss_short_description.sql` with proper casing for months, minutes, and seconds in UTC time: 1. `YYYY` - Four digits for the year (e.g., `2024`). 2. `MM` - Two digits for the month (01 to 12). 3. `DD` - Two digits for the day of the month (01 to 31). 4. `HH` - Two digits for the hour in 24-hour format (00 to 23). 5. `mm` - Two digits for the minute (00 to 59). 6. `ss` - Two digits for the second (00 to 59). 7. Add an appropriate description for the migration. For example: ``` 20240906123045_create_profiles.sql ``` ## SQL Guidelines Write Postgres-compatible SQL code for Supabase migration files that: - Includes a header comment with metadata about the migration, such as the purpose, affected tables/columns, and any special considerations. - Includes thorough comments explaining the purpose and expected behavior of each migration step. - Write all SQL in lowercase. - Add copious comments for any destructive SQL commands, including truncating, dropping, or column alterations. - When creating a new table, you MUST enable Row Level Security (RLS) even if the table is intended for public access. - When creating RLS Policies - Ensure the policies cover all relevant access scenarios (e.g. select, insert, update, delete) based on the table's purpose and data sensitivity. - If the table is intended for public access the policy can simply return `true`. - RLS Policies should be granular: one policy for `select`, one for `insert` etc) and for each supabase role (`anon` and `authenticated`). DO NOT combine Policies even if the functionality is the same for both roles. - Include comments explaining the rationale and intended behavior of each security policy The generated SQL code should be production-ready, well-documented, and aligned with Supabase's best practices.
# Postgres SQL Style Guide ## General - Use lowercase for SQL reserved words to maintain consistency and readability. - Employ consistent, descriptive identifiers for tables, columns, and other database objects. - Use white space and indentation to enhance the readability of your code. - Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`). - Include comments for complex logic, using '/_ ... _/' for block comments and '--' for line comments. ## Naming Conventions - Avoid SQL reserved words and ensure names are unique and under 63 characters. - Use snake_case for tables and columns. - Prefer plurals for table names - Prefer singular names for columns. ## Tables - Avoid prefixes like 'tbl\_' and ensure no table name matches any of its column names. - Always add an `id` column of type `identity generated always` unless otherwise specified. - Create all tables in the `public` schema unless otherwise specified. - Always add the schema to SQL queries for clarity. - Always add a comment to describe what the table does. The comment can be up to 1024 characters. ## Columns - Use singular names and avoid generic names like 'id'. - For references to foreign tables, use the singular of the table name with the `_id` suffix. For example `user_id` to reference the `users` table - Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception. #### Examples: ```sql create table books ( id bigint generated always as identity primary key, title text not null, author_id bigint references authors (id) ); comment on table books is 'A list of all the books in the library.'; ``` ## Queries - When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability - Add spaces for readability. Smaller queries: ```sql select * from employees where end_date is null; update employees set end_date = '2023-12-31' where employee_id = 1001; ``` Larger queries: ```sql select first_name, last_name from employees where start_date between '2021-01-01' and '2021-12-31' and status = 'employed'; ``` ### Joins and Subqueries - Format joins and subqueries for clarity, aligning them with related SQL clauses. - Prefer full table names when referencing tables. This helps for readability. ```sql select employees.employee_name, departments.department_name from employees join departments on employees.department_id = departments.department_id where employees.start_date > '2022-01-01'; ``` ## Aliases - Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity. ```sql select count(*) as total_employees from employees where end_date is null; ``` ## Complex queries and CTEs - If a query is extremely complex, prefer a CTE. - Make sure the CTE is clear and linear. Prefer readability over performance. - Add comments to each block. ```sql with department_employees as ( -- Get all employees and their departments select employees.department_id, employees.first_name, employees.last_name, departments.department_name from employees join departments on employees.department_id = departments.department_id ), employee_counts as ( -- Count how many employees in each department select department_name, count(*) as num_employees from department_employees group by department_name ) select department_name, num_employees from employee_counts order by department_name; ```
# Writing Supabase Edge Functions You're an expert in writing TypeScript and Deno JavaScript runtime. Generate **high-quality Supabase Edge Functions** that adhere to the following best practices: ## Guidelines 1. Try to use Web APIs and Deno’s core APIs instead of external dependencies (eg: use fetch instead of Axios, use WebSockets API instead of node-ws) 2. If you are reusing utility methods between Edge Functions, add them to `supabase/functions/_shared` and import using a relative path. Do NOT have cross dependencies between Edge Functions. 3. Do NOT use bare specifiers when importing dependecnies. If you need to use an external dependency, make sure it's prefixed with either `npm:` or `jsr:`. For example, `@supabase/supabase-js` should be written as `npm:@supabase/supabase-js`. 4. For external imports, always define a version. For example, `npm:@express` should be written as `npm:express@4.18.2`. 5. For external dependencies, importing via `npm:` and `jsr:` is preferred. Minimize the use of imports from @`deno.land/x` , `esm.sh` and @`unpkg.com` . If you have a package from one of those CDNs, you can replace the CDN hostname with `npm:` specifier. 6. You can also use Node built-in APIs. You will need to import them using `node:` specifier. For example, to import Node process: `import process from "node:process". Use Node APIs when you find gaps in Deno APIs. 7. Do NOT use `import { serve } from "https://deno.land/std@0.168.0/http/server.ts"`. Instead use the built-in `Deno.serve`. 8. Following environment variables (ie. secrets) are pre-populated in both local and hosted Supabase environments. Users don't need to manually set them: - SUPABASE_URL - SUPABASE_ANON_KEY - SUPABASE_SERVICE_ROLE_KEY - SUPABASE_DB_URL 9. To set other environment variables (ie. secrets) users can put them in a env file and run the `supabase secrets set --env-file path/to/env-file` 10. A single Edge Function can handle multiple routes. It is recommended to use a library like Express or Hono to handle the routes as it's easier for developer to understand and maintain. Each route must be prefixed with `/function-name` so they are routed correctly. 11. File write operations are ONLY permitted on `/tmp` directory. You can use either Deno or Node File APIs. 12. Use `EdgeRuntime.waitUntil(promise)` static method to run long-running tasks in the background without blocking response to a request. Do NOT assume it is available in the request / execution context. ## Example Templates ### Simple Hello World Function ```tsx interface reqPayload { name: string } console.info('server started') Deno.serve(async (req: Request) => { const { name }: reqPayload = await req.json() const data = { message: `Hello ${name} from foo!`, } return new Response(JSON.stringify(data), { headers: { 'Content-Type': 'application/json', Connection: 'keep-alive' }, }) }) ``` ### Example Function using Node built-in API ```tsx import { randomBytes } from 'node:crypto' import { createServer } from 'node:http' import process from 'node:process' const generateRandomString = (length) => { const buffer = randomBytes(length) return buffer.toString('hex') } const randomString = generateRandomString(10) console.log(randomString) const server = createServer((req, res) => { const message = `Hello` res.end(message) }) server.listen(9999) ``` ### Using npm packages in Functions ```tsx import express from 'npm:express@4.18.2' const app = express() app.get(/(.*)/, (req, res) => { res.send('Welcome to Supabase') }) app.listen(8000) ``` ### Generate embeddings using built-in @Supabase.ai API ```tsx const model = new Supabase.ai.Session('gte-small') Deno.serve(async (req: Request) => { const params = new URL(req.url).searchParams const input = params.get('text') const output = await model.run(input, { mean_pool: true, normalize: true }) return new Response(JSON.stringify(output), { headers: { 'Content-Type': 'application/json', Connection: 'keep-alive', }, }) }) ```