Monday, March 1, 2021

[PostgreSQL] Recursive CTE for hierarchy data

 

 PostgreSQL   CTE   Hierarchy

 

 

▌Problem


 

Assume that we have a table, Departments, which include hierarchy data like this,


 


 

and we would like to know the Agile dev team’s parent hierarchy, for example,

Agile dev team / Tiger department / IT

Here is a sample for using CTE(Common table expression) and RECURSIVE modifier to solve the problem.

 

 

▌Solution


 

▋Recursive CTE


WITH RECURSIVE cte_recursive AS (
        SELECT *
        FROM "Departments"
        WHERE "Id" = '001'
        UNION ALL
        SELECT t.*
        FROM "Departments" t
        INNER JOIN cte_recursive r ON t."Id" = r."Parent"
)
SELECT * FROM cte_recursive;


 



 

 

The recursive CTE results in:

 



 

 

 

▋Function (Optional)

 

We can create a function for advanced usage.


CREATE OR REPLACE FUNCTION public.find_hirearchy_departmemts(id text) RETURNS SETOF "Departments" AS 
$function$
BEGIN
    RETURN QUERY
    WITH RECURSIVE cte_recursive AS (
        SELECT *
        FROM "Departments"
        WHERE "Id" = id
        UNION ALL
        SELECT t.*
        FROM "Departments" t
        INNER JOIN cte_recursive r ON t."Id" = r."Parent"    
)
    SELECT * FROM cte_recursive;
END;
$function$ LANGUAGE plpgsql;

 

 

 

▌Reference


▋WITH Queries (Common Table Expressions)

▋PostgreSQL CTE




Friday, January 29, 2021

[Golang] Install and Get started with samples

  Golang   Get Stared    Install Golang   




 

▌Introduction


 

Go is an open source programming language that makes it easy to build simple, reliable, and efficient software.

See more details on golang.org.

 

Learn how to install Go, create a Hello world and some samples in the following links.


▋Install Go

 

1.  Install on Windows

2.  Install on Ubuntu

 

 

▋Hello World

 

This is the tutorial from official golang.org. 

I shorted it up in this article.



▋A TODO website

 

In this tutorial, we will use http package to create a TODO list website that can

  • List done and undone TODOs
  • Create new TODO
  • Remove a TODO

[.Net 5] Upgrade to NET 5 issues

 

 .NET 5   Upgrade issues   

 

 

▌Trouble shooting


 

 

▋The "RazorGenerate" task failed unexpectedly

 

After installing .NET 5 SDK, using Visual Studio 2019 (Version 16.8.3) on Windows to build .NET Core 2.2 project will get the error:

 

The "RazorGenerate" task failed unexpectedly

 

 

To resolve the issue, following the steps (Reference: https://stackoverflow.com/a/64834683/7045253 ).

 

1.  Set DOTNET_HOST_PATH environment variable.

$ setx DOTNET_HOST_PATH "%ProgramFiles%\dotnet\dotnet.exe"



2.  Delete C:\Program Files\dotnet\sdk\NuGetFallbackFolder.

3.  Restart your PC.

 

 

 

 

Saturday, January 23, 2021

[ASP.NET Core] Identity Server 4 - Get authorized user claims

   ASP.NET Core   JWT   Claims  






▌Introduction

 

Sometimes we want to get the user’s information by JWT before go inside the API controller/action.

For example, verify the JWT claim(s) or even overwrite the payload.

 

In this short article, we will create a custom Action Filter which inherits/implements Attribute and IAsyncActionFilter that can:

 

·         Get user claims.

·         Also: Get Http request’s request and modify it.

·         Also: Save custom items to HttpContext.

 

 

 

▋Related articles

 

01.      [OpenLDAP] Create an OpenLDAP container

02.      [ASP.NET Core] Identity Server 4 – Concepts

03.      [ASP.NET Core] Identity Server 4 – LDAP authentication

04.      [ASP.NET Core] Identity Server 4 – Secure Web API

05.      [ASP.NET Core] Identity Server 4 – Custom Event Sink

06.      [ASP.NET Core] Identity Server 4 – Refresh Token

07.      [ASP.NET Core] Identity Server 4 – Role based authorization

08.      [ASP.NET Core] Identity Server 4 – Policy based authorization

09.      [ASP.NET Core] Identity Server 4 - Dockerize

10.      [ASP.NET Core] Identity Server 4 – Client Credential

11.      [ASP.NET Core] Identity Server 4 – Policy based authorization with custom Authorization Handler

12.      [ASP.NET Core] Identity Server 4 – Signing credential

13.      [ASP.NET Core] Identity Server 4 – Authenticate by multiple LDAP

14.      [ASP.NET Core] Identity Server 4 – Cache and refresh Discovery document

15.      [ASP.NET Core] Identity Server 4 – PKCE Authorization Code Flow

16.      [ASP.NET Core] Identity Server 4 – PKCE Authorization Code Flow (Javascript client)

 .NET Core] Identity Server 4 – Get .NET Core] Identity Server 4 – Get authorized user claims

 [ASP.NET Core] Identity Server 4 – Get authorized user claims

