DHI.Services.PostgreSQL for Security — Internal Developer Guide¶

This provider stores Security data in PostgreSQL using thin repositories. It auto-creates tables on first use and gives you direct CRUD over Accounts, User Groups, Refresh Tokens, and Password History.

Foundational helpers (base repo, JSON converters, connection-string “Table=” override, etc.) are in PostgreSQL Providers.

What’s in this package¶

AccountRepository — Accounts table (credentials, profile, lockout/enablement, metadata).
UserGroupRepository — User groups + membership list (text array).
RefreshTokenRepository — Long-lived refresh tokens per account.
PasswordHistoryRepository — Rotating/expired passwords per account.

Each repository:

Ensures its table exists (and indexes) at startup.
Uses Npgsql and simple SQL (no ORM).
Serializes Metadata dictionaries as JSON strings (VARCHAR).

Table schemas¶

The provider will create these tables automatically if missing and add some columns at runtime (see “Automatic provisioning & seeding”).

1) `public.accounts`¶

CREATE TABLE IF NOT EXISTS public.accounts (
  id                               varchar(255)  NOT NULL,
  name                             varchar(255)  NOT NULL,
  encryptedpassword                bytea         NOT NULL,
  activated                        boolean       NOT NULL,
  token                            varchar(255),
  tokenexpiration                  timestamp without time zone,
  company                          varchar(255),
  email                            varchar(255),
  phonenumber                      varchar(255),
  allowmepasswordchange            boolean       NOT NULL,
  enabled                          boolean       NOT NULL,
  noofunsuccessfulloginattempts    int           NOT NULL,
  lastloginattempteddate           timestamp with time zone,
  locked                           boolean       NOT NULL,
  lockeddateend                    timestamp with time zone,
  roles                            varchar(255),
  metadata                         varchar(2048),
  CONSTRAINT accounts_pk PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS accounts_index ON public.accounts USING btree (name DESC);

Notes

encryptedpassword stores either:
- legacy SHA-1 hash bytes (20 bytes), or
- PBKDF2 (Rfc2898): 16-byte salt + 20-byte hash (see “Password hashing”).
metadata is serialized JSON (string, up to 2048 chars) — not JSONB.
Case-insensitive lookup on id/email is done with LOWER(...).

2) `public.usergroups`¶

CREATE TABLE IF NOT EXISTS public.usergroups (
  id        varchar(255)  NOT NULL,
  name      varchar(255)  NOT NULL,
  users     text[],               -- membership, holds account IDs
  metadata  varchar(2048),
  CONSTRAINT usergroups_pk PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS usergroups_index ON public.usergroups USING btree (id DESC);

3) `public.refreshtokens`¶

CREATE TABLE IF NOT EXISTS public.refreshtokens (
  id         varchar(255)  NOT NULL,
  token      varchar(255)  NOT NULL,
  accountid  varchar(255)  NOT NULL,
  expiration timestamp without time zone NOT NULL,
  clientip   varchar(255),
  CONSTRAINT refreshtokens_pk PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS refreshtokens_index ON public.refreshtokens USING btree (expiration DESC);

4) `public.passwordhistory`¶

