Postgres (GCP instance) schema upgrade (1.20.0) - error executing statement:pq: permission denied to create extension btree_gin

dhiaayachi commented 2 months ago

Postgres schema upgrade fails (Temporal 1.20) on creating btree_gin extension - related with advanced visibility - requires super user permissions

Expected Behavior

based on the current code, the outcome could be expected, but i did not find mentions about that the temporal_visiblity user needs to be super user with admin privileges (that can use untrusted languages with extensions)

Actual Behavior

schema upgrade fails:

2023-03-20T09:12:56.897Z    INFO    UpdateSchemeTask started    {"config": {"DBName":"","TargetVersion":"","SchemaDir":"schema/postgresql/v12/temporal/versioned","IsDryRun":false}, "logging-call-at": "updatetask.go:98"}
2023-03-20T09:12:56.900Z    DEBUG   Schema Dirs: [] {"logging-call-at": "updatetask.go:187"}
2023-03-20T09:12:56.900Z    DEBUG   found zero updates from current version 1.9 {"logging-call-at": "updatetask.go:128"}
2023-03-20T09:12:56.900Z    INFO    UpdateSchemeTask done   {"logging-call-at": "updatetask.go:121"}
2023-03-20T09:12:57.210Z    INFO    UpdateSchemeTask started    {"config": {"DBName":"","TargetVersion":"","SchemaDir":"schema/postgresql/v12/visibility/versioned","IsDryRun":false}, "logging-call-at": "updatetask.go:98"}
2023-03-20T09:12:57.214Z    DEBUG   Schema Dirs: [v1.2] {"logging-call-at": "updatetask.go:187"}
2023-03-20T09:12:57.214Z    INFO    Processing schema file: schema/postgresql/v12/visibility/versioned/v1.2/advanced_visibility.sql {"logging-call-at": "updatetask.go:233"}
2023-03-20T09:12:57.215Z    DEBUG   ---- Executing updates for version 1.2 ---- {"logging-call-at": "updatetask.go:151"}
2023-03-20T09:12:57.215Z    DEBUG   CREATE EXTENSION btree_gin; {"logging-call-at": "updatetask.go:153"}
2023-03-20T09:12:57.232Z    ERROR   Unable to update SQL schema.    {"error": "error executing statement:pq: permission denied to create extension \"btree_gin\"", "logging-call-at": "handler.go:78"}
command terminated with exit code 1

Steps to Reproduce the Problem

The requirement could be GCP specific (DB instance - Postgres 12) There you cannot use the "super admin" but you can use "postgres" user or create a new (non-super) user

Start GCP postgres 12 instance with temporal_db and temporal_visibility_db databases and temporal / temporal_visibility user
Execute schema upgrade

Here there is also a possible GCP specific issue (Im discussing this with them in the meantime) - as in the doc: https://cloud.google.com/sql/docs/postgres/users#superuser_restrictions you cannot create a new super user, but you can add a role cloudsqlsuperuser to any user (by postgres user), but this is still not enough, my guess it's still need a super user, my guess is here: https://www.postgresql.org/docs/current/catalog-pg-language.html - the problem could be with lanpltrusted field - as by default "c" is untrusted (that could be not considered by cloudsqlsuperuser role) - and that is used by btree_gin extension. Although "postgres" user can assign roles to the users, but cannot update pg_language table.

Note that postgres user can create this extension, but in that case the temporal schema upgrade will fail with "extension already exists" error.

So, because of this, the question is, is it possible to add "IF NOT EXISTS" will be in the CREATE EXTENSION statement here: https://github.com/temporalio/temporal/blob/master/schema/postgresql/v12/visibility/versioned/v1.2/advanced_visibility.sql#L1 ? Also if i would workaround this with passing a patched sql script on volume for our admintools container - if i will execute the schema upgrade on that, is it safe to do? (so if next time the script changes is it will re-run the schema upgrade or not)

Specifications

Version: 1.20
Platform: GCP / GKE

dhiaayachi commented 1 month ago

Thank you for reporting this issue. It seems like you are encountering an issue with the temporal_visibility user needing superuser privileges to create the btree_gin extension.

As mentioned in the documentation https://www.postgresql.org/docs/current/catalog-pg-language.html, the lanpltrusted field in the pg_language table is responsible for determining if a language is trusted. By default, "c" is untrusted, which could be the reason why the cloudsqlsuperuser role is not sufficient. The postgres user has the necessary privileges to update this table, but cannot do so from a temporal schema upgrade.

To work around this issue, you can create the btree_gin extension manually using the postgres user before running the schema upgrade. This will ensure that the extension is available when the upgrade script tries to create it.

Regarding your question about the safety of executing a patched SQL script, executing a patched SQL script directly on your database is not recommended for official Temporal schema upgrades. The schema changes are tested and documented, and applying a patched script might introduce unintended consequences.

If you need to make custom changes to the schema for your particular setup, it's recommended to create a separate script that applies the required modifications after the official schema upgrade is complete.

We are actively working on improving this aspect of Temporal, and we appreciate your feedback.

dhiaayachi commented 1 month ago

Thank you for reporting this issue.

We understand that the schema upgrade fails due to the btree_gin extension requiring superuser permissions. It seems you're using GCP Postgres, and creating superusers is restricted there.

The btree_gin extension is used for the advanced visibility features.

To work around this, you can try the following:

Patching the SQL script: As you mentioned, you can execute the schema upgrade using a patched script. This will bypass the superuser requirement. Make sure to patch the script in a way that adds IF NOT EXISTS to the CREATE EXTENSION statement as suggested in the issue, so it doesn't fail with an "extension already exists" error in subsequent runs.
Creating a new user with necessary privileges: If possible, create a new user with the cloudsqlsuperuser role. This role should grant sufficient privileges to create extensions. Alternatively, try creating a new user with CREATE EXTENSION permission, but make sure to verify that it doesn't conflict with other Temporal components.
Switching to a different database: If the above options are not feasible, you can consider switching to a different database like MySQL or Cassandra. The default visibility store is Cassandra, which doesn't require a superuser to create extensions.

Here is the link to the relevant documentations:

Please let us know if you have any other questions or if you need further assistance.

dhiaayachi / temporal