Google Cloud Platform supports automatic IAM authentication for databases, but .NET is not officially supported.
Thankfully, Npgsql, a ADO.NET Data Provider for PostgreSQL, has enough features to make it easy!
Create and configure a service account
First, create a service account.
Then, assign it the
roles/cloudsql.instanceUser on your Cloud SQL instance. This can be also be done at the project level, but it’s generally better to keep your scopes tighter.
Lastly, add the service account as an IAM user to your database instance
Note: This would also work with a user account, but we’ll be using service accounts to keep things simple
Once all of this is done, your service account is ready to go
Write the code
We’ll be using a
NpgsqlDataSourceBuilder, with the
UsePeriodicPasswordProvider property in order to refresh our GCP access token.
You will need to install the following Nuget packages in your project:
We first start by readying our
// You could load this from a key file, or anywhere else, but this should cover most cases.
var credentials = await GoogleCredential.GetApplicationDefaultAsync();
// We want to limit the scope of the token, so we create scoped credentials
var scopedCredentials = credentials.CreateScoped("https://www.googleapis.com/auth/sqlservice.login");
Next, we need to setup a
Conveniently enough, Google auth will take care of refreshing the token on demand. If the token is not expired,
GetAccessTokenForRequestAsync will return a cached version. This means that we can keep
failureRefreshInterval pretty short (the latter should never happen). In this case, we use 1 minute and 0 minutes respectively.
var dataSourceBuilder = new NpgsqlDataSourceBuilder();
(settings, cancellationToken) => new ValueTask<string>(scopedCredentials.UnderlyingCredential.GetAccessTokenForRequestAsync(cancellationToken: cancellationToken)),
dataSourceBuilder.ConnectionStringBuilder.Database = "<your database name>";
dataSourceBuilder.ConnectionStringBuilder.Username = "<Your iam user. ex: email@example.com>";
dataSourceBuilder.ConnectionStringBuilder.Host = "<The IP address of your database>";
var dataSource = dataSourceBuilder.Build();
All that’s left is to create the connection and query the database.
using var connection = dataSource.CreateConnection();
using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT 1";
var result = cmd.ExecuteScalarAsync();
And that’s it, you now have secure, password-less access to your PostgreSQL server.
What about Entity Framework?
Good question! It turns out that Npgsql.EntityFrameworkCore.PostgreSQL supports passing in a
DataSource out of the box.
In an ASP.NET setting, it would look like this:
Want to register your DataSource for dependency injection? Sure!
builder.Services.AddDbContext<Context>((serviceProvider, options) =>
var dataSource = serviceProvider.GetRequiredService<NpgsqlDataSource>();
And that’s all there is to it.