Complete Guide to PostgREST and PostgreSQL Role Permission Configuration

PostgREST stands as a lightweight API server with a compelling core advantage: it eliminates the need for writing backend interfaces by directly exposing PostgreSQL database tables as RESTful endpoints. However, its proper operation depends heavily on PostgreSQL's role and permission configuration.

This comprehensive guide focuses on PostgREST core configuration, PostgreSQL role creation and authorization, combining production environment best practices. We'll provide ready-to-use configuration templates and operational commands to help backend developers quickly master PostgREST and database permission integration.

Part One: PostgREST Core Configuration (Production-Ready Template)

The PostgREST configuration file, postgrest.conf, contains critical settings for database connection and role permissions. Below is a complete production-grade configuration template with permission-related items prominently highlighted.

Complete Configuration Template

# Database connection (must match backend service configuration exactly)
db-uri = "postgres://postgres:postgres2024@10.62.204.32:5432/gansueq"

# Database schema name (default is public, modify if tables are in other schemas)
db-schema = "public"

# 🔴 CORE: PostgREST anonymous access role (must be created in PostgreSQL beforehand)
db-anon-role = "web_anon"

# Service port and address (default 3000, modify as needed)
server-port = 3000
server-host = "0.0.0.0"

# 🔴 KEY: Allow frontend to customize token headers (required for custom token authentication)
request-headers = "token"

# CORS configuration (adjust based on frontend actual addresses to avoid cross-origin blocking)
server-cors-allowed-origins = "http://10.62.210.66:8099,http://10.62.210.66:22516"
server-cors-allowed-credentials = true
server-cors-allowed-headers = "Content-Type, Authorization, Accept, token"
server-cors-allowed-methods = "GET, POST, PATCH, DELETE, OPTIONS"

# Production recommendation: Disable auto-generated OpenAPI documentation for enhanced security
openapi-mode = "disabled"

Core Configuration Item Explanations

Part Two: PostgreSQL Role and Permission Configuration (Required Operations)

Critical understanding: PostgREST itself does not implement permission control—it relies entirely on PostgreSQL's role and permission mechanisms. The web_anon role is the core of PostgREST anonymous access.

Below are complete role creation and authorization steps that you can execute directly by copying the SQL.

Step 1: Core Role Creation (web_anon)

Create a dedicated anonymous role for PostgREST interface access. This role requires no login privileges (NOLOGIN):

-- 1. Create anonymous role (NOLOGIN means this role cannot directly login, only for PostgREST access)
CREATE ROLE web_anon NOLOGIN;

Step 2: Core Permission Authorization

Authorization occurs in three steps: schema access permissions and table query permissions. This ensures the web_anon role can normally access all tables in the public schema, suitable for most production scenarios.

-- 2. Allow web_anon role to access public schema (REQUIRED, otherwise tables in schema cannot be seen)
GRANT USAGE ON SCHEMA public TO web_anon;

-- 3. Grant web_anon role query permissions for all tables in public schema (CORE, interfaces can normally query data)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO web_anon;

-- Optional: Set default permissions so newly created tables automatically grant web_anon query permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO web_anon;

Step 3: Permission Configuration Explanations (Critical Pitfalls to Avoid)

NOLOGIN Keyword:
The web_anon role is only for PostgREST access—there's no need to allow direct database login. This enhances security by preventing direct connection attempts.

USAGE Permission:
This only allows the role to access the schema; it does not include table operation permissions. However, it's a prerequisite for table access. Without USAGE on the schema, the role cannot even see the tables within it.

SELECT Permission:
Allocate based on business requirements. If interfaces need to support insert, update, or delete operations, you can append additional permissions:

GRANT INSERT ON ALL TABLES IN SCHEMA public TO web_anon;
GRANT UPDATE ON ALL TABLES IN SCHEMA public TO web_anon;
GRANT DELETE ON ALL TABLES IN SCHEMA public TO web_anon;

Default Permission Configuration:
If you'll be creating new tables in the future, we strongly recommend executing the ALTER DEFAULT PRIVILEGES statement. This avoids the need to manually grant permissions every time you create a table.

Part Three: Configuration and Permission Linkage Considerations (Production Pitfalls)

Consistency Between Configuration and Role

The db-anon-role = "web_anon" in PostgREST configuration must match exactly with the role name created in PostgreSQL. Any discrepancy will result in 400 errors (error=22023).

Common mistake: Typo in role name, or creating webanon instead of web_anon.

Restart Required After Permission Updates

After modifying PostgreSQL role permissions, creating new tables, or changing table structures, you must restart PostgREST.