[ASP.NET Core] Identity Server 4 – Get authorized use

 

▌Environment

 

▋Docker 18.05.0-ce

▋ASP.NET Core 3.1.300

▋IdentityServer4 3.1.2

▋IdentityModel 3.10.10

 

 

 

▌Implement

 

The source code is on my Github.

 

▋Create User Profile filter

 

There are two ways to get the user’s claims:

 

1.  Use System.IdentityModel.Tokens.Jwt.JwtSecurityTokenHandler to decode the JWT.

2.  Use the System.Security.ClaimsPrinciple from HttpContext.

 

▋UserProfileFilter


public class UserProfileFilter : Attribute, IAsyncActionFilter
{
    public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
    {
        string uid = string.Empty;
        string email = string.Empty;
        StringValues authHeaderVal = default(StringValues);

        // Method 1. Use System.IdentityModel.Tokens.Jwt.JwtSecurityTokenHandler to decode the JWT.
        if (context.HttpContext.Request.Headers.TryGetValue("Authorization", out authHeaderVal))
        {
            string bearerTokenPrefix = "Bearer";
            string accessToken = string.Empty;
            string authHeaderStr = authHeaderVal.ToString();
            if (!string.IsNullOrEmpty(authHeaderStr) && authHeaderStr.StartsWith(bearerTokenPrefix, StringComparison.OrdinalIgnoreCase))
            {
                accessToken = authHeaderStr.Replace(bearerTokenPrefix, string.Empty, StringComparison.OrdinalIgnoreCase).Trim();
            }

            var handler = new System.IdentityModel.Tokens.Jwt.JwtSecurityTokenHandler();
            var token = handler.ReadJwtToken(accessToken);
            uid = token.Claims.FirstOrDefault(c => c.Type.Equals("sub", StringComparison.OrdinalIgnoreCase))?.Value;
            email = token.Claims.FirstOrDefault(c => c.Type.Equals(JwtClaimTypes.Email))?.Value;
        }

        // 2. Use the System.Security.ClaimsPrinciple from HttpContext.
        var user = context.HttpContext.User;
        if (user.Identity.IsAuthenticated)
        {
            uid = user.Claims.FirstOrDefault(c => c.Type.Equals("sub", StringComparison.OrdinalIgnoreCase))?.Value;
            email = user.Claims.FirstOrDefault(c => c.Type.Equals(JwtClaimTypes.Email))?.Value;
        }
        
        await next();
    }
}



The above sample code shows how to get user’s sub and email claims.

 

 

▋Use it in Controller/Action

 


 [HttpGet("UserProfile")]
 [Authorize]
 [TypeFilter(typeof(UserProfileFilter))]
 public async Task<IActionResult> UserProfile()
 {
     return this.Ok();
 }

 



▋Also: Get request’s payload and modify it

 

We can use ActionArguments to get the payload object of a Http request and modify it before the payload get into the Controller’s Action.

 

▋UserProfileFilter

 

 public class UserProfileFilter : Attribute, IAsyncActionFilter
 {
    public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
    {
            // ...skip

            MyPayload payload = (MyPayload)context.ActionArguments?.Values.FirstOrDefault(v => v is RequestDto);
            payload.Uid = uid;
            
            await next();
    }
 }

 




▋Also: Save custom items to HttpContext

 

Once we what to keep some information on the current request, we can easily save them as HttpContext items and get them later inside Controller’s Action.

 

▋UserProfileFilter


public class UserProfileFilter : Attribute, IAsyncActionFilter    
{
    public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
    {
        // ...skip
        string itemName = "UserProfile";
        context.HttpContext.Items.Add(itemName, new { Id = uid, Email = email });
            
        await next();
    }
 }

 

 

Now we can get the items as following,

 

▋Controller


[HttpGet("UserProfile")]
 [TypeFilter(typeof(UserProfileFilter))]
 public async Task<IActionResult> UserProfile()
 {
     if (this.HttpContext.Items.TryGetValue("UserProfile", out object userProfile))
     {
         this.logger.LogInformation(JsonConvert.SerializeObject(userProfile));
     }
     
return this.Ok();
 }



  

▋Source Code

 

Github: KarateJB/AspNetCore.IdentityServer4.Sample

 

 

 

▌Reference

 

▋Overwrite request object in ASP .NET Core