How to create database user and assign role to it with terraform
Solution 1
I've started working on a Terraform provider that will do this over at https://github.com/jayway/terraform-provider-mssql. It's still very early work, but I've managed to make it provision logins and users for those logins in a local SQL Server instance (that was already running, so I haven't tested it at all in conjunction with e.g. the AzureRM provider). Testing and contributions are very welcome (but don't use it for production scenarios just yet)!
Solution 2
Creating users and roles is a function of SQL server, not of the Azure platform. As such Terraforms ARM provider does not deal with this, and as far as I can see Terraform does not have an MSSQL provider to this work (it does have MYSQL and Postgres). You would need to do this work using SQL scripts.
Solution 3
Here's how I did this using postgres/psql.
First, generate the md5 password hash by appending the username to the password:
$ echo -n mypasswordbob|md5sum
fd9c9714184c4ae189ca83f1c21aeeb8
Prepend the string 'md5' to the resulting hash to get the encrypted password form that Postgres will accept:
md5fd9c9714184c4ae189ca83f1c21aeeb8
Then add in a standalone template file with the database commands you want to run:
data "template_file" "db_roles" {
vars {
username_bob = "bob"
password_bob = "md5fd9c9714184c4ae189ca83f1c21aeeb8"
}
template = <<EOF
create user $${username_bob};
alter user $${username_bob} WITH ENCRYPTED PASSWORD '$${password_bob}';
EOF
}
Finally, add a remote-exec provisioner as part of your server creation:
provisioner "remote-exec" {
inline = [
"set -x",
"cat > db_roles.sql <<EOL\n${data.template_file.db_roles.rendered}\nEOL",
"psql -U myuser < db_roles.sql",
]
}
sumit salunke
Updated on September 18, 2022Comments
-
sumit salunke over 1 year
I have created sql server and database with terraform now I have to create database user and assign role to it.
How I can do it. Please suggest me.