Graphql-engine: create pgcrypto extension by default for uuid support

[shahidhk]

Right now, when a UUID column is created from console, console executes CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;

But if the user try to create a table with UUID column from run SQL or psql, as shown in this blog it will fail.

We should create the pgcrypto extension on graphql engine init step.

[tirumaraiselvan]

I am actually installing pgcrypto for event engine in init step : https://github.com/tirumaraiselvan/graphql-engine/blob/4e8a1617f3956439bd33fe0aa5245bf282732497/server/src-rsr/initialise.sql#L205

[revskill10]

@shahidhk @tirumaraiselvan How about passing array of extensions as argument when running graphql-engine ?

[shahidhk]

Added in https://github.com/hasura/graphql-engine/commit/82e09efce68f7b771338079564db1edc7919f9c7

[jasonmorganson]

Creating the extension fails if not using superuser.

From the container logs:

{"internal":{"statement":"-- required for generating uuid\nCREATE EXTENSION IF NOT EXISTS pgcrypto;\n\nCREATE TABLE hdb_catalog.event_triggers\n(\n id TEXT DEFAULT gen_random_uuid() PRIMARY KEY,\n name TEXT UNIQUE,\n type TEXT NOT NULL,\n schema_name TEXT NOT NULL,\n table_name TEXT NOT NULL,\n definition JSON,\n query TEXT,\n webhook TEXT NOT NULL,\n num_retries INTEGER DEFAULT 0,\n retry_interval INTEGER DEFAULT 10,\n comment TEXT\n);\n\nCREATE TABLE hdb_catalog.event_log\n(\n id TEXT DEFAULT gen_random_uuid() PRIMARY KEY,\n schema_name TEXT NOT NULL,\n table_name TEXT NOT NULL,\n trigger_id TEXT NOT NULL,\n trigger_name TEXT NOT NULL,\n payload JSONB NOT NULL,\n delivered BOOLEAN NOT NULL DEFAULT FALSE,\n error BOOLEAN NOT NULL DEFAULT FALSE,\n tries INTEGER NOT NULL DEFAULT 0,\n created_at TIMESTAMP DEFAULT NOW(),\n locked BOOLEAN NOT NULL DEFAULT FALSE\n);\n\nCREATE TABLE hdb_catalog.event_invocation_logs\n(\n id TEXT DEFAULT gen_random_uuid() PRIMARY KEY,\n event_id TEXT,\n status INTEGER,\n request JSON,\n response JSON,\n created_at TIMESTAMP DEFAULT NOW(),\n\n FOREIGN KEY (event_id) REFERENCES hdb_catalog.event_log (id)\n);\n","prepared":false,"error":{"exec_status":"FatalError","hint":"Must be superuser to create this extension.","message":"permission denied to create extension \"pgcrypto\"","status_code":"42501","description":null},"arguments":[]},"path":"$","error":"postgres query error","code":"postgres-error"}

[shahidhk]

Thanks for reporting this @jasonmorganson. We're working on a fix.

[0x777]

We should check if the extension is already loaded, if it is not, try to create it and if it fails, we exit by saying that pgcrypto extension has to be loaded for graphql-engine to work.

[shahidhk]

@jasonmorganson If you haven't already done so, you can fix this issue by executing the following statement as a superuser:

CREATE EXTENSION IF NOT EXISTS pgcrypto;