Hasura possibly needs to be smarter about creating the pgcrypto extension

[dventimiglia]

Version Information

Server Version: 2.1.1 CLI Version (for CLI related issue):

Environment

OSS

What is the expected behaviour?

Migrations should issue CREATE EXTENSION IF NOT EXISTS pgcrypto before DDL that uses the gen_random_uuid() function, not after.

Keywords

pgcrypto gen_random_uuid

What is the current behaviour?

In v2.1.1 at least, Hasura creates migrations in up.sql that issue DDL that reference the gen_random_uuid() function first and then issue the CREATE EXTENSION IF NOT EXISTS pgcrypto statement second. This will fail in PostgreSQL versions 12 and before, because the pgcrypto extension supplies the gen_random_uuid function. If this is a bug, it's subtle however, because it will silently not fail in versions of PostgreSQL 13 and after, since gen_random_uuid was moved into PostgreSQL core and it's no longer necessary to add the pgcrypto extension to get that function.

How to reproduce the issue?

1. Iterate on a local Hasura instance to generate migrations.
2. Deploy those migrations to another Hasura instance in the cloud in Hasura HQ
3. Make sure that cloud Hasura instance is connected to a version of PostgreSQL v12 or earlier.
4. Note that the migrations fail leaving the metadata in an inconsistent state.
5. Repeat steps 2 & 3 but for a cloud Hasura instance connected to a version of PostgreSQL v 13 or later.
6. Note that the migrations succeed

Screenshots or Screencast

https://youtu.be/CYEYbplbyv8

Please provide any traces or logs that could help here.

Any possible solutions?

There are at least two possible solutions:

• Install pgcrypto during DB init, like is promised in the docs
• Alternatively, ensure that CREATE EXTENSION IF NOT EXISTS pgcrypto is always added to migration files first

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

I'm afraid I don't know the source code or even Haskell at all.

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

I suppose I could try.

[dventimiglia]

I think it might be as simple as changing sqlQueries.push(sqlCreateExtension) to sqlQueries.unshift(sqlCreateExtension) in sqlUtils.ts.

I can try doing that and testing it.