Installing and Configuring PostgreSQL 9.4 on Linux Mint/Ubuntu

Posted on April 19 2015 07:41 AM by jatten in Postgres, Database, Linux, CodeProject   ||   Comments (1)


Installing and configuring PostgreSQL on a Linux box is either simple, because you are a Linux and/or Postgres expert, or not so simple, because you are new(er) to Linux, Postgres, or both. Over the past year, I have resided firmly in the latter camp. I am a huge fan of the Postgres database platform, and I have been slowly but steadily trying to improve my Linux chops.

If you are an experienced Linux user, or a PostgreSQL DBA, this is not the post for you, although your feedback and constructive criticism are most welcome – if you see something amiss, please do let m eknow in the comments, or via email.

I should note here that, where practical, I do as much as I can on Linux from the terminal. While the desktop/GUI is handy for some tasks, I am doing my level best to become proficient with the terminal in Linux. I strongly recommend doing the same. Which is how we’re going to do things here.

The installation process for Postgres on Ubuntu or Linux Mint is, like many things in the Linux world, less than intuitive for new users. As much for my own sake as anyone else’s, I’m going to walk through the steps to getting Postgres installed and configured on a Linux box.

Why Postgres?

PostgreSQL is a fantastic database platform. Postgres is open source, cross-platform, free, and offers an amazing feature set which, in my mind, exceeds those of its principle peers in the relational database space.

Postgres offers all of the (mostly) standards-compliant SQL/relational database feature you would expect, plus a host of exciting and innovative features. Highlights include a JSON datatype (and also JSONB!), an array datatype, and the new HStore type, which essentially allows the specification of a column as containing a list of key/value pairs. We’ll take a tour of PostgreSQL in another post, but first, let’s get the thing installed and running.

Installation Steps for PostgreSQL on Linux Mint or Ubuntu

At the moment, my preferred Linux distro is Linux Mint 17 (“Quiana”) with the Cinnamon desktop. This is a long-term support release of the Linux Mint distro, very stable, and an excellent place to start. If you do not have a dedicated Linux machine, it is simple enough to spin up a VM using Virtual Box

As of this writing, the most recent version of PostgreSQL is version 9.4, which brought with it some very cool features such as full JSONB support. However, the 9.4 release is not available directly using the Advanced Packaging Tool (APT) or the Linux Mint/Ubuntu Software Manager.

Fortunately, the PostgreSQL Global Development Group (PGDB) maintain an APT repository of PostgreSQL packages for Debian and Ubuntu-derived Linux distros.

Before we can install Postgres, we need to add the package source for the distro we are using. In my case, I am using Linux Mint 17, which is derived from, and compatible with, the Ubuntu 14.04 (“Trusty Tahar”) release. We’ll see why this matters in a moment.

Add the Postgres Package Source for Your Linux Release

We need to create a sources file reflecting the proper Postgres source for our particular distro. In my case, as noted above, we need the source compatible with the ��Trusty” release of Ubuntu. So we can do this from the terminal to add the file (make sure you use sudo in all of the following steps):

Add the PGDB APT Source file From the Terminal:
~ $ sudo touch /etc/apt/sources.list.d/pgdg.list


Now that the file exists, open in your editor of choice (we’ll use gedit here):

Open the pgdg.list File in gedit (use sudo):
~ $ sudo gedit /etc/apt/sources.list.d/pgdg.list


then add the following line in gedit and save (where I used “trusty” below, use the name of your release for Ubuntu, or the corresponding Ubuntu release if you you are using Linux Mint):

Add the Postgres Package Repository and Specify Your Distro Release:
deb trusty-pgdg main


Save, and close gedit.

Alternately, we can achieve all of the above in one shot from the terminal like so (take note of the placement of single and double quotes here…):

Add the Package Source in one multi-line Terminal Command:
~ $ sudo sh -c \
'echo "deb trusty-pgdg main" > \


Add the Postgres Package Repository Key

Next, add the package repository key:

Add the Postgres Package Repository Key:
~ $ sudo apt-get install wget ca-certificates
~ $ wget --quiet -O - | sudo apt-key add -


Update, Upgrade, and Install Postgres

Then, we need to update our package sources:

Update Package Sources:
~ $ sudo apt-get update


Once that’s done, we need to upgrade packages to the latest versions:

Upgrade Packages:
~ $ sudo apt-get upgrade


Note, this can be a long process. Also, you may be prompted at several points to make some choices about configuration items. Specifically, you may informed that this that or the other configuration file has been changed, and asked if you want to keep your original version, or replace with the package maintainer’s version. Select “Y” to accept the package maintainer’s version in these cases.

When the upgrade process finishes, we are ready to install Postgres (we’ll also include pgadmin3):

Install Postgres:
~ $ sudo apt-get install postgresql-9.4 pgadmin3


This shouldn’t take long (especially compared to the installation times for certain other GUI – installer-based database platforms. I’m looking at you, SQL Server). Once the installation completes, we’re ready to configure our new database.

Configuring Postgres for Use

We’ve now installed both PostgreSQL and the database management utility Pg Admin 3. Next, we should understand a few things about how PostgreSQL works, out of the box.

The Postgres User

When PostgreSQL was installed, a system user account named postgres was created. with a matching user account in Postgres. By default, the postgres user account is not configured with a password, so it is not possible to log into the server using the postgres user account without first creating a password for it. This postgres account has an all-access pass on your postgres database server, permission-wise. The postgres user account is analogous to the sa account in SQL Server. For security reasons, it is recommended that a password not be created for the postgres account.

The Postgres Database

PostgresSql is installed with a default database named…wait for it… postgres. From the PostgreSQL documentation:

Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the "template1" and "postgres" databases . . .

. . . The postgres database is a default database meant for use by users, utilities and third party applications.

For the most part, we use the postgres database for admin purposes, and create new databases on the PostgreSQL server to suit our needs.