Why? PostgREST caches table structures and permissions at startup and does not automatically refresh during runtime. Without a restart, your permission changes won't take effect.

Multi-Environment Consistency

When deploying across multiple servers, ensure all servers have:

• Identical PostgREST configurations
• Identical PostgreSQL roles and permissions
• Identical schema structures

Environment discrepancies are a common source of interface errors that can be difficult to debug.

Security Recommendations

In production environments, avoid granting excessive permissions to the web_anon role:

• Do NOT grant ALL permissions unless absolutely necessary
• Assign only the minimum permissions required for interface functionality
• For read-only APIs, SELECT permission is often sufficient
• Consider creating separate roles for different access levels

Error Troubleshooting Priority

If PostgREST interfaces report 400 Bad Request (error=22023), prioritize checking:

1. Does the web_anon role exist?
2. Does it have USAGE permission on the schema?
3. Does it have appropriate table permissions (SELECT, INSERT, etc.)?

Statistically, 90% of such errors stem from missing roles or insufficient permissions.

Part Four: Windows Environment PostgREST Restart Method

After permission or configuration updates, restart PostgREST to apply changes. In Windows environments, the simplest restart method:

Step-by-Step Restart Process

1. Close the running PostgREST black window (simply click the window close button)
2. Navigate to the PostgREST installation directory and double-click postgrest.exe to restart

3. Verify successful startup: The black window should output the following logs, indicating cache has been updated and permission configuration is effective:

   Successfully connected to PostgreSQL
   Schema cache loaded

Alternative: Command Line Restart

For automated scenarios, you can use command line:

taskkill /IM postgrest.exe /F
start "" "C:\path\to\postgrest.exe" "C:\path\to\postgrest.conf"

Part Five: Common Problems and Solutions

Problem 1: "Role web_anon Does Not Exist" Error

Symptom: When executing authorization SQL, you receive an error indicating the role doesn't exist.

Solution: First execute the role creation statement:

CREATE ROLE web_anon NOLOGIN;

Then execute the authorization statements.

Problem 2: PostgREST Starts Normally, But Interfaces Report 400 Error (error=22023)

Symptom: PostgREST appears to start successfully, but API calls fail with 400 errors.

Solution:

1. Verify web_anon role has USAGE permission on public schema
2. Verify web_anon role has SELECT permission on tables
3. Restart PostgREST after confirming permissions

Problem 3: Permission-Related Errors Persist After Permission Updates

Symptom: You've updated permissions and restarted, but errors continue.

Solution:

1. Confirm permissions were correctly granted (re-check with \dp in psql)
2. Confirm PostgREST was actually restarted (check process ID changed)
3. Verify db-uri configuration is correct (is it connecting to the right database?)
4. Check for connection pooling issues that might maintain old connections

Problem 4: CORS Errors from Frontend

Symptom: Browser console shows CORS policy blocking requests.

Solution:

1. Verify server-cors-allowed-origins includes your frontend URL exactly
2. Ensure server-cors-allowed-credentials = true if sending cookies/credentials
3. Check that frontend is sending requests to the correct PostgREST port

Part Six: Advanced Permission Scenarios

Row-Level Security (RLS) Integration

For finer-grained access control, consider PostgreSQL's Row-Level Security:

-- Enable RLS on a table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy allowing users to see only their own data
CREATE POLICY user_isolation ON users
    USING (user_id = current_setting('app.current_user_id')::uuid);

Multiple Roles for Different Access Levels

Consider creating separate roles for different API endpoints:

CREATE ROLE web_readonly NOLOGIN;
CREATE ROLE web_readwrite NOLOGIN;

GRANT USAGE ON SCHEMA public TO web_readonly, web_readwrite;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO web_readonly;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO web_readwrite;

Then configure different PostgREST instances or use JWT claims to switch roles dynamically.

Summary

The core of PostgREST and PostgreSQL role permission configuration is consistency:

• The anonymous role configured in PostgREST must exist in the database
• That role must have appropriate permissions on the corresponding schema and tables
• After permission and table structure changes, PostgREST must be restarted to refresh cache

The configuration templates and SQL commands provided in this guide can be directly applied to production environments. This is particularly valuable for emergency response systems, data query platforms, and rapid prototyping scenarios.

Simply adjust the actual database address, username, and password according to your specific situation, and you can quickly complete the PostgREST and database permission integration. This approach helps you avoid interface errors caused by configuration or permission issues, saving valuable development and debugging time.

Remember: Proper permission configuration is not just about making things work—it's about building secure, maintainable, production-ready systems from the start.