Feature request: Resources for creating Schema and SQL User permissions

[TeodorStoyanov]

Overview

I have the following limitation with the Terraform provider for CockroachDB. We are trying to automate the creation of Database, Schema, Users and User's permissions like GRANTS and DEFAULT PRIVILEGES. Unfortunately, the Provider currently supports only the creation of Database and SQL Users.

Issue

I have tried to create the resources with the PostgreSQL Provider but there are engine compatibility issues with this provider and CockroachDB. I am receiving the following errors when trying to create the schema and the permissions:

│ Error: Error reading schema: pq: incompatible COALESCE expressions: type "aclitem" does not exist
│ 
│   with module.apps["test-app-1-migration"].postgresql_schema.cockroachdb_schema[0],
│   on .terraform/modules/apps/cockroachdb.tf line 5, in resource "postgresql_schema" "cockroachdb_schema":
│    5: resource "postgresql_schema" "cockroachdb_schema" {

│ Error: could not get advisory lock for role test_app_1_migration: pq: unknown function: pg_advisory_xact_lock(): function undefined
│ 
│   with module.apps["test-app-1-migration"].postgresql_grant.cockroachdb_schema_permissions[0],
│   on .terraform/modules/apps/cockroachdb.tf line 42, in resource "postgresql_grant" "cockroachdb_schema_permissions":
│   42: resource "postgresql_grant" "cockroachdb_schema_permissions" {

Request

It would be nice if the CockroachDB provider is able to create these resources

• postgresql_schema
• postgresql_grant
• postgresql_default_privileges
  like the PostgreSQL Provider or make the CockroachDB compatible with Vanilla Postgresql.

[marksoper]

@TeodorStoyanov - thanks for writing this up. I apologize that we didn't get back to you sooner. Role grant is now supported. Do you still have the need for schema and priviliges?

[sramshaw-yolabs]

@marksoper The new resources for cockroach_user_role_grant and cockroach_user_role_grants appear to only relate to Roles within the Cloud UI.

I believe this feature request (and something many of us are anxiously awaiting) is the ability to create non-admin SQL users and Manage SQL role grants specifically, totally separate and outside of the UI users and UI roles.