My context: I need creating an web-app use ASP.NET Core WebAPI .NET 6, Entity Framework Core 6, PostgreSQL 14.5 with JWT authentication/authorization. I mimics a sample at https://github.com/patrickgod/AuthenticationWebApi , then fork to me https://github.com/donhuvy/AuthenticationWebApi .
This source code use .NET 6 , SQLite , Entity Framework Core 6, HMACSHA512 :
namespace AuthenticationWebApi.Models
{
public class User
{
public int Id { get; set; }
public string Username { get; set; } = string.Empty;
public byte[] PasswordHash { get; set; } = new byte[32]; // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
public byte[] PasswordSalt { get; set; } = new byte[32]; // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
public string RefreshToken { get; set; } = string.Empty;
public DateTime TokenCreated { get; set; } // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
public DateTime TokenExpires { get; set; } // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
public string Role { get; set; } = string.Empty;
}
}
source https://github.com/donhuvy/AuthenticationWebApi/blob/master/AuthenticationWebApi/Models/User.cs
I need migration to PostgreSQL 14.6 . My wrong SQL script is
CREATE TABLE public."my_user"
(
id integer,
username character varying(32),
password_hash character varying(32), // <-- Incorrect mapping.
password_salt character varying(32), // <-- Incorrect mapping.
refresh_token character varying(32),
token_created time with time zone, // <-- Incorrect mapping.
token_expires time with time zone, // <-- Incorrect mapping.
role text
);
ALTER TABLE IF EXISTS public."my_user"
OWNER to postgres;
I use table name my_user for avoiding duplicate with reserved keyword user in PostgreSQL database system.
Help me mapping correct datatype for SQL script inside PostgreSQL. I hope you understand my need (you can ask me for clarification).