After my post regarding the security to connecting your application in the cloud, Use Aurora PostgreSql With Aws Iam Authentication using DotNet6 Core MVC , I got questioned how to connect to PostgreSQL with hibernate ORM.
For entity framework users you can find a lot of example even within AWS website. This Link is a good start for you.
With hibernate ORM, i will say the example are rare, i guess due to the nature of this Open source evolution. But there are still a lot of companies out there using it and I figured out the necessity to provide at least some sample code, in case you run into difficulties.
My goal is not to show you how to implement using fluent Nhibernate because basic Fluent Nhibernate implementation can be found online.
IAM database authentication for MariaDB, MySQL, and PostgreSQL. Let me remind you the goal in case you did not read my first article; The idea , you don’t want to use a password or add password to your connectionString, when you connect to a DB instance.
<property name="connection.connection_string">Host=aws-rds-endpoint; Database=300;Username=postgres;Password=postgres;</property>
Instead, you use an authentication token. This Security method is better and recommended in cloud scenario, and even better in multitenant SAAS/PAAS case. When designing a solution in the cloud, you need to follow the best practices to secure your environment, and database connection is one of them.
<property name="connection.connection_string">Host=dz-2022-db002.cluster-donotTry.us-east-2.rds.amazonaws.com;Database=demodb;Username=dbuser;Port=5432;" </property>
In .net core you might want to upgrade your version to at least .net5.0 or 6 in order to successfully use the related libraries.
One of the major library being Npgsql and you better use the version 6 with work with .net5 and netstandard2.0 . Add the following reference to your project (.csproj)
<PackageReference Include="Npgsql" Version="6.0.0" />The below code can be found in my Git with the related file for the fluent nhibernate config
public static void CheckPostgreSQLDatabase(ILogger Logger)
{
try
{
var configurationFileName =
Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Settings.Default.ConfigurationFile);
NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration().Configure(configurationFileName);
var connectionString = cfg.GetProperty(NHibernate.Cfg.Environment.ConnectionString);
var dialect = cfg.GetProperty(NHibernate.Cfg.Environment.Dialect);
GlobalConstant.DB_PostgreSQL = dialect.IndexOf("PostgreSQL") >= 0;
if (GlobalConstant.DB_PostgreSQL)
{
var ctalogConnectionString = new NpgsqlConnectionStringBuilder(connectionString);
var configuration = Fluently.Configure()
.Database(PostgreSQLConfiguration.Standard
.ConnectionString(c => c
.Host(ctalogConnectionString.Host)
.Port(ctalogConnectionString.Port)
.Database(ctalogConnectionString.Database)
.Username(ctalogConnectionString.Username)
.Password(GenerateAwsIamAuthToken(ctalogConnectionString.Host, ctalogConnectionString.Port, ctalogConnectionString.Database, ctalogConnectionString.Username)))
)
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<VersionInfo>())
.ExposeConfiguration(config => new SchemaExport(config).Create(false, true))
.BuildSessionFactory();
using (var session = configuration.OpenSession())
{
// Query all objects
Logger.Info("PostgreSQL Database is ready....");
var completeList = session.CreateCriteria<Object>().List();
Console.ReadLine();
}
}
}
catch (Exception)
{
throw;
}
}Please let me know if this code was useful to you or if you have better improvement .
Comeback2C
