Making more parameters available to SQL templating in database secrets engine

Vault
1

Gidday,

Bit of a long post, targeted at anyone who’s a contributor with knowledge around the database secret engine + MSSQL plugin.

I’ve been thinking through whether we can use the database secret engine for both static and dynamic credential generation in Microsoft SQL Server specifically. I reckon it would be great for our use case if it weren’t for one unfortunate limitation.

I can see from the docs that the server login and the database user are created this way:

vault write database/roles/my-role \
    db_name=my-mssql-database \
    creation_statements="CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';\
        CREATE USER [{{name}}] FOR LOGIN [{{name}}];\
        GRANT SELECT ON SCHEMA::dbo TO [{{name}}];" \
    default_ttl="1h" \
    max_ttl="24h"


vault read database/creds/my-role # GET /database/creds/:name

What interests me here is the templating done by Vault (by the MSSQL plugin I guess) of the username and password. I’d like the ability to make other parameters available to this templating engine - in particular, a list of database names. This is because in our setup we don’t necessarily know the database names upfront all of the time, and would like to specify specific database names in creation_statements.

I noticed that when using the pki secret manager to issue a cert we make a POST to the /pki/issue/:name endpoint to generate and return a cert - my understanding is that since there is required data which must be provided in order to issue the cert, that makes a GET unsuitable even though it’s technically “retrieving” data as well as creating a record.

Following that line of thinking, I imagine adding an additional API endpoint POST /database/creds/:name, which takes a payload like:

{
  "databases": ["CustomerADatabase1", "CustomerADatabase2"
}

…and then makes a field “databases” available for templating in creation_statements:

DECLARE @UserCreationSql NVARCHAR(MAX) = '
CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';

WITH Databases(DatabaseName) AS (
-- Plugin would need to inject string array param as a comma-separated
-- string I guess, since SQL Server doesn't support arrays:
   SELECT value FROM STRING_SPLIT('{{databases}}', ',')
)
SELECT @UserCreationSql = @UserCreationSql + '

USE ' + QUOTENAME(DatabaseName) + ';
CREATE USER [{{name}}] FOR LOGIN [{{name}}];'
FROM Databases;

PRINT @UserCreationSql;
EXEC sp_executesql @UserCreationSql;

Which would eventually execute:

CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';

USE [CustomerADatabase1];
CREATE USER [{{name}}] FOR LOGIN [{{name}}];

USE [CustomerADatabase2];
CREATE USER [{{name}}] FOR LOGIN [{{name}}];

Digging into the code I find (in /builtin/logical/database/path_creds_create.go) this bit:

Callbacks: map[logical.Operation]framework.OperationFunc{
	logical.ReadOperation: b.pathCredsCreateRead(),
},

What I’m after is thoughts on whether it’s just a matter of adding a
logical.UpdateOperation: b.pathCredsCreateWithParams() in here (plus the corresponding function).

Guessing that the main challenge here will be making these params from the POST body available to the SQL templating, which seems like it’d live in the MSSQL plugin code, not in here.

Open to advice or thoughts here re: whether I’m on the right track or barking up the wrong tree - I’m not a Go developer but still happy to try and contribute this feature.

Thanks!