Other topics (Performance, Security, …)

Scenario 1 - Top Selling Products and Restock Plan

Without indexes

The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used sequential scans on both orders and order_products.

Seq Scan on order_products
rows=75021

Seq Scan on orders
rows=5009
Rows Removed by Filter: 20008

The query was executed 10 times and the average execution time without indexes was: 25.646 ms

Indexes

CREATE INDEX idx_orders_status_purchase_date
ON project.orders (status, purchase_date, order_id);

CREATE INDEX idx_order_products_order_product_quantity
ON project.order_products (order_id, product_id, quantity);

ANALYZE project.orders;
ANALYZE project.order_products;

• The first index targets the filters on status and purchase_date, while also including order_id for the join.

• The second index was tested to support the join and aggregation on order_products.

With indexes

After indexing, PostgreSQL used:

Index Only Scan using idx_orders_status_purchase_date on orders
Heap Fetches: 0

This replaced the previous sequential scan on orders.

However, PostgreSQL did not use idx_order_products_order_product_quantity. It continued using:

Seq Scan on order_products

because scanning the table and performing a hash join was estimated to be cheaper.

The query was again executed 10 times and the average execution time with indexes was: 22.299 ms

Performance comparison and conclusion

Without indexes: 25.646 ms
With indexes:    22.299 ms
Improvement:     13.05%

• idx_orders_status_purchase_date was successfully used as an Index Only Scan and reduced the cost of filtering orders by status and purchase date.

• idx_order_products_order_product_quantity was not used by the optimizer, because a sequential scan of order_products was still considered cheaper for the current dataset.

Security

Cookie-based Authentication

For authentication in our application, we use ASP.NET Core cookie authentication.

After a user successfully logs in, the server creates an authentication cookie containing information about the authenticated user. This allows the application to recognize the user on subsequent requests without requiring them to log in again for every request.

Cookie authentication is configured in Program.cs:

builder.Services
    .AddAuthentication("Cookies")
    .AddCookie("Cookies", options =>
    {
        options.LoginPath = "/Account/Login";
        options.AccessDeniedPath = "/Account/Login";
        options.ExpireTimeSpan = TimeSpan.FromHours(8);
        options.SlidingExpiration = true;
    });

builder.Services.AddAuthorization();

The authentication cookie is valid for 8 hours. Sliding expiration is enabled, meaning the authentication period can be renewed while the user remains active.

After successful login, claims containing information about the user are created:

var claims = new List<Claim>
{
    new Claim(
        ClaimTypes.NameIdentifier,
        user.UserId.ToString()),

    new Claim(
        ClaimTypes.Name,
        user.Username),

    new Claim(
        ClaimTypes.Email,
        user.Email),

    new Claim(
        ClaimTypes.Role,
        role)
};

An identity and authentication principal are then created:

var identity = new ClaimsIdentity(
    claims,
    "Cookies");

var principal =
    new ClaimsPrincipal(identity);

await HttpContext.SignInAsync(
    "Cookies",
    principal);

The role claim allows us to distinguish between consumers and administrators and can be used to restrict access to specific functionality.

When the user logs out, the authentication cookie is invalidated:

await HttpContext.SignOutAsync("Cookies");

HttpContext.Session.Clear();

Password Storage

For password hashing, we use ASP.NET Core's PasswordHasher<User>:

builder.Services.AddScoped<
    IPasswordHasher<User>,
    PasswordHasher<User>>();

When a new user registers, their password is hashed before it is stored in the database:

user.Password =
    _passwordHasher.HashPassword(
        user,
        model.Password);

_context.Users.Add(user);
_context.SaveChanges();

Because password hashing is a one-way operation, the original password cannot be obtained from the stored value.

During login, we first retrieve the user by username:

var user = _context.Users
    .FirstOrDefault(x =>
        x.Username == model.Username);

The entered password is then verified against the stored password hash:

var result =
    _passwordHasher.VerifyHashedPassword(
        user,
        user.Password,
        model.Password);

if (result == PasswordVerificationResult.Failed)
{
    ModelState.AddModelError(
        "",
        "Invalid username or password.");

    return View(model);
}

This allows the application to verify a password without ever storing or comparing plaintext passwords in the database.

Protection Against CSRF

For POST requests that modify application data, ASP.NET Core anti-forgery protection is used.

Controller actions that receive POST requests are marked with:

[HttpPost]
[ValidateAntiForgeryToken]

For example:

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Register(RegisterViewModel model)
{
    // ...
}

The anti-forgery token protects the application against Cross-Site Request Forgery (CSRF) attacks by ensuring that the submitted request originates from a valid application form.

HTTPS and HSTS

The application redirects HTTP requests to HTTPS:

app.UseHttpsRedirection();

Additionally, outside the development environment, HTTP Strict Transport Security (HSTS) is enabled:

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    app.UseHsts();
}

HTTPS protects communication between the browser and the server by encrypting transmitted information, while HSTS instructs browsers to use HTTPS when communicating with the application.