[CRITICAL] Event Triggers | Extensions can only be created on heroku_ext schema

[joaorbdg]

Environment

Cloud

What is the current behaviour?

If you connect a Postgres database that runs in Heroku to your Hasura instance, you cannot create Event Triggers on Hasura.

The reason is that Heroku doesn't allow creating Postgres extensions in the public schema. Heroku only allow creating extensions in a schema called heroku_ext.

What is the expected behaviour?

Be able to create event triggers on Postgres databases that run in Heroku.

How to reproduce the issue?

1. Create a new Hasura cloud instance and connect a Postgres DB that runs in Heroku.
2. Go to the page to create an event trigger: https://cloud.hasura.io/project/<PROJECT_ID>/console/events/data/add
3. Try to create an event trigger.

Screenshots or Screencast

Please provide any traces or logs that could help here.

{
    "statement": "CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA public",
    "prepared": false,
    "error": {
        "exec_status": "FatalError",
        "hint": null,
        "message": "Extensions can only be created on heroku_ext schema",
        "status_code": "P0001",
        "description": null
    },
    "arguments": []
}

Any possible solutions?

Maybe during the event trigger creation, having a field that allows defining in which schema the extensions should be created.

Can you identify the location in the source code where the problem exists?

No.

If the bug is confirmed, would you be willing to submit a PR?

No.

Keywords

event, triggers, heroku, postgres, error, heroku_ext, schema, public

[chiaberry]

https://github.com/hasura/graphql-engine/pull/496/files

[AwakenedMind]

ran into this issue as well on Hasura Cloud when I try to upload metadata after previously deleting it https://devcenter.heroku.com/changelog-items/2446

[etrepum]

This won't fix the issue with Hasura cloud, but I have worked around this in Docker by patching the binary to replace SCHEMA public with whitespace.

RUN apt-get install -y bbe upx
# Patch graphql-engine to work with heroku
RUN (upx -d /bin/graphql-engine || true) && \
  bbe -e 's/CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA public/CREATE EXTENSION IF NOT EXISTS pgcrypto              /' /bin/graphql-engine > /tmp/graphql-engine && \
  mv /tmp/graphql-engine /bin/graphql-engine && \
  chmod +x /bin/graphql-engine

[AwakenedMind]

Anyone who is using Hasura Cloud and trying to restore metadata on a Heroku db is kinda screwed, Heroku ended up breaking many backend tooling across many businesses according to some of the posts I've seen online. They seemed to have forecfully injected heroku_ext schema on production databases and expect sql extensions to be loaded in that schema... I ended up leaving Heroku altogether.

1. used pg_dump on our heroku db
2. exported the Hasura metadata json file
3. used pg_restore on a AWS RDS instance
4. connect the AWS RDS Instance on Hasura Cloud and imported the metadata json file back

[ro-savage]

It would be great if Hasura could check if the extension is installed, and then if it was just skip trying to install it. This would fix Heroku deploys, and be a non-breaking change for everyone else.

Alternatively the command run could be CREATE EXTENSION IF NOT EXISTS pgcrypto dropping the schema per etrepum's comment.

However - I am not sure how this would affect other users?

I've also raised the issue with Heroku directly -- since I don't believe CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA public should fail if its already available on the path (the failure appears to be a Heroku created error message, that is being incorrectly thrown).

For now though -- Hasura + Heroku is broken. @rakeshkky @0x777 @tirumaraiselvan

[tirumaraiselvan]

I've also raised the issue with Heroku directly -- since I don't believe CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA public should fail if its already available on the path

This is true. In regular Postgres, such a scenario doesn't fail. There seems to something off with Heroku PG and extensions. If you install the extension in heroku_ext, it doesn't show up in the result of select * from pg_extension either.

[tirumaraiselvan]

FYI, we are working on adding the ability to add "extensions_schema" when adding a PG source so you can explicitly specify the schema where pgcrypto should be installed on the source db.

[ro-savage]

@tirumaraiselvan - Unfortunately it appears 2.9.0-beta is also broken on heroku (but in a different way) https://github.com/hasura/graphql-engine/issues/8770

[qbunt]

just hit the same thing with being blocked from installing pgcrypto into the public schema for UUID support.

IMO, this isn't something that Hasura should be fixing, and is entirely in Heroku's court. This thread isn't the only place people are displeased by this change.

[tirumaraiselvan]

Hey folks, this has been fixed as of v2.11.0-beta.1 . There are 2 places where pgrcypto is required: in metadata db and source db (only if using event triggers)

If you are using Heroku PG as the metadata DB, then pls set HASURA_GRAPHQL_METADATA_DATABASE_EXTENSIONS_SCHEMA: heroku_ext

If you are using event triggers on your source db (which is Heroku PG), then pls set extensions_schema field in the source metadata to heroku_ext . Can be done via console as well:

[konnyaku256]

Thank you for your prompt response!

I'm currently having the exact same problem. Hopefully this will be officially released as soon as possible.

[kurt-o-sys]

@tirumaraiselvan I still have issues to get Hasura running running. It used to work, broke suddenly and can't get the application running again: https://stackoverflow.com/questions/73274923/hasura-on-heroku-fails-to-make-connection-to-postgres . I even can't access the Hasura console, because the application simply doesn't start. When I connect from Hasura cloud, it seems to work (but in that case, I'm missing my metadata). (Not sure what I'm missing. I tried different releases of Hasura, including v2.11.0-beta.1 and different settings in my Dockerfile.)