Installing ASP.NET Core Identity in PostgreSQL

It’s “one of those days” project time!  I want to run an ASP.NET Core site on AWS, using ASP.NET Core Identity provider for user AuthN/AuthZ.  ASP.NET Core Identity has enough features to get started, can be extended, and is free.  The most common back-end for Identity is SQL Server, but I want to use a managed database like PostgreSQL instead, because I don’t want to be a DBA this time.  Fortunately, switching from SQL Server to PostgreSQL is a simple but not well known.

Although you can add Identity at any time during development, you really want to install and configure Identity preferably before you do anything else, since an EF Core migration is involved.  It’ll also set a better baseline in your git history.

(Note: this is an updated and expanded post, based on https://stackoverflow.com/questions/65970582/how-to-create-a-postgres-identity-database-for-use-with-asp-net-core-with-dotnet.  A few things have changed since that answer was posted, and additional explanation may be helpful also.)

Step 1: Create the database (use your database IDE)

A. Create a database user, with a password and login permissions.

B. Create your Identity database, and assign user as owner of the database.  For the DDL will need  lot of permissions, but can configure a least privileged user later.

Step 2: Create your site and install Identity

Create an ASP.NET Core site with Individual Identity selected.

image

If you have a site already without Identity, you can scaffold it, per https://learn.microsoft.com/en-us/aspnet/core/security/authentication/scaffold-identity.

If you create a site with Individual Identity, the DbContext and UserContext are created for you; if you scaffold in later, you’ll just have to add these yourself.

This is a good time to commit to git, in case you need to revert anything we do in the next steps.  Or so I’ve been told…

In appsettings.json, set the DefaultConnection to your PostgreSQL instance.  For dev, I’m running it in Docker so my string looks like this:

Host=localhost:5432;Username=wombat_user;Password=w0mb@t;Database=wombat_identity

Step 3: Configure the Nuget packages

First, delete the Sqlite package.  We don’t need this anymore.

image

Next, install the latest version of these packages

image

Finally, set the database provider in Program.cs.  Around line 10 you’ll see the AddDbContext line.  Change UseSqlServer (or UseSqlite) to UseNpgsql and save the file.

Step 4: Run the EF Migrations

If you need to, install and enable EF Migrations:

dotnet tool install –global dotnet-ef

First, clean up any cruft from the default installation:

dotnet ef migrations remove

Now, create a migration for PostgreSQL

dotnet ef migrations add {a good migration name}

Then, apply the migration.

dotnet ef database update

You should now see all the database objects for ASP.NET identity in your database.

If you threw an error, especially about casting TwoFactorAuth to a boolean, you probably need to re-remove the migrations and try again.  This worked for me.

This is an excellent time to commit to git.

The Identity pages live in a magic component and just work.  If you plan on extending Identity, or just want the pages in your solution, you can scaffold the pages by right-clicking on the project, choose Add >> Scaffolded Item >> Identity.  You’ll be prompted to choose the database context, and then all the pages will be added to your solution.  Details on this can be found at https://learn.microsoft.com/en-us/aspnet/core/security/authentication/scaffold-identity?view=aspnetcore-7.0&tabs=visual-studio#scaffold-identity-into-a-razor-project-with-authorization.

At this point, my test site is running fine.  It’s possible we’ll hit a snag in some of the more advanced capabilities, so there may be more blog posts to come.

Setting Up Neo4j on Azure VM

NB: I’m leaving this up for continuity purposes, but MS Open Tech no longer exists, so the VM Depot is no longer being updated (see https://msopentech.com/blog/2015/04/17/nextchapter/).  Newer versions of Neo4j will need to be installed the usual way using VMs.

It’s time for me to get back to experimenting with different datastores and data structures (and burn some Azure credits I’m basically wasting).  One datastore I’m interested in for my day job is the graph database Neo4j.  Relationships are fascinating to me, and a graph database stores relationships as data you can query.  There are DBaaS (managed, cloud-based Neo4j) providers such as graphstory, but for getting started and learning it’s probably cheaper to set up your own instance, and here I’ll show you one way to get up your own instance.  Fortunately, Neo Technology (the company behind Neo4j) created a VM image on Microsoft’s VM Depot, which we can use to spin up an Azure VM .

  1. Obviously, you need an Azure account.  If you don’t have one, you need to create one.  Despite the promise of “Free Account”, running VMs is not free on Azure, and the cheapest option for me was $13/month (prices at https://azure.microsoft.com/en-us/pricing/details/virtual-machines/#Linux).   It’s not terrible, especially if you remember to turn off your VM when you’re not using it.  The day job gets me MSDN credits, and anyone in the same boat can probably run a small VM without worries.
  2. It would also be a good idea to know some Linux, because that’s the OS.  If you don’t know the difference between SSH and LTS, you might want to pick up a used copy of Ubuntu Unleashed for 12.04 LTS for a buck or so.  It’s scary thick, but don’t panic, it’s organized well enough to be used as a reference.
  3. In order to publish a VM Depot image to your Azure account, you need a PublishSettings file (which is similar to a WebDeploy file, if you know what those are).  Just click https://manage.windowsazure.com/publishsettings/index?client=xplat and save the file locally.  You don’t need to do anything else, even though there are additional instructions on the page.
  4. Find the Neo4j Community on Ubuntu VM.  This VM is Neo4j 2.0.1 and the current Neo4j is 2.3, so it’s a little behind but good enough as a sandbox.  (This link might change if the Ubuntu OS or Neo4j version are updated, so if it’s broken let me know and I’ll update this post)
  5. On the VM Depot page, click the “Create Virtual Machine” button.  If you haven’t logged in you’ll be prompted to do so, and then you’ll need to provide your PublishSettings file.
  6. Next you’ll get to choose your DNS name, VM username and a few more options.  Pay attention to the ADVANCED settings, the default machine size will cost you about $65/month.  This would be a good time to scale it down a bit.  This is also a good time to change default ports for Neo4j or SSH if you want to.
  7. Now wait about 10 minutes for everything to get set up.  The publish process is a background process, and once it’s complete you’ll get an email if you close the window.

Once you get the confirmation, you’re now ready to start using Neo4j!

Transactional vs. ODS Talking Points

When considering implementing an operational data store, discussion always includes the differences between an ODS and a transactional database.  Transactional databases store the data for an application.  An ODS’s purpose is to consolidate from one or more transactional systems, to serve as a source of master data, or for reporting, or one source to a data warehouse.  While the purposes are pretty clear, how they differ at a design level is less clear.  Here are the talking points I’ve used in the past to describe the differences.

Transactional databases

  • are optimized for write performance and ensuring consistency of data
  • mainly inserts and updates, no table rebuilds
  • Low level of indexing, mainly primary keys and the lookups needed 
  • high use of foreign keys
  • use of history and archive tables for no longer current data
  • index and data fragmentation are a concern due to updates, and maintenance jobs need to be utilized
  • data are normalized
  • but, frequently updated data are often separated from less frequently updated data to reduce table fragmentation
  • data are raw

Operational data stores

  • are optimized for reads
  • mainly inserts and table rebuilds via ETL from transactional systems, few updates
  • high level of indexing to support querying
  • low use of foreign keys, since relations are maintained in the transactional databases
  • no history or archive tables–ODSs are for current data
  • low level of normalization, since updates are usually on the same schedule and in a batch process
  • data are sometimes calculated or rolled-up (rather than saving a birthdate, use a demographic age)
  • data may be bucketed

Exactly when to use an ODS and how the schema is designed is a discussion about balancing data duplication vs application architecture.

The update schedule of an ODS is determined partly by the needs of the ODS data consumers, and partly by what the transactional databases can tolerate.  Usually ODS updates are a batch job which runs once or several times a day.  For more frequent updates, commanding could be used.