Database Roles and Permissions for Self-Hosted Supabase

Understanding Postgres roles is fundamental to securing your self-hosted Supabase deployment. Unlike managed Supabase Cloud where role configuration happens behind the scenes, self-hosting puts you in direct control of database access. This means understanding how the anon, authenticated, service_role, and postgres roles interact—and knowing when to create custom roles for your specific needs.

This guide covers the role system in depth, from the default Supabase roles to creating custom permission hierarchies that match your application's authorization requirements.

How Postgres Roles Work in Supabase

Postgres uses roles as its primary access control mechanism. In Postgres terminology, a "role" can function as either a user (with login privileges) or a group (without login privileges but used to manage permissions).

Supabase extends Postgres with a set of predefined roles that integrate with PostgREST (the API layer) and GoTrue (the auth service). When a request hits your Supabase API, PostgREST connects to Postgres using the authenticator role, validates the JWT, then switches to the appropriate role based on the token's role claim.

The Default Supabase Roles

Here's how each default role functions:

postgres: The superuser role with full administrative privileges. Use this for migrations, schema changes, and administrative tasks. Never expose this in application code.

authenticator: A limited role that PostgREST uses to connect to the database. It validates JWTs and then "becomes" another role based on the token. You shouldn't modify this role.

anon: For unauthenticated requests. When no valid JWT is present, PostgREST uses this role. Your Row-Level Security policies determine what anon can access.

authenticated: For logged-in users. When a valid JWT is present (not anonymous sign-in), PostgREST switches to this role. Most application data access happens through this role.

service_role: Bypasses Row-Level Security entirely. Used for server-side operations that need unrestricted database access. Never expose the SERVICE_ROLE_KEY to client code.

Self-Hosted Role Configuration

When you deploy Supabase with Docker, the official Supabase Postgres image includes all necessary role configurations. However, understanding how to verify and modify these roles is essential for production deployments.

Verifying Role Configuration

Connect to your Postgres instance and list all roles:

SELECT rolname, rolsuper, rolcanlogin, rolbypassrls
FROM pg_roles
WHERE rolname IN ('anon', 'authenticated', 'authenticator', 'service_role', 'postgres');

You should see:

Notice that anon and authenticated cannot login directly—they're accessed via authenticator. The service_role has rolbypassrls set to true, which is why it ignores RLS policies.

Environment Variables for API Keys

Your .env file must include properly configured keys that map to these roles:

# Generate these using a secure method - never use defaults
ANON_KEY=eyJ...  # JWT with role: "anon"
SERVICE_ROLE_KEY=eyJ...  # JWT with role: "service_role"
JWT_SECRET=your-secure-secret-min-32-chars

The ANON_KEY is safe to expose in client applications because access is controlled by RLS policies. The SERVICE_ROLE_KEY must remain server-side only—it bypasses all security policies.

Granting Permissions to Roles

Postgres permissions control what each role can do with database objects. The GRANT command assigns permissions:

-- Allow authenticated users to read from a table
GRANT SELECT ON public.posts TO authenticated;

-- Allow insert and update operations
GRANT INSERT, UPDATE ON public.posts TO authenticated;

-- Allow anonymous users to read public content
GRANT SELECT ON public.public_posts TO anon;

-- Grant all permissions (use sparingly)
GRANT ALL ON public.admin_logs TO postgres;

Schema-Level Grants

Roles also need permission to access schemas:

-- Allow authenticated role to use the public schema
GRANT USAGE ON SCHEMA public TO authenticated;

-- Allow access to all tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO authenticated;

-- Apply to future tables as well
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO authenticated;

For self-hosted deployments, the Supabase Postgres image handles default grants. But if you create new schemas or have specific requirements, you'll need to manage these manually.

Checking Current Grants

View existing permissions on a table:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name = 'posts';

Creating Custom Roles

The default anon, authenticated, and service_role cover most use cases when combined with RLS. However, custom roles make sense when you need:

• Different API access levels (e.g., premium_user, admin)
• Separation of concerns between services
• Third-party integrations with limited access

Basic Custom Role Creation

-- Create a custom role without login (for API access via JWT)
CREATE ROLE premium_user NOLOGIN;

-- Grant it to authenticator so PostgREST can switch to it
GRANT premium_user TO authenticator;

-- Grant necessary permissions
GRANT USAGE ON SCHEMA public TO premium_user;
GRANT SELECT, INSERT, UPDATE ON public.posts TO premium_user;
GRANT SELECT ON public.premium_content TO premium_user;

Using Custom Roles with JWTs

To use custom roles through the Supabase API, you need to issue JWTs with the custom role in the role claim. This requires auth hooks or a custom token generation system:

-- Auth hook to assign custom role based on user data
CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb
LANGUAGE plpgsql
AS $$
DECLARE
  user_role text;
BEGIN
  -- Look up user's subscription status
  SELECT subscription_tier INTO user_role
  FROM public.user_profiles
  WHERE user_id = (event->>'user_id')::uuid;
  
  -- Modify the role claim
  IF user_role = 'premium' THEN
    event := jsonb_set(event, '{claims,role}', '"premium_user"');
  END IF;
  
  RETURN event;
END;
$$;

Enable the hook in your GoTrue environment:

# docker-compose.yml
services:
  auth:
    environment:
      GOTRUE_HOOK_CUSTOM_ACCESS_TOKEN_ENABLED: "true"
      GOTRUE_HOOK_CUSTOM_ACCESS_TOKEN_URI: "pg-functions://postgres/public/custom_access_token_hook"

