allow specifying the schema where pgcrypto is (or can be) installed

[dilame]

Right after i installed it to heroku and input my existing database URL. Now i have 43 routines with names like crypto, pgp_*****_***** mixed with my own business-logic routines. It's a bad experience for me 😿 You are using hdb_*** schemas, why you need to modify my public?

[rikinsk]

@dilame These don't seem to be Hasura specific routines. These would either be default Postgres routines or routines added by some Postgres extentions.

[dilame]

But it appeared right after i deployed hasura. I started developing this database 2 weeks ago, i'm perfectly know all the routines and extensions in it. This routines wasn't exist before i give hasura access.

[tirumaraiselvan]

@rikinsk Does Hasura try to install pgcrypto if its not present?

[rikinsk]

@tirumaraiselvan I don't believe so. Installing it is a part of the Postgres permissions setup instructions we have in docs but I dont believe it is done automatically.

@ecthiender can add more insights

[ecthiender]

@tirumaraiselvan @rikinsk looks like it does https://github.com/hasura/graphql-engine/blob/master/server/src-lib/Hasura/Server/Migrate.hs#L95

Not sure why we create it in public schema though.

[0x777]

pgcrypto provides gen_random_uuid() which can be used as a default for uuid columns. Some of graphql-engine's uuid columns use this function. So why not install this extension into hdb_catalog schema? Currently in Postgres, an extension can only be installed once and only into a single schema, so if the extension is installed into hdb_catalog schema, you'll have to depend on hdb_catalog schema if you were to use gen_random_uuid or any of the pgcrypto functions in your own schemas. You shouldn't have such a dependency as it wouldn't be easy to clean your database of any graphql-engine related changes. Hence, pgcrypto extension is installed in public schema.

[dilame]

Maybe we should install this extension in pgcrypto schema? It looks like a right answer for me. pgcrypto is module, so it should have it's own namespace. If i need any of it's functions i can use it regardless hasura, and hasura still have an access to necessary functions.
pgcrypto.gen_random_uuid() looks pretty-nice

[0x777]

Maybe we should install this extension in pgcrypto schema?

I agree. We can maybe provide a flag to specify the schema in which pgcrypto extension is installed. I'm changing the title to reflect this.

[dilame]

One more thing: i run command on existing db before hasura init

CREATE EXTENSION pgcrypto SCHEMA utils;

After it hasura doesn't start. I think it's more of a bug than an enhancement.

{"exec_status":"FatalError",
"hint":"No function matches the given name and argument types. You might need to add explicit type casts.",
"message":"function gen_random_uuid() does not exist",
"status_code":"42883",
"description":null},"arguments":[]},"path":"$","error":"postgres query error","code":"unexpected"}

[tirumaraiselvan]

@dilame You will need to include the schema utils in the postgres search_path for the user/role that Hasura connects with: https://www.postgresql.org/docs/12/ddl-schemas.html#DDL-SCHEMAS-PATH

Pls make sure that it is set permanently for the user (role) and not just for the current session: https://stackoverflow.com/questions/2875610/permanently-set-postgresql-schema-path

[W1M0R]

@tirumaraiselvan I have the same issue as @dilame, and doing a set search_path to utils, public; still causes the same hasura error. The hasura error message says, function gen_random_uuid() does not exist.

[tirumaraiselvan]

@W1M0R Can you check if it's permanently set?

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase

[W1M0R]

@tirumaraiselvan The code here tries to create pgcrypto in the public schema if it does not exist https://github.com/hasura/graphql-engine/blob/master/server/src-lib/Hasura/Server/Migrate.hs#L117

Also, if I run your query I get no results.

[tirumaraiselvan]

@W1M0R Yes, the original issue still exists. Currently, if pgcrypto is not installed then Hasura will install it in public schema. If it's installed and not available in search_path then hasura will throw an error (although there is an issue with error reporting in v1.2). To solve this, you can add the schema in your user's search_path.

[dilame]

Hey guys, what's with this issue? :) I just started a new project and it happened again

[angrocode]

The silent installation of the extension is awful!
Users have the right to control the installation and make their own decisions.
There are three options:

1. Use the built-in function PG 13
   https://www.postgresql.org/docs/13/functions-uuid.html

2. Write a wrapper around the pgcrypto functions in the form of your own extension.

hasura_uuid.control

# hasura extension
comment = 'hasura uuid functions'
default_version = '1.0'
module_pathname = '$libdir/pgcrypto'
relocatable = true
trusted = true

hasura_uuid--1.0.sql

CREATE FUNCTION hasura_uuid()
RETURNS uuid
AS 'MODULE_PATHNAME', 'pg_random_uuid'
LANGUAGE C VOLATILE PARALLEL SAFE;

