Row-level security and audit context in FunkyORM

If your app talks to the database as one service account, FunkyORM can still put the real user on the connection the command uses. What you are installing is FunkyORM, a .NET library and micro-ORM that maps plain C# classes to SQL Server, PostgreSQL, MySQL, and SQLite. The audit-context beta is Funcular.Data.Orm 3.8.0-beta1, and that is the build that stamps the person onto the session the command actually uses.

Introducing Funcular ORM is the class-to-table walkthrough; source, issues, and release tags sit on GitHub.

Why the service account is not enough

A lot of apps authenticate to SQL Server or PostgreSQL as one identity: a managed identity (the cloud login the process uses, not the person at the keyboard), a pool login, or a single service account, and that is the right operational shape because you do not mint a database login per user. It is the wrong shape for policies that filter rows by user, and for audit columns that should say who did the write, because row-level security (RLS: the database hides rows the current user is not allowed to see) then sees the service account on every command. You primed that session state yourself before 3.8.0-beta1, and it was easy to do it on a connection the next command never used, so the policy read empty keys and the audit column named the pool login.

How you opt in

You opt in through AuditContextOptions, and the default is off, so leave it unset and the provider sends the same SQL it sent before, with no extra session keys on the connection the command uses. Your app implements IAuditContextAccessor over its own AsyncLocal (a value that follows the async call, not a static that leaks across requests), and FunkyORM does not care what the keys mean. It sets what you hand it onto the scope’s own connection, once, before the command runs, so the row-level security policy and the audit column read the same session the insert or select is about to use. Immutable read_only keys are never re-set on that connection, which is what you want when a pooled connection is reused and a later request must not overwrite a key the first command already sealed. RequireAuditContext is fail-closed if you want it: when the accessor has nothing for this request, the command does not run, instead of going out as the service account and looking like a successful write.

What each engine does with the keys

SQL Server and PostgreSQL can use those keys for row-level security and for attribution, so a policy can hide rows the current user should not see and an audit column can name the person who issued the write. MySQL gets attribution only: you can stamp who did the work, but there is no native row-level security on MySQL for these keys to feed, so a SELECT still returns every row the service account can read. SQLite is a no-op on a default config, writing nothing into the file database’s session; a strict config throws, so a test that expected session keys fails in the open instead of pretending the file filtered anything. PostgreSQL keys must be dot-namespaced (a dotted name, not a bare word), and they pass through verbatim and error otherwise, because set_config is not a dumping ground for bare names. If you send a bare name, PostgreSQL rejects the SET rather than FunkyORM inventing a namespace for you.

The mapper around this feature is already stable; the audit-context API is the beta, and it may still move.

Funcular.Data.Orm 3.8.0-beta1 is the audit-context drop, with no 3.8.0 final yet; when a key you set never shows up in the session the policy reads, the v3.8.0-beta1 tag is the place to report it.