PostgREST and PostgreSQL Role Permissions: Production-Ready Configuration Guide

PostgREST has emerged as a lightweight API server solution, offering the core advantage of exposing PostgreSQL database tables directly as RESTful interfaces without requiring backend interface development. Its normal operation depends heavily on PostgreSQL role and permission configuration. This comprehensive guide focuses on PostgREST core configuration, PostgreSQL role creation and authorization, combining production environment practical scenarios to organize directly copyable configuration templates and operation instructions, helping backend developers quickly complete PostgREST and database permission integration.

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

The PostgREST configuration file is postgrest.conf. Among its settings, database connection and role permission-related configurations represent the core components. The following presents a complete production-level configuration template, with permission-related items prominently highlighted:

# Database Connection (Must match backend service Java yml configuration exactly)
db-uri = "postgres://postgres:postgres2024@10.62.204.32:5432/gansueq"

# Database Schema Name (Default: public, modify if tables exist 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 according to requirements)
server-port = 3000
server-host = "0.0.0.0"

# 🔴 Critical: Allow Frontend Custom Token Headers (Required for custom token authentication)
request-headers = "token"

# Cross-Origin Configuration (Adjust according to 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 Environment Recommendation: Disable Auto-Generated OpenAPI Documentation for Enhanced Security
openapi-mode = "disabled"

Core Configuration Item Explanations (Permission-Related Items Highlighted)

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

PostgREST itself doesn't implement permission control, relying entirely on PostgreSQL's role and permission mechanisms. Among these, the web_anon role serves as the core of PostgREST anonymous access. The following presents complete role creation and authorization steps, with directly copyable SQL for execution.

1. Core Role Creation (web_anon)

Create a dedicated anonymous role for PostgREST, used for interface access, without login permissions (NOLOGIN):

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

2. Core Permission Authorization (Ensuring PostgREST Can Access Tables)

Authorization divides into three steps: schema access permissions and table query permissions, ensuring the web_anon role can normally access all tables under the public schema, applicable to most production scenarios:

-- 2. Allow web_anon role to access public schema (Mandatory, otherwise tables under 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, automatically grant web_anon query permissions for subsequently created tables, avoiding repeated authorization
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO web_anon;

3. Permission Configuration Explanations (Critical Pitfall Avoidance)

• NOLOGIN Keyword: The web_anon role is only used for PostgREST access, no need to allow direct database login, enhancing security
• USAGE Permission: Only allows role to access schema, doesn't include table operation permissions, serves as prerequisite for table access
• SELECT Permission: Allocate according to business requirements. If interfaces need to support insert, update, delete operations,追加 GRANT INSERT/UPDATE/DELETE permissions
• Default Permission Configuration: If tables will be created subsequently, executing the ALTER DEFAULT PRIVILEGES statement is recommended, avoiding manual authorization for each new table

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

Configuration and Role Consistency

The db-anon-role = "web_anon" in PostgREST configuration must match the role name created in PostgreSQL exactly, otherwise error 400 will be reported (error=22023).

Permission Updates Require Restart

After modifying PostgreSQL role permissions, creating new tables, or modifying table structures, PostgREST must be restarted. PostgREST caches table structures and permissions at startup, not automatically refreshing during operation.

Multi-Environment Consistency

During multi-server deployment, ensure PostgREST configuration, PostgreSQL roles, and permissions remain completely consistent across all servers, avoiding environment difference-caused interface errors.

Security Recommendations

In production environments, avoid granting excessive permissions to the web_anon role (granting ALL permissions is not recommended). Only allocate minimum permissions required by interfaces (such as SELECT permissions only).

Error Troubleshooting

If PostgREST interfaces report 400 Bad Request (error=22023), prioritize checking whether the web_anon role exists and whether permissions are complete (high probability indicates role absence or insufficient permissions).

Part Four: Windows Environment PostgREST Restart Method (Mandatory After Permission/Configuration Updates)

After permission or configuration modifications, PostgREST must be restarted for changes to take effect. The simplest restart method in Windows environment:

1. Close the Running PostgREST Black Window (Directly click the window close button)
2. Locate the PostgREST Installation Directory, double-click postgrest.exe to restart
3. Successful Startup Verification: The black window outputs the following log, indicating cache has updated and permission configuration has taken effect:

Successfully connected to PostgreSQL
Schema cache loaded

Part Five: Common Problems and Solutions

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

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

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

Solution: Check whether the web_anon role possesses public schema USAGE permissions and table SELECT permissions. After confirmation, restart PostgREST.

Problem 3: Interfaces Still Report Permission-Related Errors After Permission Updates

Solution: Confirm permissions have been correctly granted and PostgREST has been restarted. If errors persist, check whether db-uri configuration is correct (whether connected to the correct database).

Part Six: Extended Considerations for Production Deployments

Connection Pool Management

For high-traffic production environments, consider implementing connection pooling between PostgREST and PostgreSQL. This reduces connection overhead and improves overall system throughput. Configuration involves adjusting PostgreSQL's max_connections parameter and potentially deploying PgBouncer as an intermediary connection pooler.

Monitoring and Logging

Implement comprehensive monitoring for PostgREST instances:

• Request Metrics: Track request rates, response times, and error rates
• Database Connection Health: Monitor connection pool utilization and query performance
• Permission Violation Attempts: Log unauthorized access attempts for security auditing
• Resource Utilization: Track CPU and memory usage to identify scaling needs

Backup and Recovery Strategies

Ensure robust backup procedures for both PostgREST configuration and PostgreSQL database:

• Configuration Versioning: Maintain version-controlled copies of postgrest.conf
• Database Backups: Implement regular automated backups with tested recovery procedures
• Role Permission Documentation: Document all custom roles and their permissions for quick recreation if needed

Load Balancing Considerations

For high-availability deployments, consider deploying multiple PostgREST instances behind a load balancer:

• Stateless Design: PostgREST's stateless nature makes it ideal for horizontal scaling
• Session Affinity: Generally not required, but may be needed for specific authentication flows
• Health Checks: Implement proper health check endpoints for load balancer integration

Part Seven: Security Hardening Recommendations

Network Isolation

• Deploy PostgREST in a private network segment, accessible only through designated gateways
• Implement firewall rules restricting database access to PostgREST server IP addresses only
• Use SSL/TLS encryption for all database connections in production environments

Authentication Enhancement

• Consider implementing JWT-based authentication for enhanced security
• Regularly rotate database passwords and update db-uri configuration accordingly
• Implement rate limiting to prevent brute-force attacks

Audit Logging

• Enable PostgreSQL's audit logging to track all database access
• Configure PostgREST logging to capture request details for security analysis
• Regularly review logs for suspicious patterns or unauthorized access attempts

Part Eight: Summary and Best Practices

The core of PostgREST and PostgreSQL role permission configuration lies in "consistency"—the anonymous role in PostgREST configuration must exist in the database and possess permissions for corresponding schemas and tables. Simultaneously, note that PostgREST must be restarted after permission and table structure changes to refresh the cache.

The configuration templates and SQL instructions provided in this guide can be directly applied to production environments, particularly for emergency response systems and data query applications. Only adjust according to actual database addresses, usernames, and passwords to quickly complete PostgREST and database permission integration, avoiding interface errors caused by configuration or permission issues.

Key Takeaways

1. Consistency is Critical: Ensure exact matching between configuration and database roles
2. Minimal Permissions: Grant only necessary permissions following the principle of least privilege
3. Restart After Changes: Always restart PostgREST after permission or schema modifications
4. Document Everything: Maintain clear documentation of roles, permissions, and configurations
5. Test Thoroughly: Validate all configurations in staging environments before production deployment
6. Monitor Continuously: Implement comprehensive monitoring for early issue detection
7. Plan for Scale: Design with future growth and high-availability requirements in mind

By following these guidelines and leveraging the provided templates, developers can establish robust, secure, and maintainable PostgREST deployments that serve as reliable foundations for modern API-driven applications.