{"id":916,"date":"2024-06-20T11:13:30","date_gmt":"2024-06-20T11:13:30","guid":{"rendered":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/2024\/06\/20\/using-postgresql-with-net-and-entra-id\/"},"modified":"2024-06-20T11:13:30","modified_gmt":"2024-06-20T11:13:30","slug":"using-postgresql-with-net-and-entra-id","status":"publish","type":"post","link":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/2024\/06\/20\/using-postgresql-with-net-and-entra-id\/","title":{"rendered":"Using PostgreSQL with .NET and Entra ID"},"content":{"rendered":"

PostgreSQL<\/a> is a powerful, open-source relational database system that is widely used in the industry. In this blog post, we will explore how to use PostgreSQL with .NET and how to secure your app with Entra ID.<\/p>\n

Getting started with PostgreSQL<\/h2>\n

Let\u2019s start by creating a new application which is going to use PostgreSQL as the database. There are many ways we could setup PostgreSQL: we could install it, run it in a Docker container, but for this example, I\u2019m going to bootstrap it with .NET Aspire and the PostgreSQL hosting component<\/a>. While this does run a Docker container behind the scenes, it means we don\u2019t need to worry about setting up Docker or managing the secrets to connect to the database.<\/p>\n

For this, we\u2019ll use the .NET Aspire starter application:<\/p>\n

dotnet new aspire-starter –name PostgreSQLDemo<\/p>\n

This will create a new .NET Aspire application with the AppHost, Service Defaults, an API backend and a Blazor web frontend.<\/p>\n

Next, we\u2019ll add the PostgreSQL hosting component to the AppHost project:<\/p>\n

cd PostgreSQLDemo.AppHost
\ndotnet add package Aspire.Hosting.PostgreSQL<\/p>\n

The final piece for our AppHost is to create the PostgreSQL resource and then add it as a reference to the API project. Open the Program.cs file in the AppHost project and update it to match the following:<\/p>\n

var builder = DistributedApplication.CreateBuilder(args);<\/p>\n

\/\/ Create the PostgreSQL resource
\nvar postgres= builder.AddPostgres(“postgres”);
\nvar db = postgres.AddDatabase(“db”);<\/p>\n

\/\/ Update the API Service project to have the PostgreSQL database as a reference
\nvar apiService = builder.AddProject<Projects.PostgreSQLDemo_ApiService>(“apiservice”)
\n .WithReference(db);<\/p>\n

builder.AddProject<Projects.PostgreSQLDemo_Web>(“webfrontend”)
\n .WithExternalHttpEndpoints()
\n .WithReference(apiService);<\/p>\n

builder.Build().Run();<\/p>\n

There are two changes that we\u2019ve made here. First, we\u2019ve added the PostgreSQL resource to the AppHost project. This will create a new PostgreSQL database and user for the application, running in a container, when we launch our project. Second, we\u2019ve added the database as a reference to the API project. This will allow the API project to connect to the database.<\/p>\n

Adding PostgreSQL to the API<\/h2>\n

Now that we are starting a PostgreSQL resource in our AppHost, we can add the necessary code to the API project to interact with the database. We\u2019ll start by adding the Npgsql<\/a> package to the API project, but we\u2019ll use the .NET Aspire package, which ensures that we get all the logging and telemetry that we need:<\/p>\n

cd ..\/PostgreSQLDemo.ApiService
\ndotnet add package Aspire.Npgsql<\/p>\n

Note: If you are using EF Core, you can use the Aspire.Npgsql.EntityFrameworkCore.PostgreSQL package instead.<\/p>\n

Next, we\u2019ll add Npgsql to our service collection, so we can resolve it with Dependency Injection. Open the Program.cs file in the API project and update it to match the following:<\/p>\n

builder.AddNpgsqlDataSource(“db”);<\/p>\n

Make sure that the value you provide as the connectionName is the same as the name you used when you added the database reference in the AppHost project, this way the right connection string will be resolved.<\/p>\n

With all that in place, you can now use Npgsql in your API project to interact with the PostgreSQL database.<\/p>\n

Securing the database in Azure with Entra ID<\/h2>\n

So far, we\u2019ve focused on the local development experience. For production, we\u2019ll want to utilize Azure PostgreSQL Flexible Server for a managed instance. To enhance application security, we will employ Entra ID, creating a Managed Identity for database authentication instead of using a SQL username and password.<\/p>\n

Note: Setting up an Azure PostgreSQL Flexible Server and configuring it with Entra ID is beyond the scope of this article, but do check out the docs<\/a> for information on that.<\/p>\n

Using Managed Identity differs from traditional SQL username and password authentication because it does not require a persistent password. Instead, you need to request an access token that acts in place of a persistent password, and this access token is only short lived, which means we\u2019re going to need to request a new one from time to time.<\/p>\n

Getting an access token<\/h3>\n

To get an access token, we\u2019ll use the Azure.Identity package, which is part of the Azure SDK. We\u2019ll add this package to the API project:<\/p>\n

cd ..\/PostgreSQLDemo.ApiService
\ndotnet add package Azure.Identity<\/p>\n

With the Azure.Identity package, we can use the DefaultAzureCredential class to load the Managed Identity from the environment, and then use it to request an access token. Here\u2019s an example of how you would do that:<\/p>\n

var credentials = new DefaultAzureCredential();
\nvar token = await credentials.GetTokenAsync(new TokenRequestContext([“https:\/\/ossrdbms-aad.database.windows.net\/.default”]), CancellationToken.None);
\nConsole.WriteLine(token.Token);<\/p>\n

That\u2019s great to get a token one-time, but we\u2019ll need to refresh it, and to do that we can leverage a feature of Npgsql, the PeriodicPasswordProvider.<\/p>\n

Using PeriodicPasswordProvider<\/h3>\n

The PeriodicPasswordProvider is a feature of Npgsql that allows you to provide a callback that will be called whenever Npgsql needs a password. This is perfect for our use case, as we can use it to request a new access token whenever Npgsql needs it. It will also cache that token for a period of time, so we don\u2019t need to request a new one every time.<\/p>\n

To use the PeriodicPasswordProvider, we\u2019ll adjust the registration of Npgsql in the service collection to use it. Open the Program.cs file in the API project and update it to match the following:<\/p>\n

builder.AddNpgsqlDataSource(“db”, configureDataSourceBuilder: (dataSourceBuilder) =>
\n{
\n if (string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
\n {
\n dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
\n {
\n var credentials = new DefaultAzureCredential();
\n var token = await credentials.GetTokenAsync(new TokenRequestContext([“https:\/\/ossrdbms-aad.database.windows.net\/.default”]), ct);
\n return token.Token;
\n }, TimeSpan.FromHours(24), TimeSpan.FromSeconds(10));
\n }
\n});<\/p>\n

Here, we\u2019re providing a configureDataSourceBuilder callback to the AddNpgsqlDataSource method, which allows us to configure the DataSourceBuilder that Npgsql will use. We\u2019re checking if the password is empty, which means that Npgsql is expecting a password. If so, we\u2019re setting up the PeriodicPasswordProvider to cache the password for 24 hours and to retry every 10 seconds in case of failure. When running locally with .NET Aspire, the password will be set, so we do not need to use the PeriodicPasswordProvider because we have a known and consistent password.<\/p>\n

Next Steps<\/h2>\n

In this blog post, we\u2019ve looked at how to get started with PostgreSQL in .NET, how we can easily setup a local development experience with .NET Aspire, and how, when deployed to Azure, we can secure our application with Entra ID using the PeriodicPasswordProvider in Npgsql to request access tokens for Managed Identity.<\/p>\n

.NET Aspire PostgreSQL component<\/a>
\nConfigure Azure PostgreSQL Flexible Server with Entra<\/a>
\nNpgsql documentation<\/a><\/p>\n

The post Using PostgreSQL with .NET and Entra ID<\/a> appeared first on .NET Blog<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"

PostgreSQL is a powerful, open-source relational database system that is widely used in the industry. In this blog post, we […]<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[7],"tags":[],"class_list":["post-916","post","type-post","status-publish","format-standard","hentry","category-dotnet"],"_links":{"self":[{"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/posts\/916","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/comments?post=916"}],"version-history":[{"count":0,"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/posts\/916\/revisions"}],"wp:attachment":[{"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/media?parent=916"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/categories?post=916"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rssfeedtelegrambot.bnaya.co.il\/index.php\/wp-json\/wp\/v2\/tags?post=916"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}