3. Use installation pgcrypto if extension hasura_uuid cannot be installed.

In the graphql-engine settings, select the functions used.

[holloway]

Not sure if it's related but I'm getting an error on Hasura Cloud that's preventing even making a connection,

[
    {
        "internal": {
            "statement": "/* We define our own uuid generator function that uses gen_random_uuid() underneath.\n   Since the column default is not directly referencing gen_random_uuid(),\n   it prevents the column default to be dropped when pgcrypto or public schema is dropped unwittingly.\n\n   See https://github.com/hasura/graphql-engine/issues/4217\n */\nCREATE OR REPLACE FUNCTION hdb_catalog.gen_hasura_uuid() RETURNS uuid AS\n  -- We assume gen_random_uuid() is available in the search_path.\n  -- This may not be true but we can't do much till https://github.com/hasura/graphql-engine/issues/3657\n'select gen_random_uuid()' LANGUAGE SQL;\n\nCREATE TABLE hdb_catalog.hdb_source_catalog_version(\n  version TEXT NOT NULL,\n  upgraded_on TIMESTAMPTZ NOT NULL\n);\n\nCREATE UNIQUE INDEX hdb_source_catalog_version_one_row\nON hdb_catalog.hdb_source_catalog_version((version IS NOT NULL));\n\nCREATE TABLE hdb_catalog.event_log\n(\n  id TEXT DEFAULT hdb_catalog.gen_hasura_uuid() PRIMARY KEY,\n  schema_name TEXT NOT NULL,\n  table_name 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  /* when locked IS NULL the event is unlocked and can be processed */\n  locked TIMESTAMPTZ,\n  next_retry_at TIMESTAMP,\n  archived BOOLEAN NOT NULL DEFAULT FALSE\n);\n\nCREATE INDEX ON hdb_catalog.event_log (trigger_name);\nCREATE INDEX ON hdb_catalog.event_log (locked);\nCREATE INDEX ON hdb_catalog.event_log (delivered);\nCREATE INDEX ON hdb_catalog.event_log (created_at);\n\nCREATE TABLE hdb_catalog.event_invocation_logs\n(\n  id TEXT DEFAULT hdb_catalog.gen_hasura_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\nCREATE INDEX ON hdb_catalog.event_invocation_logs (event_id);\n\nCREATE OR REPLACE FUNCTION\n  hdb_catalog.insert_event_log(schema_name text, table_name text, trigger_name text, op text, row_data json)\n  RETURNS text AS $$\n  DECLARE\n    id text;\n    payload json;\n    session_variables json;\n    server_version_num int;\n    trace_context json;\n  BEGIN\n    id := gen_random_uuid();\n    server_version_num := current_setting('server_version_num');\n    IF server_version_num >= 90600 THEN\n      session_variables := current_setting('hasura.user', 't');\n      trace_context := current_setting('hasura.tracecontext', 't');\n    ELSE\n      BEGIN\n        session_variables := current_setting('hasura.user');\n      EXCEPTION WHEN OTHERS THEN\n                  session_variables := NULL;\n      END;\n      BEGIN\n        trace_context := current_setting('hasura.tracecontext');\n      EXCEPTION WHEN OTHERS THEN\n        trace_context := NULL;\n      END;\n    END IF;\n    payload := json_build_object(\n      'op', op,\n      'data', row_data,\n      'session_variables', session_variables,\n      'trace_context', trace_context\n    );\n    INSERT INTO hdb_catalog.event_log\n                (id, schema_name, table_name, trigger_name, payload)\n    VALUES\n    (id, schema_name, table_name, trigger_name, payload);\n    RETURN id;\n  END;\n$$ LANGUAGE plpgsql;\n",
            "prepared": false,
            "error": {
                "exec_status": "FatalError",
                "hint": "No function matches the given name and argument types. You might need to add explicit type casts.",
                "message": "function gen_random_uuid() does not exist",
                "status_code": "42883",
                "description": null
            },
            "arguments": []
        },
        "definition": "default",
        "reason": "database query error",
        "type": "source"
    }
]

[incorvia]

I started getting this error.. what is going on here.. what's the solution?

[mikejcooper]

psql cli run CREATE EXTENSION "pgcrypto";

[tirumaraiselvan]

Hey folks, this is possible 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)

For your metadata DB, pls set HASURA_GRAPHQL_METADATA_DATABASE_EXTENSIONS_SCHEMA env var to the appropriate schema name.

If you are using event triggers on your source db, then pls set extensions_schema field in the source metadata to the appropriate schema name. Can be done via console as well: