Docker部署Tooljet升级后遭遇Public Schema错误求助

Root Cause #

The proxy_postgrest service in your Tooljet deployment is configured to only allow access to the public PostgreSQL schema, but your Tooljet core tables (like ACCOUNTS) are stored in a different schema (typically tooljet by default). This mismatch triggers the PGRST106 error when PostgREST attempts to access tables outside its allowed schema list.

Step-by-Step Resolution #

1. Confirm the Schema of Your Tooljet Tables #

First, verify which schema your Tooljet tables are in:

• Connect to your PostgreSQL container:

  docker-compose exec postgres psql -U YOUR_TOOLJET_DB_USER -d YOUR_TOOLJET_DB_NAME
  

  Replace YOUR_TOOLJET_DB_USER and YOUR_TOOLJET_DB_NAME with values from your .env file (look for TOOLJET_DB_USER and TOOLJET_DB_NAME).
• Run this SQL query to check the schema of the ACCOUNTS table:

  SELECT table_schema FROM information_schema.tables WHERE table_name = 'ACCOUNTS';
  

  The result will likely be tooljet.

2. Update PostgREST's Allowed Schemas #

Modify your configuration to include the correct schema:

• Open your .env file and locate (or add) the PGRST_DB_SCHEMAS variable. Set its value to include the schema from step 1:

  PGRST_DB_SCHEMAS=tooljet,public
  

• If you don't see this variable in .env, check your docker-compose.yml file's postgrest service section. Update the environment entry for PGRST_DB_SCHEMAS there instead.

3. Ensure Database User Permissions #

Make sure the Tooljet database user has full access to the schema:

• While still connected to PostgreSQL, run these commands (replace tooljet with your schema and YOUR_TOOLJET_DB_USER with your user):

  GRANT USAGE ON SCHEMA tooljet TO YOUR_TOOLJET_DB_USER;
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tooljet TO YOUR_TOOLJET_DB_USER;
  GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tooljet TO YOUR_TOOLJET_DB_USER;
  

4. Restart Postgrest Service #

Apply the changes by restarting the PostgREST container:

docker-compose restart postgrest

5. Verify the Fix #

Refresh your Tooljet application and attempt to access the tables again. The "schema must be public" error should no longer appear.

Additional Checks #

• Confirm your .env file has TOOLJET_DB_SCHEMA set to the same schema (e.g., TOOLJET_DB_SCHEMA=tooljet).
• Ensure all Tooljet core tables reside in the schema specified in your configuration.

内容的提问来源于stack exchange，提问作者iDOHandbags