SirChristian.net

The initial prototype of StockJock was a simple python web application (using Flask) with PostgreSQL backend. It was fun writing python for a while, however when development work really started to pick up it was decided to move to ASP.NET MVC and use C#. (I won’t get into why right now. If you really want details hit me up on Twitter.) We also wanted to keep the database in Postgres (again for details there is Twitter).

We also wanted to take advantage of the Universal Providers provided when using ASP.NET MVC out of the box. The problem was out of the box Simple Membership expected to connection to SqlServer with the “DefaultConnection” connection string. The data is all managed through EntityFramework. Luckily

ASP.NET SimpleMembership schema with PostgreSQL syntax

First step was to create a database in which to keep the membership data.

	CREATE DATABASE my_mvc_web_app;

Once we have a DB connect to the DB with a user who has permissions to create tables to create the schema. There are few particularities to pay attention to between Postgres & SQLServer.

• The default schema in Postgres is “public”, in SQLServer it’s “dbo”. There are ways to tell Entity framework which schema to use or the way we chose was to create a “dbo” schema inside our database.
• Postgres will treat any unquoted identifier as lowercase, where SQLServer is case agnostic with identifiers. The easiest way to keep EntityFramework from failing to find table was to quote our table identifiers to preserve case.

Here is the SQL we used to create our DB.

	CREATE SCHEMA IF NOT EXISTS dbo;

	CREATE TABLE IF NOT EXISTS dbo."AspNetUsers" (
	    "Id"                   VARCHAR   NOT NULL,
	    "Email"                VARCHAR   NULL,
	    "EmailConfirmed"       BOOLEAN   NOT NULL,
	    "PasswordHash"         VARCHAR   NULL,
	    "SecurityStamp"        VARCHAR   NULL,
	    "PhoneNumber"          VARCHAR   NULL,
	    "PhoneNumberConfirmed" BOOLEAN   NOT NULL,
	    "TwoFactorEnabled"     BOOLEAN   NOT NULL,
	    "LockoutEndDateUtc"    TIMESTAMP NULL,
	    "LockoutEnabled"       BOOLEAN   NOT NULL,
	    "AccessFailedCount"    INT       NOT NULL,
	    "UserName"             VARCHAR   NOT NULL,
	    CONSTRAINT PK_AspNetUsers PRIMARY KEY ("Id"),
		CONSTRAINT UQ_AspNetUsers UNIQUE ("UserName")
	);

	CREATE TABLE IF NOT EXISTS dbo."AspNetUserLogins" (
	    "LoginProvider" VARCHAR NOT NULL,
	    "ProviderKey"   VARCHAR NOT NULL,
	    "UserId"        VARCHAR NOT NULL,
	    CONSTRAINT PK_AspNetUserLogins PRIMARY KEY ("LoginProvider", "ProviderKey", "UserId"),
	    FOREIGN KEY ("UserId") REFERENCES dbo."AspNetUsers" ("Id") ON DELETE CASCADE
	);

	DO $$
	    BEGIN
	        CREATE INDEX IX_AspNetUserLogins_UserId ON dbo."AspNetUserLogins" ("UserId");
	    EXCEPTION
	        WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserLogins_UserId. Does it already exist?';
	    END
	$$;


	CREATE TABLE IF NOT EXISTS dbo."AspNetRoles" (
	    "Id"   VARCHAR NOT NULL,
	    "Name" VARCHAR NOT NULL,
	    CONSTRAINT PK_AspNetRoles  PRIMARY KEY ("Id"),
		CONSTRAINT UQ_RoleName UNIQUE ("Name")
	);

	CREATE TABLE IF NOT EXISTS dbo."AspNetUserClaims" (
	    "Id"         SERIAL,
	    "UserId"     VARCHAR NOT NULL,
	    "ClaimType"  VARCHAR NULL,
	    "ClaimValue" VARCHAR NULL,
	    CONSTRAINT PK_AspNetUserClaims PRIMARY KEY ("Id"),
	    FOREIGN KEY ("UserId") REFERENCES dbo."AspNetUsers"("Id") ON DELETE CASCADE
	);

	DO $$
	    BEGIN
	        CREATE INDEX IX_AspNetUserClaims_UserId ON dbo."AspNetUserClaims" ("UserId");
	    EXCEPTION
	        WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserClaims_UserId. Does it already exist?';
	    END
	$$;

	CREATE TABLE IF NOT EXISTS dbo."AspNetUserRoles" (
	    "UserId" VARCHAR NOT NULL,
	    "RoleId" VARCHAR NOT NULL,
	    CONSTRAINT PK_AspNetUserRoles PRIMARY KEY ("UserId", "RoleId"),
	    FOREIGN KEY ("RoleId") REFERENCES dbo."AspNetRoles"("Id") ON DELETE CASCADE,
	    FOREIGN KEY ("UserId") REFERENCES dbo."AspNetUsers"("Id") ON DELETE CASCADE
	);

	DO $$
	    BEGIN
	        CREATE INDEX IX_AspNetUserRoles_UserId ON dbo."AspNetUserRoles"("UserId");
	    EXCEPTION
	        WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserRoles_UserId. Does it already exist?';
	    END
	$$;


	DO $$
	    BEGIN
	        CREATE INDEX IX_AspNetUserRoles_RoleId ON dbo."AspNetUserRoles"("RoleId");
	    EXCEPTION
	        WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserRoles_RoleId. Does it already exist?';
	    END
	$$;

Hooking up EntityFramework with PostgreSQL

Now that we have a database we need our web application to communicate with it. The code the Microsoft provides for SimpleMembership uses EntityFramework. Francisco Figueiredo Jr., the creator of Npgsql project, has a great writeup on how to use npgsql with EntityFramework. I’d recommend following his blog post. The post summed up in bullet points:

• Install the Npgsql NuGet package
• Edit your web.config to hook in the Npgsql as DbProvider

  <System.data>
      <DbProviderFactories>
          <add name="Npgsql Data Provider" 
                  invariant="Npgsql"
                  description="Data Provider for PostgreSQL"
                  type="Npgsql.NpgsqlFactory, Npgsql" />
      </DbProviderFactories>
  </system.data>
  		

• Edit your web.config to set DefaultConnection to your DB

  <connectionStrings>
      <add name="DefaultConnection"
           providerName="Npgsql"
            connectionString="server=127.0.0.1;userid=yoursqluser;password=sqlpass;database=my_mvc_web_app"/>
  </connectionStrings>
  	

Using SimpleMembership

Now you are all set to start hooking in authentication into your web application. Following articles like this should be able to followed without modifications since the interaction with the database is all done through the EntityFramework just setup.