Complete Guide to PostgREST and PostgreSQL Role Permission Configuration for Production Environments

PostgREST has emerged as a powerful lightweight API server that eliminates the need for writing traditional backend interfaces. Its core advantage lies in directly exposing PostgreSQL database tables as RESTful endpoints, dramatically reducing development time and complexity. However, the proper functioning of PostgREST depends entirely on correct PostgreSQL role and permission configuration.

This comprehensive guide focuses on essential PostgREST configuration, PostgreSQL role creation, and authorization strategies. We'll provide production-ready configuration templates and executable commands that backend developers can immediately apply to establish secure and efficient database-API connections.

Part One: Production-Ready PostgREST Core Configuration

The PostgREST configuration file (postgrest.conf) contains critical settings for database connections and role permissions. Below is a complete production-grade configuration template with detailed explanations of each permission-related component.

Complete Configuration Template

# Database connection string (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 exist in other schemas)
db-schema = "public"

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

# Service port and address (default 3000, modify based on requirements)
server-port = 3000
server-host = "0.0.0.0"

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

# Cross-origin configuration (adjust based on frontend actual addresses to avoid CORS 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"

Critical Configuration Item Explanations

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

PostgREST itself does not implement permission control—it relies entirely on PostgreSQL's role and permission mechanisms. The web_anon role serves as the core of PostgREST anonymous access. Below are complete role creation and authorization steps with executable SQL commands.

Step 1: Creating the Core Role (web_anon)

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

-- Create anonymous role (NOLOGIN indicates 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:

-- Allow web_anon role to access public schema (mandatory, otherwise cannot see tables in schema)
GRANT USAGE ON SCHEMA public TO web_anon;

-- 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, automatically grant web_anon query permissions for newly created tables, no need for repeated authorization
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO web_anon;

Step 3: Permission Configuration Explanations (Critical Pitfall Avoidance)

NOLOGIN Keyword: The web_anon role is used exclusively for PostgREST access and doesn't need direct database login capability, which enhances security.

USAGE Permission: This only allows the role to access the schema and does not include table operation permissions. It's a prerequisite for table access.

SELECT Permission: Allocate based on business requirements. If interfaces need to support insert, update, or delete operations, you can append GRANT INSERT/UPDATE/DELETE permissions accordingly.

Default Permission Configuration: If you'll create new tables in the future, executing the ALTER DEFAULT PRIVILEGES statement is recommended to avoid manual authorization for each new table.

Extended Permission Scenarios

For applications requiring write operations, extend permissions carefully:

-- Grant insert permissions for specific tables
GRANT INSERT ON TABLE users TO web_anon;

-- Grant update permissions
GRANT UPDATE ON TABLE orders TO web_anon;

-- Grant delete permissions (use with caution)
GRANT DELETE ON TABLE sessions TO web_anon;

-- Grant all permissions on specific tables (not recommended for production)
-- GRANT ALL ON TABLE temp_data TO web_anon;

Part Three: Configuration and Permission Integration Considerations (Production Pitfall Avoidance)

Configuration and Role Consistency

The db-anon-role = "web_anon" setting in PostgREST configuration must match the role name created in PostgreSQL exactly. Any discrepancy will result in 400 errors (error=22023). This is one of the most common configuration mistakes.

Restart Requirements After Permission Updates

After modifying PostgreSQL role permissions, creating new tables, or changing table structures, you must restart PostgREST. The reason: PostgREST caches table structures and permissions at startup and does not automatically refresh during runtime.

Multi-Environment Consistency

When deploying across multiple servers, ensure all servers have identical:

• PostgREST configuration files
• PostgreSQL roles and permissions
• Database connection strings

Environment differences are a frequent 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
• Apply principle of least privilege: Only allocate minimum permissions required by interfaces (e.g., SELECT-only for read-only endpoints)
• Regularly audit permissions: Review and remove unnecessary access rights

Error Troubleshooting Guide

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

1. Whether the web_anon role exists
2. Whether permissions are complete (most likely role is missing or permissions are insufficient)
3. Whether the db-uri configuration is correct

Part Four: PostgREST Restart Methods for Windows Environments (Mandatory After Permission/Configuration Updates)

After modifying permissions or configuration, restart PostgREST to apply changes. The simplest restart method for Windows environments:

1. Close the running PostgREST black window (click the window close button directly)
2. Navigate to 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

Linux/Mac Restart Methods

For Linux or macOS deployments:

# If running as a service
sudo systemctl restart postgrest

# If running manually, kill and restart
pkill postgrest
postgrest postgrest.conf &

# Verify it's running
ps aux | grep postgrest

Part Five: Common Problems and Solutions

Problem 1: "Role web_anon Does Not Exist" Error When Executing Authorization SQL

Solution: First execute CREATE ROLE web_anon NOLOGIN; to create the role, then execute authorization statements.

-- Create role first
CREATE ROLE web_anon NOLOGIN;

-- Then grant permissions
GRANT USAGE ON SCHEMA public TO web_anon;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO web_anon;

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

Solution: Verify that the web_anon role has USAGE permission on the public schema and SELECT permission on tables. After confirmation, restart PostgREST.

-- Check role exists
SELECT rolname FROM pg_roles WHERE rolname = 'web_anon';

-- Check schema permissions
SELECT * FROM information_schema.role_usage_grants 
WHERE grantee = 'web_anon' AND object_schema = 'public';

-- Check table permissions
SELECT * FROM information_schema.role_table_grants 
WHERE grantee = 'web_anon';

Problem 3: Permission-Related Errors Persist After Permission Updates

Solution:

1. Confirm permissions have been correctly granted
2. Verify PostgREST has been restarted
3. If errors persist, check whether db-uri configuration is correct (whether connected to the correct database)
4. Verify network connectivity between PostgREST server and PostgreSQL database

Problem 4: CORS Errors from Frontend

Solution: Adjust server-cors-allowed-origins in PostgREST configuration to include all frontend addresses:

server-cors-allowed-origins = "http://your-frontend-domain.com,https://app.your-domain.com"
server-cors-allowed-credentials = true

Part Six: Advanced Security Considerations

Row-Level Security (RLS)

For enhanced security, consider implementing PostgreSQL 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 (auth_uid() = user_id);

JWT Token Authentication

PostgREST supports JWT-based authentication for more granular access control:

# In postgrest.conf
db-anon-role = "web_anon"
db-pool-acquisition-timeout = 10

Frontend includes JWT token in requests:

Authorization: Bearer <your-jwt-token>

Connection Pooling Optimization

For high-traffic applications, optimize connection pooling:

db-pool-size = 20
db-pool-acquisition-timeout = 10
db-pool-max-lifetime = 300

Summary

The core of PostgREST and PostgreSQL role permission configuration lies in consistency: the anonymous role configured in PostgREST must exist in the database and possess corresponding schema and table permissions. Additionally, note that PostgREST must be restarted after permission and table structure changes to refresh its cache.

The configuration templates and SQL commands provided in this guide can be directly applied to production environments. For systems like earthquake emergency response and data query platforms, simply adjust based on actual database addresses, usernames, and passwords to quickly complete PostgREST and database permission integration. This approach avoids interface errors caused by configuration or permission issues, ensuring reliable and secure API operations.

By following these best practices, you'll establish a robust foundation for your PostgREST deployment that balances ease of development with production-grade security and performance.