CREATE TABLE IF NOT EXISTS public.passwordhistory (
  id                 varchar(255)  NOT NULL,
  accountid          varchar(255)  NOT NULL,
  encryptedpassword  bytea         NOT NULL,
  passwordexpirydate timestamp without time zone NOT NULL,
  CONSTRAINT passwordhistory_pk PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS passwordhistory_index ON public.passwordhistory USING btree (accountid);

Automatic provisioning & seeding¶

On first run:
- Each repository ensures its table exists (AssureTableExists → CreateDataModel).
AccountRepository:
- If table empty, seeds a demo admin user:
  - id = "demo_admin_to_be_deleted", name = "Demo Admin - TO BE DELETED", password = "webapi", activated = true.
- Calls ExtendDataModel() to add missing modern columns:
  - enabled boolean DEFAULT true NOT NULL
  - noofunsuccessfulloginattempts int DEFAULT 0 NOT NULL
  - lastloginattempteddate timestamptz DEFAULT now() NOT NULL
  - locked boolean DEFAULT false NOT NULL
  - lockeddateend timestamptz DEFAULT NULL
UserGroupRepository:
- If empty, seeds:
  - Administrators (members: demo_admin_to_be_deleted)
  - Editors (members: demo_admin_to_be_deleted)

Action required: In any non-test environment, delete or replace the seed user and membership immediately.

Password hashing & validation¶

AccountRepository.ValidatePassword(accountId, password):

If EncryptedPassword.Length == 20 → legacy SHA-1 (kept only for backward compatibility).
Else → PBKDF2 (Rfc2898):
- Salt: first 16 bytes of EncryptedPassword
- Iterations: 10000
- Hash: next 20 bytes
- Compares derived 20-byte hash to stored hash.

New passwords should always be stored as PBKDF2 (salt+hash in bytea).

Repository APIs (what you call)¶

`AccountRepository` (PostgreSQL)¶

CRUD & queries
- Get(id) / GetAll() / Contains(id) / Add(account) / Update(account) / Remove(id)
- GetByEmail(email) — case-insensitive
- GetByToken(token) — exact match
- Count()
Auth / validation
- ValidatePassword(accountId, password) — validates against stored encryptedpassword
Lockout helpers (write & save the account)
- LockAccount(account, lockPeriod) — sets Locked=true, LockedDateEnd=now+period, updates LastLoginAttemptedDate
- ResetAccount(account, resetValue) — sets NoOfUnsuccessfulLoginAttempts=resetValue, updates LastLoginAttemptedDate
- UnlockAccount(account) — clears Locked, LockedDateEnd, resets attempts, updates LastLoginAttemptedDate

Behavioral details

Most lookups use case-insensitive LOWER(id) / LOWER(email).
Metadata is round-tripped via System.Text.Json with DS converters.
GetAll() yields rows ordered by id.

`UserGroupRepository`¶

Add, Update, Remove
Get(id), GetAll(), GetIds(), Contains(id), Count()
users maps to text[] (account IDs). We write the whole array for updates.

`RefreshTokenRepository`¶

Add, Update, Remove
GetByToken(token) → Maybe<RefreshToken>
GetByAccount(accountId) → enumerable
Index on expiration for cleanup/lookup.

`PasswordHistoryRepository`¶

Add, Update, Remove
GetByAccountId(accountId) (all, DESC by PasswordExpiryDate)
GetMostRecentByAccountId(accountId) (LIMIT 1)
GetRecentByAccountId(accountId, limit)

Usage snippets¶

Create repositories¶

var cs = "Host=...;Port=5432;Database=security;Username=...;Password=...;SSL Mode=Require;";

// Accounts
var accounts = new DHI.Services.Provider.PostgreSQL.AccountRepository(cs, logger);

// Groups
var groups = new DHI.Services.Provider.PostgreSQL.UserGroupRepository(cs, logger);

// Refresh tokens
var refreshTokens = new DHI.Services.Provider.PostgreSQL.RefreshTokenRepository(cs, logger);

// Password history
var pwdHistory = new DHI.Services.Provider.PostgreSQL.PasswordHistoryRepository(cs, logger);

Add an account¶

var acc = new Account("jdoe", "John Doe")
{
  Email = "jdoe@example.com",
  Company = "ACME",
  AllowMePasswordChange = true,
  Activated = true,
  Enabled = true
};
acc.SetPassword("S3cure!passw0rd"); // sets EncryptedPassword

accounts.Add(acc);

Validate a password¶

var ok = await accounts.ValidatePassword("jdoe", "S3cure!passw0rd");

Lock/unlock¶

var maybe = accounts.Get("jdoe");
if (maybe.HasValue)
{
  var a = maybe.Value;
  accounts.LockAccount(a, TimeSpan.FromMinutes(15));
  // ...
  accounts.UnlockAccount(a);
}

Add a group & members¶

var ug = new UserGroup("Operators", "Ops team", new HashSet<string>{ "jdoe", "alice" });
groups.Add(ug);

Refresh tokens¶

var rt = refreshTokens.GetByToken(refreshTokenString);
if (rt.HasValue)
{
  // exchange/update your token then persist
  var entity = rt.Value;
  entity.Expiration = DateTime.UtcNow.AddDays(90);
  refreshTokens.Update(entity);
}

Operational guidance¶

Security
- Use TLS to the DB; store the connection string in a secret store.
- Grant least privilege to the application role (CRUD on these tables only).
Capacity
- metadata capped at 2048 chars. Keep custom claims/config small.
Time zones
- lastloginattempteddate uses timestamptz; other timestamps are without TZ. Store UTC and convert in app/UI.
Case-insensitivity
- Server enforces LOWER(id)/LOWER(email) on lookups; treat IDs/emails case-insensitive in your code too.
Indexes
- Accounts: name (desc)
- UserGroups: id (desc)
- RefreshTokens: expiration (desc)
- PasswordHistory: accountid

Tips¶

Seed data: remove demo_admin_to_be_deleted and strip it from Administrators after first start.
Metadata type: stored as VARCHAR JSON. If you need querying by metadata keys, consider a migration to JSONB; otherwise keep metadata small and client-interpreted.
Legacy password hashes: accounts created long ago may still use 20-byte SHA-1. They’ll validate, but when users change password they should be upgraded to PBKDF2.
Extending schema: ExtendDataModel() adds lockout columns if missing. Keep it enabled until all environments are migrated.
Performance: GetByEmail/GetByToken scan the table; add optional indexes if these lookups are frequent in your workload.

Where the base behaviors live¶

All cross-cutting PostgreSQL provider concerns (base repository, JSON converter setup, connection string “Table=” override, etc.) are documented in PostgreSQL Providers.