Separate connection strings for creating/altering/dropping tables and querying the database

[bppn]

Is your proposal related to a problem?

At the moment only one connection string can be provided for connecting to a database.

In order to get a permission level to allow schema changes, a connection string must be used with a user that is allowed to run DDL statements.

The normal queries (DML) are running at the same privilege level, what is not best practice.

Describe the solution you'd like

A separation between DDL and DML connection strings should be possible, making the solution in line with best practices and as a result more secure.

[kevinmichaelchen]

I would also extend this feature request for the scenario where you're primary database is separate from your metadata database.

e.g., when you specify both environment variables

HASURA_GRAPHQL_METADATA_DATABASE_URL=postgres://<user>:<password>@<host>:<port>/<metadata-db-name>
HASURA_GRAPHQL_DATABASE_URL=postgres://<user>:<password>@<host>:<port>/<db-name>

• DDL is required on the primary database for things like hasura migrate apply and for creating the SQL trigger for an event trigger.
• DDL is required on the metadata database for things like hasura metadata apply to create the metadata in the hdb_catalog schema.

Ideally, the DB connections that get used at runtime to serve mutations and queries would be limited to DML only.

A potential solution

Offer two additional environment variables, "deploy URLs", where you can specify DDL-enabled connections.

HASURA_GRAPHQL_METADATA_DATABASE_URL=
HASURA_GRAPHQL_DATABASE_URL=

HASURA_GRAPHQL_METADATA_DATABASE_DEPLOY_URL=
HASURA_GRAPHQL_DATABASE_DEPLOY_URL=

[deinspanjer]

I've found a somewhat reasonable workaround for this need. I'm interested in what Hasura people think.

You can run the graphql-engine:*.cli-migrations-v3 docker image and give it connection strings that reference a user with DDL permissions.

Normally, when the cli-migrations part is finished, it shuts down the temporary server and starts up a standard graphql-engine. I discovered when looking at https://github.com/hasura/graphql-engine/blob/master/packaging/cli-migrations/v3/docker-entrypoint.sh#L106 that if you pass an invalid argument when running the image, it will perform the migration and metadata application, but then shut down with an error instead of running the standard engine.

An example of running like this would look like:

HASURA_GRAPHQL_DATABASE_URL=postgres://superuser:@<hostname>:5432/app_database
HASURA_GRAPHQL_METADATA_DATABASE_URL=postgres://superuser:@<hostname>:5432/postgres

And then execute the cli-migration image like this: docker run -it --rm --env-file ${PWD}/hasura-migration.env -v ${PWD}/migrations:/hasura-migrations -v ${PWD}/metadata:/hasura-metadata hasura/graphql-engine:v2.17.1.cli-migrations-v3 then-shutdown

It would be nice if that docker-entrypoint.sh script had a check before line 106 that checked if a particular parameter was given to instruct it to shut down instead of running the standard engine. That is a pretty small change though, and I think this is a reasonable solution even without it.

I believe this even works in a Hasura Cloud scenario, where the cloud project could be given user credentials that only have DML grants, and the customer has to run the docker image locally connecting with the account that has DDL grants to perform migrations and metadata.