Recently I have been looking at the Vault project as a means to manage secrets for applications and end-users. One of the use cases that immediately drew my attention was the ability to create dynamic role-based MySQL credentials.

Why Dynamic MySQL Credentials?

There are a few reasons why dynamic credentials would be beneficial, all of which can be handled by Vault, including:

The database environment is too large to manage individual users.

A need to authenticate on an external service, such as LDAP or GitHub organization.

Provide credentials to external resources, such as auditors or outside consultants that automatically expire.

Compliance requirements for strict audit logs for database access.

A High-Level Overview of Vault

Vault is a fairly new project by HashiCorp, the folks behind projects such as Vagrant and Consul. The goal is to decouple the handling of secrets from applications to enforce access control, encryption standards, and create an audit trail.

There are several components to Vault:

Authentication such as LDAP, GitHub or custom app-id.

Authorization using path-based ACL policies.

Encrypted storage backend using one of several options such as Consul, etcd, Zookeeper, S3, or MySQL.

Secret backends that define how secrets are stored or generated. Options include MySQL and AWS IAM credentials, among others.

Audit logging of token generation and secrets access.

To begin working with a Vault deployment, Vault must be initialized and unsealed. Unsealing Vault is a very important aspect of Vault’s security model, but is beyond the scope of this post.

After Vault is unsealed, users can begin interacting with Vault either by a CLI tool or HTTP API. Users, whether they are human or applications, are authenticated based on tokens. These tokens can be revoked at any time, be given a time-to-live (TTL) or a specific number of uses.

Authentication methods are associated with access control list (ACL) policies to define which secrets the token will have access to.

Certain secret backends can generate actual credentials upon request. This includes the MySQL secret backend, which creates users with specific grants based on their role and passes only the generated user credentials to the requesting token.

Creating Dynamic Credentials with Vault

Let’s generate a read-only MySQL credential using Vault. To follow along with this exercise, you will need to install Docker Toolbox and clone my vault repository. The docker-compose file will look like this:
View the code on Gist.

Next, we will bring up the vault and mysql containers.
View the code on Gist.

The initiate-vault.sh script will initiate and unseal the vault, then set the environment variable ‘VAULT_TOKEN’ that we will use later. It also creates a ‘vault’ alias that allows us to interact with the Vault CLI within the docker container. The output will look like this:
View the code on Gist.

Now that Vault is unsealed, we can create the MySQL backend. Vault must be provided with the credentials of a MySQL user with GRANT OPTION privilege. For the purpose of our example, we will use the root user.
View the code on Gist.

With the MySQL backend configured, we can finally create our dynamic credentials. The generated credentials will be valid for 10 minutes, then expire.
View the code on Gist.

Of course, implementing Vault does add yet another dependency that must be highly available. Care must be taken to deploy Vault in a fault-tolerant manner.

One concern I have with the current workflow of secret handling is that each GET request to /mysql/readonly creates a new user. So a busy environment could thrash the database server with CREATE USER USER commands, which will be cleaned up later with DROP USER commands.

The way I expected it to work is that if the same Vault token requested the same credential endpoint, Vault would return an unexpired credential instead of generating an entirely new user.

To work around this, the user must keep track of the ‘lease-id’ returned from the initial read request and renew the lease before it expires. The user can do this up until the lease_max period.

As with any database administration strategy, management of Redshift requires setting and revoking permissions. When first setting up permissions, it is ideal to establish groups as the basic unit of user access. This keeps us from having to manage hundreds of permissions as users enter and leave organizations. If you haven’t done this early on and are managing permissions on the user level, you can leverage some of the queries below to make the migration to group based access easy.

Another advantage we see in managing by groups is for some data warehouses we want to exclude users from running reports during ETL runs in order to prevent contention or reporting on incomplete data. All we have to do is run this query at the start of the jobs:

revoke usage on schema myreportschema from group report_group;

When the jobs are finished, we then grant usage again:

grant usage on schema myreportschema to group report_group;

It is easy to see users and group assignments via (note that a user can belong to more than one group):

select usesysid, usename, usesuper, nvl(groname,'default')
from pg_user u
left join pg_group g on ','||array_to_string(grolist,',')||',' like '%,'||cast(usesysid as varchar(10))||',%'
order by 3,2;

Grants in Redshift are ultimately at the object level. That is to say while we can grant access to all existing objects within a schemas, those grants are stored at an object level. That is why issuing this command works for all existing tables, but tables added after this command that have been run do not automatically have the select privilege granted:

grant select on all tables in schema myreportschema to group report_group;

While this is good for security granularity, it can be administratively painful. Let us examine some strategies and tools for addressing this issue.

Redshift has the useful view, information_schema.table_privileges, that lists tables and their permissions for both users and groups. Note that this also includes views despite the name. AWS also provides some useful views in their Redshift utilities package in Github, most notably v_get_obj_priv_by_user which essentially flattens out information_schema.table_privileges and makes it much easier to read:

schemaname

objectname

usename

sel

ins

upd

del

ref

myreportschema

myreporttable

biuser

TRUE

FALSE

FALSE

FALSE

FALSE

However, note that this view does not report on groups and the HAS_TABLE_PRIVILEGE function that the view uses has no equivalent for groups, so to examine group privileges we can parse the ACL:

One of the biggest challenges is to fill in missing grants. We can do this by modifying the above query. Here’s an example where we create the grant statements for all missing select grants for the report_group: