Roles, Identities, Applications with Infra as Code (terraform, ansible, etc.)

TL;DR I’m looking for a way to run SQL commands for creating roles/groups/users/etc as part of a repeatable infra-as-code build process, and am hitting some errors when re-running scripts.

We’ve just setup an SDB 7.8 cluster in Google Cloud and are working through the security / identity process now, and am admittedly a Singlestore noob. I’ve deployed the cloud VMs and network via Terraform and then configured cluster hosts using Ansible playbooks (OS configs, SDB installation, cluster/aggregator/leaf setup). So far so good, but I’m now trying to figure out an idempotent way to apply configuration SQL commands like CREATE ROLE, GRANT, and CREATE GROUP so we can manage these configs as deployment scripts as well.

I haven’t come across either an sdb-admin command or an SQL parameter like IF NOT EXISTS that would make these commands idempotent or otherwise re-runnable. Has anyone found a good way to go about this?

More detail

I’m modeling our initial roles off of this guide: Role Creation

The docs recommend managing roles in a “version-controlled file and loaded into SingleStore.” This would fit really nicely with the Ansible/IAC idempotency model except that SDB will throw errors if roles/groups/etc already exist. So suppose I have a roles.sql file that looks like:

CREATE ROLE 'security_role';
CREATE ROLE 'dba_role';

Suppose I want to add another line CREATE ROLE 'application_schema_role';. Re-running the script throws errors like ERROR 1022 (23000): Role backup_operator_role already exists. I would have to comment out whichever commands have already been executed in version control (complicates the build pipeline) or force the script to ignore errors with -f (could have unintended side effects).

Any pointers are much appreciated!!

Hi @sdj0,

Welcome to the SingleStore forum!! Thanks for making your first post!

While we don’t have IF NOT EXISTS as it pertains to creating roles, there are a few options to get you to a similar end result.

You can create a script, or anonymous code block, in SingleStore to query INFORMATION_SCHEMA to see if the role exists before executing the CREATE ROLE statement.

Alternatively, you could create a stored procedure with the role name as a parameter that performs the same set of steps as described above and call the stored procedure for each desired role in your script.

Let me know how it goes!