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: SingleStoreDB Cloud · SingleStore Documentation
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!!