This guide will walk you through the process of creating a secure, read-only Supabase user specifically for connecting to the application. Supabase uses PostgreSQL, so some steps will be similar to PostgreSQL setup.

​
Why Create a Read-Only User?

Creating a dedicated read-only user ensures:

  1. Enhanced Security: Limits potential data manipulation risks.
  2. Data Integrity: Prevents accidental modifications to your database.
  3. Compliance: Adheres to the principle of least privilege.

​
Step-by-Step Guide

Follow these steps to create your Supabase read-only user:

​
1. Access Supabase SQL Editor

First, log in to your Supabase project and navigate to the SQL Editor.

​
2. Create the Read-Only User

Create a new user with a secure password:

CREATE USER readonly WITH PASSWORD ‘your_secure_password’;

Replace ‘your_secure_password’ with a strong, unique password. Consider using a password manager to generate and store this securely.

​
3. Grant Connect Privilege

Grant the user the ability to connect to the database:

GRANT CONNECT ON DATABASE postgres TO readonly;

In Supabase, the main database is typically named ‘postgres’.

​
4. Grant Schema Usage

Grant usage on the public schema and any other schemas you want the user to access:

GRANT USAGE ON SCHEMA public TO readonly;

​
5. Grant Select Privileges

Grant SELECT privileges on all tables in the schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

​
6. Grant Additional Necessary Privileges

For full read-only access, grant privileges on sequences:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

​
7. Apply Row Level Security (RLS) Policies

Ensure that appropriate Row Level Security policies are in place:

ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY “Read access for readonly user” ON your_table FOR SELECT TO readonly USING (true);

Repeat the above for each table that needs RLS. Adjust the USING clause as needed to restrict access to specific rows.

​
Verifying the User Setup

To confirm that the user has been set up correctly:

  1. Use the Supabase SQL Editor to run queries as the readonly user:

    SET ROLE readonly; SELECT * FROM your_table LIMIT 5;

  2. Attempt to modify data (this should fail):

    SET ROLE readonly; INSERT INTO your_table (column1, column2) VALUES (‘test’, ‘data’);

If you can select data but not modify it, your read-only user is set up correctly!

​
Connecting to Your Application

When setting up your connection in your application:

  1. Use the Supabase project’s connection string or host
  2. Enter ‘readonly’ as the username
  3. Use the secure password you set in step 2
  4. Use the default database name (typically ‘postgres’)

Supabase provides connection information in the project settings. Make sure to use these details when configuring your application.

​
Supabase-Specific Notes

  • Row Level Security (RLS): Supabase heavily relies on RLS. Ensure your RLS policies are correctly set up for the readonly user.
  • Realtime Subscriptions: If using Supabase’s realtime features, ensure the readonly user has appropriate permissions for subscriptions.
  • Supabase API: If you’re using Supabase’s API, you’ll need to create an API key with read-only permissions instead of a database user.
  • Supabase Functions: For serverless functions, use appropriate service roles that have read-only access.

​
Troubleshooting

If you encounter issues:

  • Check the Supabase logs in the dashboard for detailed error messages
  • Verify that all necessary privileges have been granted
  • Ensure the user has been created successfully
  • Check that you’re using the correct connection details in your application
  • Verify that RLS policies are not overly restrictive

For further assistance, consult the Supabase documentation or community forums.

​
Must-Know Details

  1. Connection Pooling: Supabase uses PgBouncer for connection pooling. Be aware of this when troubleshooting connection issues.
  2. SSL Connections: Supabase enforces SSL connections. Ensure your application supports this.
  3. Automatic Backups: Supabase provides automatic backups. The readonly user won’t affect these, but it’s good to be aware of this feature.
  4. Schema Migrations: When applying schema migrations, remember to update permissions for the readonly user if new tables or schemas are added.
  5. Supabase Dashboard: While you can create a readonly database user, many operations in Supabase are designed to be done through the dashboard or API.

By following this guide, you’ve created a secure, read-only Supabase user that’s perfectly suited for connecting to your application. This setup ensures that your data remains protected while allowing your application to perform necessary read operations effectively.

Creating a Microsoft SQL Server Read-Only UserChangelog