Role Hierarchies

Postgres supports role inheritance, allowing you to build permission hierarchies:

-- Base role with common permissions
CREATE ROLE base_user NOLOGIN;
GRANT USAGE ON SCHEMA public TO base_user;
GRANT SELECT ON public.posts TO base_user;

-- Premium role inherits from base and adds more
CREATE ROLE premium_user NOLOGIN INHERIT;
GRANT base_user TO premium_user;
GRANT SELECT ON public.premium_content TO premium_user;

-- Admin role with full access
CREATE ROLE admin_user NOLOGIN INHERIT;
GRANT premium_user TO admin_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;

The INHERIT option means premium_user automatically has all permissions granted to base_user.

Role-Specific RLS Policies

RLS policies can target specific roles, providing fine-grained access control:

-- Policy for anonymous users (read-only on published content)
CREATE POLICY "Anon can read published posts"
ON public.posts FOR SELECT
TO anon
USING (published = true);

-- Policy for authenticated users (CRUD on own posts)
CREATE POLICY "Users manage own posts"
ON public.posts FOR ALL
TO authenticated
USING ((SELECT auth.uid()) = author_id);

-- Policy for premium users (access premium content)
CREATE POLICY "Premium users read premium content"
ON public.premium_content FOR SELECT
TO premium_user
USING (true);

-- Policy for admins (full access)
CREATE POLICY "Admins have full access"
ON public.posts FOR ALL
TO admin_user
USING (true)
WITH CHECK (true);

Always specify the TO clause in your policies. Without it, the policy applies to PUBLIC (all roles), which can lead to unintended access.

Security Best Practices

Principle of Least Privilege

Grant only the minimum permissions each role needs:

-- Bad: Overly permissive
GRANT ALL ON ALL TABLES IN SCHEMA public TO authenticated;

-- Good: Specific permissions per table
GRANT SELECT ON public.posts TO authenticated;
GRANT SELECT, INSERT ON public.comments TO authenticated;
GRANT SELECT, UPDATE ON public.user_profiles TO authenticated;

Protect System Tables

Never grant access to system schemas:

-- These should remain off-limits to application roles
REVOKE ALL ON SCHEMA auth FROM authenticated;
REVOKE ALL ON SCHEMA storage FROM authenticated;

The Supabase Postgres image configures this by default, but verify after migrations or updates.

Audit Role Permissions Regularly

Create a view to monitor role permissions:

CREATE VIEW public.role_permissions AS
SELECT 
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.role_table_grants
WHERE grantee IN ('anon', 'authenticated', 'service_role', 'premium_user', 'admin_user')
ORDER BY grantee, table_schema, table_name;

Review this periodically to catch permission drift.

Revoke Unused Permissions

Clean up permissions you no longer need:

-- Remove a specific permission
REVOKE DELETE ON public.posts FROM authenticated;

-- Remove all permissions on a table
REVOKE ALL ON public.deprecated_table FROM authenticated;

-- Remove a role entirely
DROP ROLE IF EXISTS unused_custom_role;

Troubleshooting Role Issues

"Permission Denied" Errors

When you see "permission denied for table X", check:

1. Does the role have SELECT/INSERT/UPDATE/DELETE on the table?
2. Does the role have USAGE on the schema?
3. Is RLS enabled, and does a policy grant access?

-- Check table permissions
SELECT has_table_privilege('authenticated', 'public.posts', 'SELECT');

-- Check schema permissions
SELECT has_schema_privilege('authenticated', 'public', 'USAGE');

RLS Blocking Access Despite Grants

Remember that GRANT and RLS are separate layers. A role needs both:

• GRANT permission to access the table
• An RLS policy that matches the query

-- This won't work even with SELECT grant if no policy exists
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- You need at least one policy
CREATE POLICY "Allow authenticated read"
ON public.posts FOR SELECT
TO authenticated
USING (true);

Custom Roles Not Working via API

If PostgREST can't switch to your custom role:

1. Verify the role is granted to authenticator:

SELECT pg_has_role('authenticator', 'your_custom_role', 'MEMBER');

2. Check your JWT contains the correct role claim
3. Ensure the role exists and has necessary grants

Managing Roles with Supascale

Role management is critical for production self-hosted Supabase deployments. Supascale simplifies the operational burden so you can focus on proper security configuration:

• Project isolation: Each Supabase project runs independently with its own role configuration
• Automated backups: Your role configurations are preserved in database backups
• Quick recovery: Restore from backup if role changes cause issues

The one-time $39.99 purchase includes unlimited projects, making it cost-effective to maintain separate staging and production instances with different role configurations.

Key Takeaways

1. Understand the defaults: anon, authenticated, and service_role cover most use cases when combined with RLS
2. Custom roles for custom needs: Create them when you need distinct API access levels beyond authenticated/unauthenticated
3. Layer your security: GRANT controls table access, RLS controls row access—you need both
4. Audit regularly: Monitor permissions and revoke unused grants
5. Never expose service_role: It bypasses all security measures

Proper role configuration is foundational to Supabase security. Get this right, and your RLS policies have a solid base to build on.

Further Reading

• Row Level Security for Self-Hosted Supabase
• Auth Hooks for Custom Claims and RBAC
• Securing Self-Hosted Supabase
• Postgres Roles Documentation