The psql Command Line Utility

PostgreSQL includes psql, a command line utility for managing your databases and server. While a GUI-based utility such as pgadmin3 is often easier to use in the day-to-day, the command line utilty psql is also handy. Psql offers complete control of your Postgres system from the terminal, including the ability to execute SQL queries.

Also, we need to use psql to perform our initial configuration, and to create an initial database super user.

Create a Super User Account

Since we will not be creating a password for the postgres user account, we need a super-user account in order to work with our database in the day-to-day.

To do this, we will gain access to the postgres account through your system root user, and then use that postgres account to create a new super-user account on your Postgres installation which can be regulated more effectively. As an example, from my own machine (comments denoted by ##):

Access the Postgres User Through Root:
## switch user to root:
xivsolutions@mint-vm ~ $ su -
## switch user to postgres:
mint-vm ~ # su - postgres
postgres@mint-vm ~ $ 


As we can see, we now have a prompt for the postgres user. We can now log in to the default postgres database and, using psql, create a super user account for ourselves:

To get into the psql command line utility, we type the following:

Enter the psql Command Line Utility:
postgres@mint-vm ~ $ psql


Now, from the psql prompt, enter the following. Note the name you specify for your super-user account should match the system user account you plan to use to manage your Postgres Installation (use your own user account name in place of youruseraccount here):

Create a New Super User from the psql Prompt:
postgres=# CREATE USER youruseraccount
postgres-# PASSWORD 'userAccountPassword';


Notice in the above we can enter multiple lines of SQL. The SQL is not executed until we enter a semi-colon followed by enter. Which means, the semi-colon matters!

Now, we can exit psql, exit the postgres user, and exit root like so:

Return to your normal user account:
postgres=# \q
postgres@mint-vm ~ $ exit
mint-vm ~ # exit
xivsolutions@mint-vm ~ $ 


With that, we should now be able to log in using psql and make sure everything is wired up correctly.

Log In Using Psql and Create a Test Database

Now, just to make sure everything is working correctly, let’s log in with psql using our new super user account and create a quick test database:

Log-In Using the New Super-User Account:
xivsolutions@mint-vm ~ $ psql postgres
psql (9.4.1)
Type "help" for help.


Note in the above, we specified the postgres default database when we logged in, since there aren’ tyet any other databases to connect to. We’ll use the default postgres as our admin platform, create a new database, and then connect to the new database to test things out.

So, let’s create a database to play with (once again, make sure to end the SQL statement with a semi-colon!):

Create a Test Database Using Psql:
postgres=# CREATE DATABASE test_db WITH OWNER xivsolutions;


Now that we have our own database to mess with, use the \connect command to switch psql to that:

Switch the Active Psql Connection to the new Test Database:
postgres=# \connect test_db;
You are now connected to database "test_db" as user "xivsolutions".


Now, let’s whip up a quick and dirty table, insert some data, and query:

Create Table, Insert Data, and Select Query Using Psql:
test_db=# CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT);
test_db=# INSERT INTO products (name) VALUES ('Brass Widgets');
test_db=# SELECT * FROM products;
 id |     name      
  1 | Brass Widgets
(1 row)


This is a quick example of what can be done from the psql command line. psql is a powerful tool, and is very much worth exploring. While GUI-based tools like PG Admin 3 and others certainly can be helpful, gaining a certain level of comfort working with Postgres from the terminal will save you time in many cases.

That said, let’s configure PG Admin 3, so we can have our cake and eat it too.

Configure PG Admin 3 for Local Connections

PG Admin 3 is a free, open source GUI database management tool for Postgres. While the GUI itself is not as polished as some, all the essentials are there. We already installed PG Admin 3 when we installed Postgres itself, so let’s take a look.

First off, open PG Admin 3from the terminal:

Open PG Admini 3:
xivsolutions@mint-17 ~ $ pgadmin3


You will probably see something like this:

The PG Admin 3 GUI After Opening:


Before we can do much of anything here, we need to add our new database server. Since we are working on our local machine, we’ll go ahead and add a local server, which points to localhost. Use File –> Add Server… to open the Server Registration Dialog, and you should see something like the following:

Register your Postgres Server with PG Admin 3:


Note the default values you see in the above may differ slightly on your machine. We’re going to provide a name by which our local server will be known, specify the host, and make sure that the User Name matches our new Super User name (which in most cases should match our system user name, although this is not a requirement).

As an example, on my machine, I set things up like so:

Enter PG Admin Server Registration Items for Local Postgres Database:


In the above note that I deselected the “Store Password” option. In general, storing of Postgres user passwords is not recommended, although you may consider your circumstances and proceed accordingly. For example, if you are setting up a local development installation on your laptop, you may choose to store the password anyway. Next, hit “OK” and voila – our existing Postgres database server is available in PG Admin 3:

Postgres Database Server Registered in PG Admin 3:


Now you can use PG Admin 3 to create new databases, tables, query, and all the other tasks you might expect. While the GUI experience is not as polished as some commercially available alternatives, you will find that PG Admin 3 is highly functional, and can handle most of your database administration needs quite well.

Postgres Configuration Options – Database Files and Directory Location

When we performed our installation and configuration above, we basically allowed Postgres to use its own sensible defaults for most things. Most of the time, if you are setting up Postgres on your own machine, for development or just to mess around, these are sufficient. However, understanding some of the basic configuration options, how and where these are set, can be most helpful.

When Apt installs Postgres, it creates a few directories by default:

/etc/postgresql/9.4/main – Primary PostgreSQL configuration files are located here. This directory is owned by root, so you will need to elevate your privileges to edit files in here. Of particular interest to us are two files:

  • pg_hba.conf – Configuration parameters for client authentication and access is specified in this file.
  • postgresql.conf – Configuration parameters for important file locations, logging, resource usage, and a host of other database configuration items are set here. For our purposes, most of the defaults will do for now. However, the file locations may be of interest, as we’ll see in a moment.

/var/lib/postgresql/9.4/main – Default location for the database cluster and associated files. Above, when we initialized our database, we were working with database files in this location.

Specify a Different Directory for the Database Cluster Files

For a variety of reasons we may wish to specify a different directory for our database files. We may want to keep them more easily accessible, or we might have set up our system with a separate partition for data files. In any case, if we want to use a location other than the default for our database cluster, we can.

The Postgres cluster directory must be owned by the postgres user, so we need to create the directory, and then assign ownership to postgres.

For our example, we’ll create a /database directory in our file system root, and within that a pg subdirectory:

Create a New Directory for Postgres Data:
$ sudo mkdir –p /database/pg
$ sudo chown -R postgres /database


In the above, the –p flag tells bash to make sure any leading directories are created on the way to the final directory. The –R flag used with chown tells chown to work recursively, and apply the ownership change to any nested directories or files.

Now we need to initialize the new database cluster in the new directory. Note, if you are already running an existing postgres instance, it is best to stop the server before moving the data directory:

Stop Existing Postgres Instance:
$ sudo service postgresql stop


Once again, we will need to get to our postgres user through root, and then initialize the new cluster (note – I’m showing the full terminal prompt here for clarity, so we can see which user privileges are used):

Initialize the New Postgres Cluster Using initdb:
xivsolutions@mint-vm / $ su -
mint-vm ~ # su - postgres
postgres@mint-vm ~ $ /usr/lib/postgresql/9.4/bin/initdb -D /database/pg
postgres@mint-vm ~ $ exit
mint-vm ~ # exit
xivsolutions@mint-vm / $


Don’t forget to exit from the postgres user, and then also exit root as shown.

Next, we need to edit the postgresql.conf file and point the data_directory variable to the new cluster location. We can open the file with elevated privileges in gedit like so:

Open the postgresql.conf File with Elevated Privileges in Gedit:
$ sudo gedit /etc/postgresql/9.4/main/postgresql.conf


Scroll down in the file to the file locations section, comment out the existing entry for data_directory (using the ‘#’ character), and add a new entry pointing to our new directory like so:

Replace the data_directory Parameter to Point at the New Directory Location:
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
# Add a '#' to the beginning of the line below:
# data_directory = '/var/lib/postgresql/9.4/main'    # use data in another directory
data_directory = '/database/pg'          
# Blah blah more configuration stuff . . . 


Save and exit Gedit, then re-start the postgres server:

Restart the Postgres Server:
$ sudo service postgresql restart

NOTE: If you forgot to stop an existing instance of Postgres before moving the data directory (or for other potential reasons), you may receive an error indicating that “the pid file is invalid, and to manually kill the stale server process.” If this happens, you can manually kill the process with the following:

Manually Kill a Stale Postgresql Server Process:
sudo pkill -u postgres


Before restarting the server, it is important to make certain all Postgres processes have, in fact, stopped. Make sure that the shell command ps returns no results when used as follows:

Ensure All Postgres Processes have been Killed:
ps -u postgres


Then go ahead and restart postgres as per previous.

You should now be running Postgres in the new location. Note that any existing database files did not move as part of this process, so you will need to follow the previous steps for creating a new super user, etc.

In upcoming posts, we’ll take a look at some of the stand-out features of this database.

Additional Resources and Items of Interest

If you are just digging in to PostgreSQL, Linux, or both, see some of these excellent resources for additional information:

Some OSS projects I’m privileged to be involved with using PostgreSQL:


Posted on April 19 2015 07:41 AM by jatten     

Comments (1)

A More Useful Port of the Chinook Database to Postgresql

Posted on April 5 2015 12:26 PM by jatten in Database, Postgres, CodeProject   ||   Comments (0)

chinookI use Chinook Database as the sample database for a lot of things, primarily because the same data set is available for most of the major database platforms, so it's easy to port/compare etc. Chinook Database offers up a set of tables with enough sample data to satisfy most testing/demo needs, in a general enough schema that it is easy to get something up and running quickly.

However, the Chinook database as made available at the Chinook site maintains the same object naming conventions (proper-cased table and column names) across database platforms, and this I DON'T like. Also, the Postgres version of Chinook available as part of the Chinook package uses integer primary keys, but not auto-incrementing integers (this corresponds to the serial data type in Postgres).

Image by Ingrid Talar  |  Some Rights Reserved

Because I use Chinook fairly often with Postgres, I decided to make a few mods to the Chinook Postgres script to fix these things, and provide a more native PG experience when using this otherwise handy sample database.

Use Postgres-Idiomatic SQL

Postgresql ("Postgres" or "pg") has its roots in the Unix world. Database object names are case-sensitive, and in fact the convention is to use lower-case names, and where needed, separate with underscores. It is possible to use proper-cased object names in Postges by escaping them with double-quotes. However, this makes for some atrocious-looking SQL.

A sample excerpted from the Chinook script Postgres perfectly illustrates this:

Excerpt from Standard Postgres Chinook Script:
    "AlbumId" INT NOT NULL,
    "Title" VARCHAR(160) NOT NULL,
    "ArtistId" INT NOT NULL,
    CONSTRAINT "PK_Album" PRIMARY KEY  ("AlbumId")


The above forces the Album table to be created with proper-cased object names (the table name itself, as well as the columns). However, from this point forward you will need to use the double-quote escapes in any SQL you push in which requires the use of object names explicitly.

At a minimum, you would need to do THIS to pull all the records from the Album table:

Select all the Records from the Chinook DB Album Table:


Note the quotes around the table name. If you don’t use those, you’ll get an error indicating that the table album doesn’t exist. This is because Postgres will automatically down-case incoming SQL before processing it.

Now let’s imagine you wanted to set some criteria on that SELECT statement:

Select some specific Records from the Chinook DB Album Table:
SELECT * FROM "Album" WHERE "ArtistId" = 2


Again with the double-quote escape sequence. Things continue to go downhill from there. Imagine a simple JOIN between two tables with a minimal set of columns returned:

Select Records from the Chinook DB Album Table using a JOIN to Artists:
  "Album" AS al, 
  "Artist" AS ar
  al."ArtistId" = ar."ArtistId";


Yeah. Now things start to look ugly.

Composing SQL against this (and even worse, code which incorporates said SQL) gets painful fast.

Use Serial Integer Primary Keys

The maintainers of Chinook Database for some reason chose not to implement auto-incrementing integers for table primary keys in the Postgres version. Whatever the reason, I want auto-incrementing integer keys on the tables in my Chinook database.

Making this happen took a little doing, since the sample data itself establishes table relationships based on existing primary key data. How to solve this?

The Postgres serial type utilizes sequences to maintain the current value of a particular serial column. We can specify the starting value of a sequence, and the amount by which it is incremented, using the Postgres CREATE SEQUENCE command:

Create Sequence Example:
CREATE SEQUENCE "albums_id_seq"
 MAXVALUE 2147483647
 START 347
SELECT setval('"public"."albums_id_seq"', 347, true);


But wait, John – you pluralized the name of the albums table there. Also, what’s with that setting the START value at 347?

Good question. We’ll get to the pluralized naming convention – I changed that for the entire Chinook script. As for the initial value, the Chinook sample data set contains an initial set of 347 album records. Therefore, we want to set an initial value for our table PK sequence at 347, so that the next ID provided will be 348. 

Down-Casing Object Names, and Simplifying

I went through the Chinook script for Postgres, and modified the object names for all of the tables and columns, down-casing everything, and adding underscores as needed. I also pluralized the table names themselves. With that in mind, as well as the fact that we want to use a serial data type for our primary key columns, the create statement for our albums table might now look more like this:

Modified Create SQL for Chinook Album Table:
    id int DEFAULT nextval('albums_id_seq'::regclass) NOT NULL,
    title VARCHAR(160) NOT NULL,
    artist_id INT NOT NULL,
    CONSTRAINT pk_albums PRIMARY KEY  (id)


Notice we have pluralized the table name, and also used a straight-forward id as the name for the primary key column, doing away with the problematic AlbumId (we could have used album_id, but why?). We set the default value for the id using the Postgres nextval() function, which will pull the next value based on the sequence we created unless a value is provided.

Get the Modified Chinook Script from Github

You can find the full, modified version of the Chinook database script at my Github repo. There are two versions, each on a separate branch.

Branch Master contains the full Chinook script, with down-cased/underscore-separated object names. However, table names have not been pluralized, and table primary key columns maintain the underscored version of the original column name. For example, AlbumID becomes simply album_id.

Branch pg_names contains the same database, but with table names pluralized, and table PK column names reduced to simply id. In other words, the table Album in the original Chinook DB is now albums, and the PK for albums is simply id.

See Something Amiss?

I am not a DBA, and it is possible I did something stupid in here. I like to think I know enough database to do those things I need to do as a developer. However, if you see something I did that might be accomplished better another way, please do sound off in the comments, shoot me a PR on Github, or shoot me an email at the address in the “About the Author” section.


Posted on April 5 2015 12:26 PM by jatten     

Comments (0)

ASP.NET Web API: Understanding OWIN/Katana Authentication/Authorization Part III: Adding Identity

Posted on February 15 2015 06:19 PM by jatten in ASP.NET MVC, ASP.Net, C#, CodeProject   ||   Comments (7)

eyball-500This is the third post in a series in which we have built up a minimal, self-hosted, OWIN-based Web Api application essentially from scratch. Our objective has been to develop a better understanding of how the various components fir together and interact in an OWIN-based environment, and to do so without creating any dependencies on IIS or the heavy weight System.Web.dll.

Up to this point, we have created a basic Web Api, and implemented our own authentication/authorization, using basic OWIN authorization, and our own set of models. What we have built so far represents a bare-bones model of how authentication and authorization work in an OWIN-based application. We have taken some architectural shortcuts in the name of maintaining a simple, easy to understand structure as again, our goal so far has emphasized concept over details.

Image by alles-schlumpf  |  Some Rights Reserved

Previous posts, in order:

In this post, we are going pull in the ASP.NET Identity framework, and ideally, we will again achieve a better understanding of how Identity fits in to a general Web Api application, and we will allow Identity to perform some of the heavy lifting for us when it comes to difficult, hard-to-get-right-even-for-the-experts details such as crypto and security details.

Source Code for Examples

We are building up a project over a series of posts here. In order that the source for each post make sense, I am setting up branches that illustrate the concepts for each post:

On Github, the branches of the Web Api repo so far look like this:

  • Branch: Master - Always the most current, includes all changes
  • Branch: auth-db - The code we build up in the course of the previous post, adding a persistence layer for our authentication system.
  • Branch: auth-identity - the code we will build out in the course of this post. We will start where we left off in the previous post, and modify to bring in a minimal implementation using Identity Framework.

The code for the API client application is in a different repo, and the branches look like this:

  • Branch: Master - Always the most current, includes all changes
  • Branch: owin-auth - Added async methods, and token-based authentication calls to the Web Api application. The code for the client application remains unchanged, except when we need to switch out user credentials.

In the previous article, we created create the classes MyUser and MyUserClaim classes in order to implement our authorization and authentication mechanism in the OWIN/Katana environment, and which also became our code-first models for database persistence via Entity Framework. We also created the MyUserStore class, which contained the methods necessary to save and retreive user authentication data from our backing store.

We had assembled these very rudimentary classes into a functioning authentication and authorization framework of sorts, using them in our application to perform the basic functions needed to properly authenticate a user, and establish a minimal authorization mechanism based upon the role claims possessed by each user.

In this post, we are going to use ASP.NET Identity instead, and replace our crude, homebuilt mechanism with a fully-functioning, if basic, auth system.

Core Identity Framework

To understand how Identity will fit into our application, and the OWIN/Katana environment, it is useful to examine the structure of Identity framework itself.

The actual core Identity library is Microsoft.AspNet.Identity.Core. This library defines a host of interfaces upon which the function of Identity is based, and a smaller number of concrete implementation classes which are expressed in terms of these interfaces. We actually already have this library in our project, because we pulled in the Microsoft.AspNet.Identity.Owin library previously via Nuget. We haven't used any of the identity components to this point, but we made use of some items from dependent libraries included in that Nuget package, such as Microsoft.Owin.Security and Microsoft.Owin.OAuth

In general, the models which might need to be consumed in an application are expressed as interfaces, and the internals of the framework provide implementation for those interfaces. It is up to the application, and/or any other frameworks pulled in, to provide the concrete implementations for these model interfaces.

For example, the core Identity framework provides us with a pair of interfaces to represent a User:

Two Versions of the IUser Interface:
// Interface with generic type argument for the Key:
public interface IUser<out TKey>
    TKey Id
    string UserName
// Interface derived from IUser<Tkey> Specifying string Key:
public interface IUser : IUser<string> { }


In a similar manner, most of the interfaces defined in the core Identity framework to represent persistence models are expressed in a manner which allows us to specify the type for the key to be used.

Additionally, many of the Identity interfaces are dependent on other interfaces in the framework. In these cases, the interface is expressed in terms of a generic type argument representing the concrete implementation of the dependency. For example, core Identity framework provides two interfaces to represent a UserStore:

Two Versions of the IUserStore Interface:
// Interface with generic type arguments for User, and the User Key:
public interface IUserStore<TUser, in TKey> : IDisposable
where TUser : class, IUser<TKey>
    Task CreateAsync(TUser user);
    Task DeleteAsync(TUser user);
    Task<TUser> FindByIdAsync(TKey userId);
    Task<TUser> FindByNameAsync(string userName);
    Task UpdateAsync(TUser user);
// Interface expressing IUserStore in terms of generic User type, 
// and specifying a string User Key
public interface IUserStore<TUser> : IUserStore<TUser, string>, IDisposable
where TUser : class, IUser<string>


If you explore the Microsoft.AspNet.Identity.Core library using a free tool such as Telerik's fine Just Decompile, you can explore the various interfaces and concrete classes available, and develop an understanding of how they relate. However, a quick look using even the VS Object Browser will reveal that there are no concrete implementations of the basic model interfaces we need in order to implement Identity in our application. For this, we either need to roll our own, or pull in another library which provides ready-to-use implementations.

Since we are already using Entity Framework, in our case this is easy.

Identity and Entity Framework

The Microsoft.AspNet.Identity.EntityFramework library provides concrete implementation classes needed to use Identity from an application using Entity Framework. In this library we find some model classes we can use as-is, or which we can extend and customize as needed. For example, the IdentityUser class provides a concrete implementation for IUser<Tkey> :

The Base IdentityUser Class:
// Base implements IUser<TKey> and is expressed with generic type arguments
// for other model types required by Identity Framework:
public class IdentityUser<TKey, TLogin, TRole, TClaim> : IUser<TKey>
    where TLogin : IdentityUserLogin<TKey>
    where TRole : IdentityUserRole<TKey>
    where TClaim : IdentityUserClaim<TKey>
    public IdentityUser()
        this.Claims = new List<TClaim>();
        this.Roles = new List<TRole>();
        this.Logins = new List<TLogin>();
    public virtual TKey Id { get; set; }
    public virtual string UserName { get; set; }
    public virtual string Email { get; set; }
    public virtual bool EmailConfirmed { get; set; }
    public virtual string PhoneNumber { get; set; }
    public virtual bool PhoneNumberConfirmed { get; set; }
    public virtual string SecurityStamp { get; set; }
    public virtual bool TwoFactorEnabled { get; set; }
    public virtual string PasswordHash { get; set; }
    public virtual int AccessFailedCount { get; set; }
    public virtual bool LockoutEnabled { get; set; }
    public virtual DateTime? LockoutEndDateUtc { get; set; }
    public ICollection<TLogin> Logins { get; set; }
    public ICollection<TRole> Roles { get; set; }
    public ICollection<TClaim> Claims { get; set; }
// Alternate implementation derives from Generic implementation, 
// and expresses Generic model types in terms of classes defined 
// within Identity.EntityFrameowrk Library: 
public class IdentityUser : IdentityUser<string, IdentityUserLogin, 
    IdentityUserRole, IdentityUserClaim>, IUser, IUser<string>
    public IdentityUser()
        this.Id = Guid.NewGuid().ToString();
    public IdentityUser(string userName) : this()
        this.UserName = userName;


We can see in the above example that there is a lot of functionality ready to go in the basic IdentityUser implementation. Also, note that the various generic type arguments provided in the base implementation are again provided such that we might extend our Identity model classes with custom implementations, for example, if we wanted to use integer keys instead of strings.

The Identity.EntityFramework library provides similar implementations for the other interfaces defined in Identity.Core. In addition, as we might expect, Identity.EntityFramework also includes the IdentityDbContext class, which knits together the various model classes, ready for use in a EF/Code-First application.

What is important here is understanding that the Identity.Core library provides the interfaces we need, and implements the interactions between those interfaces. It is up to us to provide the implementation for those interfaces, either by rolling our own, or by using a library specific to our persistence model such as Identity.EntityFramework.

Add Identity.EntityFramework Library Via Nuget

Since we are using Entity Framework in our minimal OWIN Web Api project, we will pull in the Identity.EntityFramework package we discussed above to take advantage of the ready-made Identity implementation afforded by the Identity team.

NOTE: This post assumes you are working with ASP.NET < 5.0, and Identity 2.1. As of this publication date, the newest package for this library is a pre-release of Identity 3.0 which targets ASP.NET 5. Unless you are working with ASP.NET 5.0 ("vNext") you want to make sure you are pulling in version 2.1

Add the Microsoft.AspNet.Identity.EntityFramework Nuget Package:
PM> Install-Package Microsoft.AspNet.Identity.EntityFramework -Version 2.1.0


We already have the Identity.Core library in our example project, because it was included when we pulled in the Identity.Owin Nuget package in Part I of this series.

Add Identity to the Self-Hosted Web Api - Models and Stores

To get started, we will be picking up where we left off in the previous post. Recall that we had added an AuthModels.cs file, and coded up a MyUser class, a MyUserClaim class, a MyPasswordHasher class, and a MyUserStore class.

We can get rid of all that now, delete the AuthModels.cs file.

In order to add the (mostly) ready-to-use Identity framework to our project, let's add a new code file named IdentityModels.cs, and add the following code:

Add Identity Models:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// Add using statements:
using Microsoft.Owin;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.Owin;
using Microsoft.AspNet.Identity.EntityFramework;
namespace MinimalOwinWebApiSelfHost.Models
    public class ApplicationUser : IdentityUser
        // A default Constructor:
        public ApplicationUser() { }
        public ApplicationUser(string email) : base(email)
            // Use the email for both user name AND email:
            UserName = email;
    public class ApplicationUserManager 
        : UserManager<ApplicationUser>
        public ApplicationUserManager(IUserStore<ApplicationUser> store) 
            : base(store) { }
        public static ApplicationUserManager Create(
            IdentityFactoryOptions<ApplicationUserManager> options,
            IOwinContext context)
            return new ApplicationUserManager(
                new UserStore<ApplicationUser>(


Now, let's take a good hard look at what's going on in the above code.

First, we have added an ApplicationUser class, which derives from IdentityUser. IdentityUser is a concrete implementation if the Identity.Core IUser interface, and is provided by the Identity.EntityFramework library we discussed previously. We aren't doing much in this derived class at this point, but we will be adding to it later.

Next, we have the ApplicationUserManager class, which derives from UserManager. Unlike IdentityUser, the UserManager class is a part of the Identity.Core library. In other words, any application using the Identity framework can expect to have access to UserManager, or a derivation similar to what we have done above.

Notice that our ApplicationUserManager expects an argument of IUserStore as a constructor parameter. What's this?

We'll take a short detour to discuss UserManager and UserStore, and the purpose behind each.

UserManager and UserStore - What's the Difference?

The core Identity framework defines the UserManager class to implement various functionality required to, well, manage user information according to business rules and configuration established either by Identity framework itself, or as part of configuration options set up during development.

These framework "rules" and configuration items are independent of the specific persistence store used to save and retrieve the user identity data. In other words, the UserManager understands and works with Identity model objects, without concern for the concrete database underlying the application.

Examples of the sorts of functionality afforded by UserManager include methods such as:

  • CreateAsync(TUser user, string Password)
  • AddToRoleAsync(TKey userId, string role)
  • AddClaimAsync(Tkey userId, Claim claim)

The UserManager class is defined in terms of various interfaces which represent abstractions over database-specific implementation models. As an example, the basic UserManager is defined with a generic type argument for the IUser implementation, which must be specified. The UserManager class also requires a constructor argument which implements the IUserStore interface.

A concrete implementation of IUserStore represents the underlying persistence layer of the application. In other words, a UserStore implementation knows how to "talk" to a specific data store (such as MongoDb, SQL Server, RavenDb, Etc.).

In our code, we are using the default UserStore implementation provided by EntityFramework (which is, itself, an abstraction over our SQL CE or SQL Server database…).

The idea behind defining a UserManager base class in Identity.Core in terms of an interface IUserStore is to achieve a clean separation between the business rules governing how authentication and authorization occurs between Identity model objects, and the details of the application's specific backing store.

Within out application proper, when we use Identity we should generally be using the UserManager (or a derived version of it) the work directly with our identity model objects. We rarely have need to consume an instance of UserStore directly, except for the purpose of injecting it as a constructor argument to UserManager.

UserManager, UserStore, and Identity:


Also notice in our code, we have added a static method, Create(). We'll discuss this momentarily as well. But first, we will update the code for our database context.

Update the DbContext to Inherit from IdentityDbContext

When we finished the last article in this series, we had updated our ApplicationDbContext code file to look like this:

The Previous DbContext:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// Add using:
using System.Data.Entity;
using System.Security.Claims;
namespace MinimalOwinWebApiSelfHost.Models
    public class ApplicationDbContext : DbContext
        public ApplicationDbContext()
            : base("MyDatabase")
        static ApplicationDbContext()
            Database.SetInitializer(new ApplicationDbInitializer());
        public IDbSet<Company> Companies { get; set; }
        public IDbSet<MyUser> Users { get; set; }
        public IDbSet<MyUserClaim> Claims { get; set; }
    public class ApplicationDbInitializer 
        : DropCreateDatabaseAlways<ApplicationDbContext>
        protected async override void Seed(ApplicationDbContext context)
            context.Companies.Add(new Company { Name = "Microsoft" });
            context.Companies.Add(new Company { Name = "Apple" });
            context.Companies.Add(new Company { Name = "Google" });
            // Set up two initial users with different role claims:
            var john = new MyUser { Email = "" };
            var jimi = new MyUser { Email = "" };
            john.Claims.Add(new MyUserClaim 
                ClaimType = ClaimTypes.Name, 
                UserId = john.Id, 
                ClaimValue = john.Email 
            john.Claims.Add(new MyUserClaim 
                ClaimType = ClaimTypes.Role, 
                UserId = john.Id, 
                ClaimValue = "Admin" 
            jimi.Claims.Add(new MyUserClaim 
                ClaimType = ClaimTypes.Name, 
                UserId = jimi.Id, 
                ClaimValue = jimi.Email 
            jimi.Claims.Add(new MyUserClaim 
                ClaimType = ClaimTypes.Role, 
                UserId = john.Id, 
                ClaimValue = "User" 
            var store = new MyUserStore(context);
            await store.AddUserAsync(john, "JohnsPassword");
            await store.AddUserAsync(jimi, "JimisPassword");


In the same code file, we had defined both our ApplicationDbContext, and a database initializer.

Now that we have access to the Identity.Core and Identity.EntityFramework libraries, we can simplify this somewhat.

First of all, Identity.EntityFramework provides us with IdentityDbContext, which, as its name implies, is an Identity-specific implementation of the DbContext. There is a little more going on under the hood than we will cover here, but essentially, IdentityDbContext provides us with a base class from which we can inherit, and which provides a DbContext implementation which works with the base Identity.EntityFramework models we will be working with.

First, we are going to modify the code for our ApplicationDbContext above to derive from IdentityDbContext. Then, we will update the ApplicationDbInitializer to work with our new DbContext and Identity models.

Modify DbContext to Derive from IdentityDbContext:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// Add using:
using System.Data.Entity;
using System.Security.Claims;
// Add THESE to use Identity and Entity Framework:
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
namespace MinimalOwinWebApiSelfHost.Models
    // Derive from IdentityDbContext:
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
        public ApplicationDbContext()
            : base("MyDatabase") { }
        static ApplicationDbContext()
                new ApplicationDbInitializer());
        // Add a static Create() method:
        public static ApplicationDbContext Create()
            return new ApplicationDbContext();
        // We still need a DbSet for our Companies 
        // (and any other domain objects):
        public IDbSet<Company> Companies { get; set; }
    public class ApplicationDbInitializer 
        : DropCreateDatabaseAlways<ApplicationDbContext>
        protected async override void Seed(ApplicationDbContext context)
            context.Companies.Add(new Company { Name = "Microsoft" });
            context.Companies.Add(new Company { Name = "Apple" });
            context.Companies.Add(new Company { Name = "Google" });
            // Set up two initial users with different role claims:
            var john = new ApplicationUser 
                Email = "", 
                UserName = "" 
            var jimi = new ApplicationUser 
                Email = "", 
                UserName = "" 
            // Introducing...the UserManager:
            var manager = new UserManager<ApplicationUser>(
                new UserStore<ApplicationUser>(context));
            var result1 = await manager.CreateAsync(john, "JohnsPassword");
            var result2 = await manager.CreateAsync(jimi, "JimisPassword");
            // Add claims for user #1:
            await manager.AddClaimAsync(john.Id, 
                new Claim(ClaimTypes.Name, ""));
            await manager.AddClaimAsync(john.Id, 
                new Claim(ClaimTypes.Role, "Admin"));
            // Add claims for User #2:
            await manager.AddClaimAsync(jimi.Id, 
                new Claim(ClaimTypes.Name, ""));
            await manager.AddClaimAsync(jimi.Id, 
                new Claim(ClaimTypes.Role, "User"));


In the above code, notice that we have now implemented our ApplicationDbContext by deriving from IdentityDbContext<ApplicationUser> . In so doing, we have specified a DbContext implementation which will be ready to use, and already work with our ApplicationUser class.

We have also updated our ApplicationDbInitializer, making use of the handy interface afforded by our ApplicationUserManager to easily add our test users, and related user claims.

Pay close attention here. Remember previously, how we went ahead and create a mock password hasher, and all that nonsense about implementing a proper crypto mechanism for hashing passwords? here, Identity has taken care of all that for us. No mocking needed. Just the way we like it, we have left the details of the crypto to folks who know what they are doing. Remember, crypto is hard, even for the experts, and it is a solved problem.

Now, before we go much further, we are going to take another short detour. Notice how, like we did with ApplicationUserManager, we also defined a static Create() method on our ApplicationDbContext? We're going to take a look at why we did that now.

Context per Request and CreatePerOwinContext

From the standpoint of our data model, we want to make sure that we are always working with the same instance of our database context, and hence, with the same set of objects. For example, if two separate instances of ApplicationDbContext (or, similarly, UserStore or UserManager) are created while processing the same HTTP request, it is possible we could introduce changes to two instances of the same  user data.

We want to make sure that when we retreive a user object, it will always refer to the same instance of that user's data within the context of a single HTTP request.

The Microsoft.AspNet.Identity.Owin library provides an extension method by which we can ensure a single instance of an object is created per OwinContext. The CreatePerOwinContext() method allows us to pass in a generic type argument, and a function reference which returns an instance of the desired object. We set this up during the Owin Configuration() method. Then, when the OwinContext object is created for each incoming HTTP request, a discreet instance of the desired object will be created per OWIN context.

For a deeper look at this concept, see Per request lifetime management for UserManager class in ASP.NET Identity

In our application, we want to make sure that during request processing, we are only ever working against the same instance of ApplicationDbContext, as well as ApplicationUserManager. We achieve this by adding those state Create() methods on ApplicationDbContext and ApplicationUserManager respectively, and then passing references to them to CreatePerOwinContext() during the Owin Configuration() method.

We will want to add the following code to our ConfigureAuth() method in our Startup class:

Create ApplicationDbContext and ApplicationUserManager per Owin Context:
private void ConfigureAuth(IAppBuilder app)
    // Create per OWIN Context:
    var OAuthOptions = new OAuthAuthorizationServerOptions
        TokenEndpointPath = new PathString("/Token"),
        Provider = new ApplicationOAuthServerProvider(),
        AccessTokenExpireTimeSpan = TimeSpan.FromDays(14),
        // Only do this for demo!!
        AllowInsecureHttp = true
    app.UseOAuthBearerAuthentication(new OAuthBearerAuthenticationOptions());


Recall from our previous explorations of OWIN and Katana that when we pass function references like this, the function itself is not executed at this point in our code. Instead, a reference to the function is added to the Owin Environment Dictionary, which will then be Invoked each time a new Owin context is created in response to an incoming HTTP request.

We can see some of the logic to all this if we take a closer look at the static Create() method we defined on our ApplicationUserManager class:

The Create() Method from ApplicationUserManager:
public static ApplicationUserManager Create(
    IdentityFactoryOptions<ApplicationUserManager> options,
    IOwinContext context)
    return new ApplicationUserManager(
        new UserStore<ApplicationUser>(


Notice how even here, we are initializing an instance of UserStore and passing in a reference to ApplicationDbContext by retrieving it from the OwinContext instance? This way we ensure that even here, we are using the single DbContext object instance created specifically for each request.

Update OAuth Server Provider for Identity

Now that we have implemented a very basic Identity model, we can modify our ApplicationOauthServerProvider. In the call to GrantResourceOwnerCredentials(), we can avail ourselves of our ApplicationUserManager and Identity models to simplify the grant process.

Add Microsoft.AspNet.Identity.Owin to the using statements at the top of the file, and then replace the existing code as follows:

Update the code for GrantResourceOwnerCredentials for Identity:
using System.Threading.Tasks;
// Add Usings:
using Microsoft.Owin.Security;
using Microsoft.Owin.Security.OAuth;
using System.Security.Claims;
using MinimalOwinWebApiSelfHost.Models;
// Add to use Identity:
using Microsoft.AspNet.Identity.Owin;
namespace MinimalOwinWebApiSelfHost.OAuthServerProvider
    public class ApplicationOAuthServerProvider 
        : OAuthAuthorizationServerProvider
        public override async Task ValidateClientAuthentication(
            OAuthValidateClientAuthenticationContext context)
            // This call is required...
            // but we're not using client authentication, so validate and move on...
            await Task.FromResult(context.Validated());
        public override async Task GrantResourceOwnerCredentials(
            OAuthGrantResourceOwnerCredentialsContext context)
            // ** Use extension method to get a reference 
            // to the user manager from the Owin Context:
            var manager = context.OwinContext.GetUserManager<ApplicationUserManager>();
            // UserManager allows us to retrieve use with name/password combo:
            var user = await manager.FindAsync(context.UserName, context.Password);
            if (user == null)
                    "invalid_grant", "The user name or password is incorrect.");
            // Add claims associated with this user to the ClaimsIdentity object:
            var identity = new ClaimsIdentity(context.Options.AuthenticationType);
            foreach (var userClaim in user.Claims)
                identity.AddClaim(new Claim(userClaim.ClaimType, userClaim.ClaimValue));


Once again, see in the above how we made sure to grab a reference to the ApplicationUserManager instance from the context object? The context object helpfully provides a GetUserManager() extension method.

Also note how the ApplicationUserManager makes it very convenient to retrieve a user object with a user name and password. If the password fails to match, null will be returned, and the invalid grant error returned.

Update the CompaniesController to Use the DbContext per Request

We don't HAVE to do this, but we can. We might update our CompaniesController to take advantage of the Context-per-Request strategy afforded by Identity here.

Make sure to add Microsoft.AspNet.Identity.Owin to the using statements at the top of the CompaniesController file.

Update CompaniesController to Use Context per Request:
public class CompaniesController : ApiController
    // Ditch THIS:
    //ApplicationDbContext dbContext = new ApplicationDbContext();
    // Replace with something like THIS:
    ApplicationDbContext dbContext
            return Request.GetOwinContext().Get<ApplicationDbContext>();
    ... All the rest of the controller code....


With that, we can give our new and improved application a test run, using the same Api Client application from our previous post.

Running the Application with Identity in Place

If we spin up our Web Api, all should look well:

Running the Web Api Application - All is Well:


Next, if we run the API Client application, making sure we use our valid user credentials (they should match the credentials for the Admin user in the Seed() method), everything should work as before:

Running the API Client Application:


Similarly, if we modify the code in our client application and pass an invalid password, we get an invalid grant error:

Invalid Password Submitted by Client Returns Invalid Grant Error:


And, if we change the client credentials to those of the user in the plain old User role (which does NOT have authorization to access our CompaniesController), we receive a 401/Unauthorized error:

API Client with Insufficient Authorization:


Summing it Up

In the course of the last four articles, we have hopefully developed a better idea of how the pieces fit together in an OWIN-based Web Api application. The lines between the various frameworks become blurry as an application grows, and my objective was to break things down in a manner that would bring some clarity to what happens where, and why.

The structure of our example application is crude, and if you were to take this a few steps further, you would definitely want to change some things, do some refactoring, and implement a great deal more exception and error handling.

Similarly, our console-based API Client application is sufficient only to the task of exercising our Web Api for demonstration purposes.

From here, we could go a long ways further in developing a claims-based authorization model. As it stands, our little sample application does use claims, but relies on the in-build ability of [Authorize] attribute to perform a role-based authorization check. We will explore claims more extensively in an upcoming post.

As always, feedback is most welcome, especially if you noticed me doing something stupid, or find outright errors in the code. Pull Requests are welcome against the sample repo, so long as they address improvements or bug fixes. For obvious reasons, I want to keep the code samples in sync with the articles.

Additional Resources and Items of Interest


Posted on February 15 2015 06:19 PM by jatten     

Comments (7)

About the author

My name is John Atten, and my "handle" on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, JavaScript/Node, and databases of many flavors. Actively learning always. I dig web development. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

johnatten at typecastexception dot com

Web Hosting by