Grant access to schema for role

[stephanpelikan]

I'm using cdk-rds-sql to prepare a database for Keycloak in a serverless V2 RDS database-cluster:

    const keycloakPostgresqlDatabaseName = 'keycloak';
    const keycloakPostgresqlDatabase = new Database(props.rdsStack, 'KeycloakPostgresqlDatabase', {
      provider: props.rdsStack.postgreSqlProvider,
      databaseName: keycloakPostgresqlDatabaseName,
    });
    const keycloakPostgresqlRole = new Role(props.rdsStack, 'KeycloakPostgresqlRole', {
      provider: props.rdsStack.postgreSqlProvider,
      roleName: keycloakPostgresqlDatabaseName,
      database: keycloakPostgresqlDatabase,
    });

On starting Keycloak I get the error

ERROR: permission denied for schema public

So I created a schema public like this:

    new Schema(props.rdsStack, `KeycloakPostgresqlSchemaPublic`, {
      provider: props.rdsStack.postgreSqlProvider,
      schemaName: 'public', // required by Keycloak
      database: keycloakPostgresqlDatabase,
    });

But the error still persisted. I realized that the role needs permissions granted to setup tables within public schema.

After doing this, Keycloak could setup the database successfully:

GRANT USAGE ON SCHEMA public TO keycloak;
GRANT CREATE ON SCHEMA public TO keycloak;
GRANT ALL ON ALL TABLES IN SCHEMA public TO keycloak;

It would be great if cdk-rds-sql Schema would take a parameter role to pass the role granted for the schema.