Web.API authentication with PostgreSQL walkthrough
Authentication and authorisation is part of every REST API, regardless of whether if API is part of internal network or if it is exposed publicly. There are many types of authentication and each of them has their use cases which will not be covered in this post. We will go through creating secure web api using .NET technologies and PostgreSQL database.
This walkthrough will try to cover in details every step. If anything is unclear or missing, drop me a message or comment. Fully working code for following walkthrough can be found on GitHub > https://github.com/kolotree/WebApiWithPostrgres
Auto generate part
Creating Web API project in Visual Studio is pretty much straightforward. After opening New Project Wizard, choose ASP .NET Web Application and select the name of your solution and project. After that, you will see a window for choosing type of web application that you want to create (Web Forms, Single Page application, MVC, Web API…).
Choose Web API and open Change Authentication dialog.
There you will find option to add various authentication types to the project. One of the options is Individual User Account which stores information about user into LocalDB and which we will use as a starting point in our project also.
After creating project with these settings, your authentication mechanism is set. It will use LocalDB for storing information about registered users and everything around generating and retrieving token, invalidating token, changing password etc. is available out of the box. Now let’s see how to use PostgreSQL for our authentication mechanism.
Using Postgresql as a database
PostgreSQL is a powerful, cross platform open source relational database. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures. And maybe the best part for startup projects/companies is price – both open source and proprietary use is 0.00€. Although it is more than decent database, I had a chance to work with it only on one project and I wasn’t disappointed. Here are the first steps of integration with PostgreSQL.
There are multiple libraries for connecting .NET app with PostgreSQL. Npgsql is one of them and it is the one that I used. It is an open source ADO.NET Data Provider for PostgreSQL, fully implemented in C#. It has large community on gitter that is able to help you if you get stuck somewhere.
Let’s get on to the code. Install Npgsql with NuGet Package manager and we are ready to go and adapt our code base. First thing that needs to be changed is the
IdentityConfig. We will change existing
IdentityConfig with our custom
UserStoreService so that we can create our own database handler.
UserStoreService must implement interface
IUserStore and its CRUD methods, like this:
Possible factory for creating our
UserService can be something simple, like this:
UserService to be dispatching service inside Service Layer. It contains only call to the repository layer, which holds integration with database.
Here is what the implementation of that Repository layer looks like:
First, we need to open connection.
_connection is a readonly property inside our parent class
Connection string is defined in
Web.config file like this:
After that, we created Npgsql command entity which can represent regular (ad hoc) query or stored procedure. As you can see we are using it as a regular query with two parameters,
PasswordHash. Web API framework will create PasswordHash for us. It is created from plain text password that is submitted to the api/Account/Register endpoint. Passwordhash also contains Password Salt, so we don’t need to worry about that too. Here you can check how password hashing is implemented in ASP.Net Core.
After that we are closing connection in the finally block and returning result to the caller.
Important! Please note the quotes around table name and column names. Npgsql will automatically lower case table and column names unless they are double-quoted. Another option is to have lower case names for tables and columns in the database.
Above code will compile, but you will get runtime error if you try to register account. Error will occur because our custom store (
UserStoreService) does not implement
IUserPasswordStore which is necessary for getting and setting
Let’s get back to our custom
UserStoreService and create as simple as possible implementation for get and set
We are returning null if password cannot be found for input user. That will cause error on client’s side which will inform the user that password or user name is incorrect. Setting password can be performed only on existing user and password cannot be
Repository implementation for getting
PasswordHash from database can look like this:
Next thing that has to be done is implementing .NET
UserStoreService and we will implementing
And of course, before we can use PostgreSQL, we need to create table in our database where we will store User information.
As I already mentioned, be aware of capitalization of table and column names, since it can cause a lot of trouble without any meaningful error message.
Now we have working code that you can run and see how the User table gets populated after each User registration. After registration, you can retrieve token with User’s password and username/email address. After that, token is used to access any other secured endpoint of your application.
So, what is the thing to do after writing code and testing (or writing tests first and then writing code if you use TDD)? Refactoring of course. On the above code, we can immediately notice 3 improvement points:
- Introducing stored procedures instead of query
- Using block instead of manually closing connection
- Usage of functional extensions
Introducing stored procedures instead of queries
What are the main advantages of stored procedures over ad hoc query in the code? Well, there are a lot of pros and cons to use stored procedures over ad hoc query in the code. Some of them are:
- maintenance of the code is easier than maintenance of the stored procedures (unless you are DBA)
- migrating to different database is less painful
Cons/Pros – can’t decide if this is good or bad 🙂
- there is no chance that you can intentionally or unintentionally put business logic in the database.
- optimized performances, which is significant if you are running same query/stored procedure with different parameters
- consistency in result, from whichever application that you are calling the SP
- it is not necessary to know relationships of underlying tables
- encapsulation of tables and views. You can grant permissions to execute the stored procedures (with security definer), but deny permissions on the underlying tables
- redesign of database architecture will not result in any application changes as long as the API (stored procedure call) remains the same
So let’s add stored procedure for creating and searching user in our DB and refactor the code to call that stored procedure instead of using ad hoc query.
UserRepository.cs we will replace existing code with the following one:
Notice that we don’t need to think about letter case of procedure name. It can be upper, lower, camel or whatever you like, independent of the letter case used in database.
CreateStringParam is extracted method that can be used whenever we need to pass parameter to some stored procedure.
“Using” block instead of manually closing connection
One additional improvement is avoiding manually closing database connection and encapsulating code in
using block and getting rid of
finally block instead.
GetNpgsqlConnection() will create
NpgsqlConnection so that we can have connection string on one place:
SearchUser stored procedure returns whole user entity, we will change
GetPasswordHash method to extract only
Usage of functional extensions
Since well-structured and more readable software becomes more and more important, especially in large, enterprise projects, functional programming is considered to be mainstream paradigm that will help us achieve this. Removing boiler plate code and switching from OOP to functional programming paradigm can be tricky at first in readability sense, but once you get used to it, you will finally understand its full power.
Although you can create your own library for functional extensions, we will use CSharpFunctionalExtensions by Vladimir Khorikov. There is a great Pluralsight course from Khorikov if you want to learn more.
So, let’s start from repository. UserRepository methods will return Result instead of string and Task. Result will contain outcome of database querying, regardless of whether outcome is a success or a failure.
Create method we will change only catch block and return value
Of course, return value changes need to be applied to the interfaces and service layer. In
UserStoreService we can now handle results from underlying calls in the following way:
Using PostgreSQL as a database for Web API authentication is fairly easy and straightforward. The key step was to introduce custom UserStore in IdentityConfig.cs and to handle creating and searching User in our own way. There is also an option to use your custom User entity in which case you would extend auto generated ApplicationUser and replaced it with your own User entity. You can find fully working code for Web API authentication with PostgreSQL on GitHub > https://github.com/kolotree/WebApiWithPostrgres