Using ADO.NET Implementation of ASP.NET Identity 2 for PostgreSQL
In This Topic
ASP.NET Identity is a new system of user authentication and authorization, that continues the evolution of ASP.NET membership system, and is used in the Visual Studio 2013 Update 2 project templates for ASP.NET MVC, Web Forms, Web API and SPA. You can read more on ASP.NET Identity in Microsoft documentation.
dotConnect for PostgreSQL enables you to employ an implementation of ASP.NET Identity for PostgreSQL database using either ADO.NET or Entity Framework functionality in your web applications. This allows you to use such ASP.NET Identity benefits as unit-testable user authentication system, social login support, OWIN integration, etc. This tutorial demonstrates creating an ADO.NET implementation of ASP.NET Identity.
To complete this tutorial you need Visual Studio 2013 Update 2 or higher. For Visual Studio 2013 or Visual Studio 2013 Update 1 see Using ADO.NET Implementation of ASP.NET Identity 1 for PostgreSQL.
In order to create an ASP.NET MVC 5 application using dotConnect for PostgreSQL for storing identity information, perform the following steps:
-
In the Add New Project dialog box expand Visual C# on the left, then Web, and then select ASP.NET Web Application. Name your project, for example, "AspNet_Identity_Application" and then click OK.
-
In the New ASP.NET Project dialog box, select the MVC template with the default options (that includes Individual User Accounts as authentication method) and click OK.
-
In Solution Explorer, right-click your project and select Manage NuGet Packages from the shortcut menu. In the search text box dialog, type "Identity.EntityFramework". Select this package in the list of results and click Uninstall. You will be prompted to uninstall the dependency package EntityFramework. For Visual Studio 2013 Update 2 click Yes. For Visual Studio 2013 Update 3 or higher click No.
-
Add references to the necessary dotConnect for PostgreSQL assemblies:
- Devart.Data.dll
- Devart.Data.PostgreSql.dll
- Devart.Data.PostgreSql.Web.Identity.dll
For ASP.NET Identity 2 you need to add the reference to the Devart.Data.PostgreSql.Web.Identity.dll, having the revision number 2, and installed to the Program Files\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 2 folder
When creating an ASP.NET application, using ASP.NET Identity, you can use the existing ASP.NET Identity functionality as is or customize this functionality. For example, you may add storing some custom information about the user. In our tutorial we will demonstrate both options.
Using Existing ASP.NET Identity Functionality
First, you need to create a schema that will store information about users. To do it, execute the Install_identity_tables.sql script, which goes with dotConnect for PostgreSQL. By default, this script is in the Program Files\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 2 folder. You can also copy this script from the Identity Database Script chapter of this topic. After this perform the following steps:
- In the Models project folder open the IdentityModels.cs (or IdentityModels.vb for Visual Basic) file.
that is in the Models folder, from the project.
Replace the file contents with
using System.Security.Claims;
using System.Threading.Tasks;
using Microsoft.AspNet.Identity;
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
using ApplicationUser = Devart.Common.Web.Identity.IdentityUser;
namespace AspNet_Identity_Application.Models
{
public static class ApplicationUserExtensions
{
public static async Task<ClaimsIdentity> GenerateUserIdentityAsync(this ApplicationUser user, UserManager<ApplicationUser> manager)
{
// Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
var userIdentity = await manager.CreateIdentityAsync(user, DefaultAuthenticationTypes.ApplicationCookie);
// Add custom user claims here
return userIdentity;
}
}
}
Imports System.Security.Claims
Imports System.Threading.Tasks
Imports Microsoft.AspNet.Identity
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
Imports ApplicationUser = Devart.Common.Web.Identity.IdentityUser
Public Module ApplicationUserExtensions
<System.Runtime.CompilerServices.Extension> _
Public Async Function GenerateUserIdentityAsync(user As ApplicationUser, manager As UserManager(Of ApplicationUser)) As Task(Of ClaimsIdentity)
' Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
Dim userIdentity = Await manager.CreateIdentityAsync(user, DefaultAuthenticationTypes.ApplicationCookie)
' Add custom user claims here
Return userIdentity
End Function
End Module
Note that if you have specified the name for the application other than "AspNet_Identity_Application", you must use the corresponding namespace name in the code above instead of "AspNet_Identity_Application".
-
In the Controllers project folder open the AccountController.cs (or AccountController.vb for Visual Basic) file.
If you use Visual Studio 2013 Update 2, replace
using Microsoft.AspNet.Identity.EntityFramework;
Imports Microsoft.AspNet.Identity.EntityFramework
with:
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
using ApplicationUser = Devart.Common.Web.Identity.IdentityUser;
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
Imports ApplicationUser = Devart.Common.Web.Identity.IdentityUser
If you use Visual Studio 2013 Update 3 or higher, there is no such line, just add the above code to the beginning of the file.
-
In the App_Start project folder open the IdentityConfig.cs (or IdentityConfig.vb for Visual Basic) file.
Replace
using Microsoft.AspNet.Identity.EntityFramework;
Imports Microsoft.AspNet.Identity.EntityFramework
with:
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
using ApplicationUser = Devart.Common.Web.Identity.IdentityUser;
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
Imports ApplicationUser = Devart.Common.Web.Identity.IdentityUser
-
Replace the following line in the Create method:
var manager = new ApplicationUserManager(new UserStore<ApplicationUser>(context.Get<ApplicationDbContext>()));
Private manager As var = New ApplicationUserManager(New UserStore(Of ApplicationUser)(context.[Get](Of ApplicationDbContext)()))
with:
var manager = new ApplicationUserManager(new PgSqlUserStore());
Dim manager = New ApplicationUserManager(New PgSqlUserStore())
-
In the App_Start project folder open the Startup.Auth.cs (or Startup.Auth.vb for Visual Basic) file.
If you use Visual Studio 2013 Update 2, replace
using Microsoft.AspNet.Identity.EntityFramework;
Imports Microsoft.AspNet.Identity.EntityFramework
with:
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
using ApplicationUser = Devart.Common.Web.Identity.IdentityUser;
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
Imports ApplicationUser = Devart.Common.Web.Identity.IdentityUser
If you use Visual Studio 2013 Update 3 or higher, there is no such line, just add the above code to the beginning of the file.
-
Delete the following line:
app.CreatePerOwinContext(ApplicationDbContext.Create);
app.CreatePerOwinContext(ApplicationDbContext.Create)
For Visual Studio 2013 Update 3 or higher only - in the Controllers project folder open the ManageController.cs (or ManageController.vb for Visual Basic) file. Add the following code to the beginning of the file:
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
using ApplicationUser = Devart.Common.Web.Identity.IdentityUser;
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
Imports ApplicationUser = Devart.Common.Web.Identity.IdentityUser
In the web.config file of your project, replace the default connection string with your PostgreSQL one.
<add name="DefaultConnection" connectionString="host=server;database=test;user id=postgres;" providerName="Devart.Data.PostgreSql" />
Extending ASP.NET Identity Functionality
The Using ADO.NET Implementation of ASP.NET Identity 1 for PostgreSQL tutorial contains an example with adding a new Email column to the AspNetUsers table. In ASP.NET Identity 2 the AspNetUsers table was significantly extended, and now it includes such Email column. However the approach to extending ASP.NET Identity functionality is the same, and you can take a look at this topic if you need to implement extended ASP.NET Identity 2 functionality.
Checking Application
Identity Database Script
CREATE TABLE "AspNetRoles" (
"Id" varchar(128) NOT NULL,
"Name" varchar(256) NOT NULL,
PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetUserRoles" (
"UserId" varchar(128) NOT NULL,
"RoleId" varchar(128) NOT NULL,
PRIMARY KEY ("UserId", "RoleId")
);
CREATE TABLE "AspNetUsers" (
"Id" varchar(128) NOT NULL,
"Email" varchar(256) NULL,
"EmailConfirmed" boolean NOT NULL,
"PasswordHash" varchar(256) NULL,
"SecurityStamp" varchar(256) NULL,
"PhoneNumber" varchar(256) NULL,
"PhoneNumberConfirmed" boolean NOT NULL,
"TwoFactorEnabled" boolean NOT NULL,
"LockoutEndDateUtc" timestamp without time zone NULL,
"LockoutEnabled" boolean NOT NULL,
"AccessFailedCount" int NOT NULL,
"UserName" varchar(256) NOT NULL,
PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetUserClaims" (
"Id" serial NOT NULL,
"UserId" varchar(128) NOT NULL,
"ClaimType" varchar(256) NULL,
"ClaimValue" varchar(256) NULL,
PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetUserLogins" (
"LoginProvider" varchar(128) NOT NULL,
"ProviderKey" varchar(128) NOT NULL,
"UserId" varchar(128) NOT NULL,
PRIMARY KEY ("LoginProvider", "ProviderKey", "UserId")
);
CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("Name");
CREATE INDEX "IX_AspNetUserRoles_UserId" ON "AspNetUserRoles" ("UserId");
CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");
CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("UserName");
CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");
CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");
ALTER TABLE "AspNetUserRoles"
ADD CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id")
ON DELETE CASCADE;
ALTER TABLE "AspNetUserRoles"
ADD CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id")
ON DELETE CASCADE;
ALTER TABLE "AspNetUserClaims"
ADD CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id")
ON DELETE CASCADE;
ALTER TABLE "AspNetUserLogins"
ADD CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id")
ON DELETE CASCADE;
See Also
Using Entity Framework Implementation of ASP.NET Identity 1 for PostgreSQL
| Using ADO.NET Implementation of ASP.NET Identity 1 for PostgreSQL
| Using Entity Framework Implementation of ASP.NET Identity 2 for PostgreSQL
| Using Entity Framework Core Implementation of ASP.NET Core Identity for PostgreSQL