Pulumi Azure AD User Setup

activeDirectoryBackedSqlAzureUser.ts

import * as pulumi from '@pulumi/pulumi';
import * as tedious from 'tedious';

import {SqlCommandArgs, SqlConnectionDetails, SqlUserResourceProvider, UserCommandProvider} from './sqlUserProvider';

async function runSqlInSqlAzure(a: pulumi.Unwrap<SqlConnectionDetails>, command: string): Promise<never> {
    return new Promise((resolve, reject) => {
        // const config = new pulumi.Config('activeDirectoryAdmin');
        // const usernameSecret = config.require('username');
        // const passwordSecret = config.requireSecret('password');

        const usernameSecret = '...';
        const passwordSecret = '...';

        pulumi.all([usernameSecret, passwordSecret]).apply(([username, password]: [string, string]) => {
            const sqlConnection = new tedious.Connection({
                server: a.server,

                options: {
                    database: a.database,

                    trustServerCertificate: false,
                },

                authentication: {
                    type: 'azure-active-directory-password',

                    options: {
                        userName: username,
                        password: password,
                    },
                },
            });

            sqlConnection.on('debug', (t) => pulumi.log.debug(t));
            sqlConnection.on('infoMessage', (i) => pulumi.log.debug(i.message));
            sqlConnection.on('errorMessage', (err) => pulumi.log.error(err.message));
            sqlConnection.on('error', reject);

            sqlConnection.on('connect', (err) => {
                if (err) {
                    return reject(err);
                }

                pulumi.log.debug(`Connected to ${a.database}. Executing SQL command ${command}`);

                const request = new tedious.Request(command, function (err) {
                    if (err) {
                        return reject(err);
                    }

                    sqlConnection.close();

                    resolve();
                });

                sqlConnection.execSql(request);
            });

            // Types have not caught up
            (sqlConnection as any).connect();
        });
    });
}

const sqlAzureUserCommandProvider = {
    execute(args: pulumi.Unwrap<SqlCommandArgs<string>>, command: string): Promise<never> {
        return runSqlInSqlAzure(args, command);
    },

    getCreateUser(name: string): string {
        return `CREATE USER [${name}] FROM EXTERNAL PROVIDER;`;
    },

    getDropUser(name: string): string {
        return `DROP USER [${name}];`;
    },

    getAddRole(name: string, role: string): string {
        return `ALTER ROLE [${role}] ADD MEMBER [${name}];`;
    },

    getDropRole(name: string, role: string): string {
        return `ALTER ROLE [${role}] DROP MEMBER [${name}];`;

    },
} as UserCommandProvider<string>;

/**
 * Represents a USER within a SQL Azure database that is backed by an Azure Active Directory
 * user or group.
 */
export class ActiveDirectoryBackedSqlAzureUser extends pulumi.dynamic.Resource {
    constructor(name: string, args: SqlCommandArgs<string>, opts?: pulumi.ComponentResourceOptions) {
        super(new SqlUserResourceProvider(sqlAzureUserCommandProvider), name, args, opts);
    }
}

sqlUserProvider.ts

import * as pulumi from '@pulumi/pulumi';

export interface UserCommandProvider<TRole> {
    execute(args: pulumi.Unwrap<SqlConnectionDetails>, command: string): Promise<any>;

    getCreateUser(name: string): string;

    getDropUser(name: string): string;

    getAddRole(name: string, role: TRole): string;

    getDropRole(name: string, role: TRole): string;
}

export class SqlUserResourceProvider<TRole> implements pulumi.dynamic.ResourceProvider {
    constructor(private readonly commandProvider: UserCommandProvider<TRole>) {
    }

    public async diff(id: pulumi.ID, olds: SqlCommandArgs<TRole>, news: SqlCommandArgs<TRole>) {
        const serverDiff = olds.server != news.server;
        const databaseDiff = olds.database != news.database;
        const userDiff = olds.adUserOrGroup != news.adUserOrGroup;
        const rolesDiff = JSON.stringify(olds.roles) !== JSON.stringify(news.roles);

        return {
            changes: serverDiff || databaseDiff || userDiff || rolesDiff,

            replaces: [
                serverDiff ? 'server' : null,
                databaseDiff ? 'database' : null,
                userDiff ? 'user' : null,
            ].filter(i => i != null) as string[],

            deleteBeforeReplace: false,
        };
    }

    public async create(inputs: pulumi.Unwrap<SqlCommandArgs<TRole>>) {
        let command = this.commandProvider.getCreateUser(inputs.adUserOrGroup);

        if (inputs.roles != null) {
            command += inputs.roles.map(r => this.commandProvider.getAddRole(inputs.adUserOrGroup, r as TRole)).join(' ');
        }

        await this.commandProvider.execute(inputs, command);

        return {
            id: inputs.server + inputs.database + inputs.adUserOrGroup,
            outs: inputs,
        };
    }

    public async update(id: string, olds: pulumi.Unwrap<SqlCommandArgs<TRole>>, news: pulumi.Unwrap<SqlCommandArgs<TRole>>) {
        const oldRoles = olds.roles || [];
        const newRoles = news.roles || [];

        // The only update we support is roles changing. Otherwise it's a delete then create
        const removes = oldRoles.filter(r => !newRoles.includes(r));
        const adds = newRoles.filter(r => !oldRoles.includes(r));

        const command = `
            ${removes.map(r => this.commandProvider.getDropRole(news.adUserOrGroup, r as TRole)).join(' ')}
            ${adds.map(r => this.commandProvider.getAddRole(news.adUserOrGroup, r as TRole)).join(' ')}`;

        await this.commandProvider.execute(news, command);

        return {
            outs: news,
        };
    }

    public async delete(id: string, props: pulumi.Unwrap<SqlCommandArgs<TRole>>) {
        // Although not necessary for SQL Server, Postgres requires the user to have all grants
        // removed first
        const removeRoles = (props.roles || []).map(r => this.commandProvider.getDropRole(props.adUserOrGroup, r as TRole)).join(' ')
        const dropUser = this.commandProvider.getDropUser(props.adUserOrGroup);

        await this.commandProvider.execute(props, `${removeRoles} ${dropUser}`);
    }
}

export interface SqlConnectionDetails {
    /**
     * The FQDN of the SQL Azure instance to create a USER for.
     */
    server: pulumi.Input<string>;

    /**
     * The database to create the USER in.
     */
    database: pulumi.Input<string>;
}

export interface SqlCommandArgs<T> extends SqlConnectionDetails {
    /**
     * The Azure Active Directory Group name or username (as [user]@[domain] form).
     */
    adUserOrGroup: pulumi.Input<string>;

    /**
     * An optional list of DB roles that the user should be assigned to.
     */
    roles?: pulumi.Input<T[]>;
}