Complete Guide to PostgREST and PostgreSQL Role Permission Configuration

Introduction

PostgREST, as a lightweight API server, offers the core advantage of exposing PostgreSQL database tables directly as RESTful interfaces without writing backend APIs. Its normal operation highly depends on PostgreSQL role and permission configuration. This article focuses on PostgREST core configuration, PostgreSQL role creation and authorization, combining production environment practical scenarios to organize directly copyable configuration templates and operation commands, helping backend developers quickly master PostgREST and database permission integration.

Part One: PostgREST Core Configuration (Production-Ready Direct Copy)

PostgREST's configuration file is postgrest.conf, where database connection and role permission-related configurations are core. Below is a complete production-level configuration template, with permission-related items highlighted:

# Database connection (must be completely consistent with backend service like Java yml configuration)
db-uri = "postgres://postgres:postgres2024@10.62.204.32:5432/gansueq"

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

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

# Service port and address (default 3000, can be modified according to needs)
server-port = 3000
server-host = "0.0.0.0"

# 🔴 Key: Allow frontend custom token headers (must configure if using custom token verification)
request-headers = "token"

# Cross-origin configuration (adjust according to frontend actual address to avoid cross-origin interception)
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 interface documentation for improved security
openapi-mode = "disabled"

Core Configuration Item Explanations (Permission-Related Key Points Highlighted)

Part Two: PostgreSQL Role and Permission Configuration (Must-Do Operations)

PostgREST itself doesn't implement permission control, completely relying on PostgreSQL's role and permission mechanisms. Among these, the web_anon role is the core of PostgREST anonymous access. Below are complete role creation and authorization steps, directly copyable SQL for execution.

1. Core Role Creation (web_anon)

Create PostgREST-specific anonymous role 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, table query permissions, ensuring web_anon role can normally access all tables under public schema, suitable for most production scenarios:

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

-- 3. Grant web_anon role query permissions for all tables under 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, no need for repeated authorization
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO web_anon;

3. Permission Configuration Explanations (Pit-Avoidance Key Points)

• NOLOGIN keyword: web_anon role is only for PostgREST access, no need to allow direct database login, improving security.
• USAGE permission: Only allows role to access schema, doesn't include table operation permissions, is prerequisite for table access.
• SELECT permission: Allocate according to business needs; if interfaces need to support insert, update, delete, can append GRANT INSERT/UPDATE/DELETE permissions.
• Default permission configuration: If tables will be created subsequently, recommend executing ALTER DEFAULT PRIVILEGES statement to avoid manual authorization for each table creation.

Part Three: Configuration and Permission Linkage Notes (Production Pit-Avoidance)

• Configuration and role consistency: db-anon-role = "web_anon" in PostgREST configuration must be completely consistent with the role name created in PostgreSQL, otherwise will report 400 error (error=22023).
• Restart required after permission updates: After modifying PostgreSQL role permissions, creating new tables, or modifying table structures, must restart PostgREST (PostgREST caches table structures and permissions at startup, doesn't automatically refresh during operation).
• Multi-environment consistency: When deploying across multiple servers, need to ensure PostgREST configuration, PostgreSQL roles and permissions are completely consistent across all servers, avoiding environment difference-caused interface errors.
• Security recommendations: In production environments, avoid granting excessive permissions to web_anon role (not recommended to grant ALL permissions), only allocate minimum permissions needed by interfaces (like SELECT-only permissions).
• Error troubleshooting: If PostgREST interfaces report 400 Bad Request (error=22023), first check whether web_anon role exists and permissions are complete (high probability is role missing or insufficient permissions).

Part Four: Windows Environment PostgREST Restart Method (Must-Do After Permission/Configuration Updates)

After permission or configuration modifications, need to restart PostgREST for changes to take effect. Simplest restart method in Windows environment:

• Close the running PostgREST black window (directly click window close button).
• Find PostgREST installation directory, double-click postgrest.exe to restart.

• Startup success verification: Black window outputs following logs, indicating cache updated, permission configuration effective:

  Successfully connected to PostgreSQL
  Schema cache loaded

Part Five: Common Problems and Solutions

Problem 1: When executing authorization SQL, prompt "role web_anon does not exist"?

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

Problem 2: PostgREST starts normally, but interfaces report 400 error (error=22023)?

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

Problem 3: After permission updates, interfaces still report permission-related errors?

Solution: Confirm permissions correctly granted and PostgREST restarted; if still reporting errors, check whether db-uri configuration is correct (whether connected to correct database).

Summary

The core of PostgREST and PostgreSQL role permission configuration is "consistency"—the anonymous role in PostgREST configuration must exist in the database and possess corresponding schema and table permissions; simultaneously note that after permission and table structure changes, must restart PostgREST to refresh cache.

The configuration templates and SQL commands provided in this article can be directly applied to production environments, especially for earthquake emergency response, data query-type systems. Only need to adjust according to actual database address, username and password to quickly complete PostgREST and database permission integration, avoiding interface errors caused by configuration or permission issues.