The PostgreSQL Provider enables storage of Audit.NET events in PostgreSQL database tables. This provider serializes audit events to JSON and stores them alongside optional custom columns for efficient querying and indexing.
For information about other SQL providers, see SQL Server Provider (3.2.1) and MySQL Provider (3.2.3). For the general data provider architecture, see Data Provider Architecture (3.1).
The PostgreSQL Provider stores audit events in PostgreSQL tables with flexible schema configuration. It supports:
The provider is implemented in the Audit.NET.PostgreSql package and uses the Npgsql driver for PostgreSQL connectivity.
Sources: src/Audit.NET.PostgreSql/README.md1-4 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs10-23
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs24 src/Audit.NET.PostgreSql/ConfigurationApi/IPostgreSqlProviderConfigurator.cs9 src/Audit.NET.PostgreSql/ConfigurationApi/PostgreSqlProviderConfigurator.cs7
Sources: src/Audit.NET.PostgreSql/README.md24-36 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs26-65 src/Audit.NET.PostgreSql/ConfigurationApi/PostgreSqlProviderConfigurator.cs9-18
The recommended configuration approach uses the fluent API via UsePostgreSql():
Sources: src/Audit.NET.PostgreSql/README.md26-36 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs75-80
Alternatively, configure the provider directly by instantiating PostgreSqlDataProvider:
Sources: src/Audit.NET.PostgreSql/README.md38-54 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs29-41
| Property | Type | Default | Description |
|---|---|---|---|
ConnectionString | Setting<string> | Required | PostgreSQL connection string |
TableName | Setting<string> | "event" | Target table name |
IdColumnName | Setting<string> | "id" | Primary key column name |
DataJsonColumnName | Setting<string> | null | JSON data column name (optional) |
DataJsonType | string | "JSON" | JSON column type: "JSON", "JSONB", or "String" |
LastUpdatedDateColumnName | Setting<string> | null | Timestamp column for updates |
Schema | Setting<string> | null | PostgreSQL schema name |
CustomColumns | List<CustomColumn> | Empty | Additional indexed columns |
DataJsonStringBuilder | Func<AuditEvent, string> | ev => ev.ToJson() | Custom JSON serialization |
Sources: src/Audit.NET.PostgreSql/README.md56-71 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs26-65
The provider supports three data types for storing audit event JSON:
Standard PostgreSQL JSON type. Stores JSON text and validates syntax on insert:
Binary JSON format with indexing support and efficient querying:
This is the recommended type for production use due to query performance benefits.
Stores JSON as plain TEXT without PostgreSQL JSON validation:
Sources: src/Audit.NET.PostgreSql/README.md68-69 src/Audit.NET.PostgreSql/ConfigurationApi/IPostgreSqlProviderConfigurator.cs44-56 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs460
The DataJsonColumnName can be set to null to skip JSON storage entirely, storing only custom columns:
When the JSON column is omitted, GetEvent(), GetEventAsync(), and EnumerateEvents() methods return null or empty results.
Sources: src/Audit.NET.PostgreSql/README.md65-68 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs73-109 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs131-134
Custom columns extract specific properties from audit events for indexing and efficient querying.
The CustomColumn class stores a column name and a function that extracts the value from an AuditEvent:
Sources: src/Audit.NET.PostgreSql/README.md34-35 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs462-464
The provider generates INSERT and UPDATE statements that include custom columns:
INSERT statement pattern:
UPDATE statement pattern:
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs259-266 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs277-283 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs298-305
PostgreSQL schemas enable logical separation of audit tables:
This stores events in the audit.events table. The schema is optional and defaults to the database's default schema (typically public).
The GetSchema() method formats the schema name with proper quoting:
Sources: src/Audit.NET.PostgreSql/README.md64 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs403-407
Sources: src/Audit.NET.PostgreSql/README.md72-94 src/Audit.NET.PostgreSql/SqlScript.sql1-16 test/Audit.PostgreSql.UnitTest/SqlScript.sql1-16
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs89-117 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs250-283
The provider implements retrieval and enumeration methods:
| Method | Description | Returns |
|---|---|---|
GetEvent<T>(eventId) | Retrieve single event by ID | T : AuditEvent or null |
GetEventAsync<T>(eventId) | Async retrieve single event | Task<T> |
EnumerateEvents(whereExpression) | Query events with WHERE clause | IEnumerable<AuditEvent> |
EnumerateEvents<T>(where, orderBy, limit) | Advanced query with sorting | IEnumerable<T> |
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs129-165 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs177-227 src/Audit.NET.PostgreSql/README.md96-119
The EnumerateEvents() method accepts PostgreSQL WHERE expressions:
This uses PostgreSQL's JSON path operators to query JSONB data. The query becomes:
Sources: src/Audit.NET.PostgreSql/README.md109-115 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs177-201
This generates:
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs215-227 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs437-450
PostgreSQL JSONB supports powerful query operators:
| Operator | Description | Example |
|---|---|---|
-> | Get JSON object field | data -> 'EventType' |
->> | Get JSON object field as text | data ->> 'EventType' |
#> | Get JSON object at path | data #> '{Environment,UserName}' |
#>> | Get JSON object at path as text | data #>> '{Environment,UserName}' |
? | Does key exist? | data ? 'CustomerId' |
@> | Does left contain right? | data @> '{"EventType":"test"}' |
For JSONB querying details, see the PostgreSQL JSON documentation linked in the comments.
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs175-176 src/Audit.NET.PostgreSql/README.md116-119
When DataJsonColumnName is null, the query methods return empty results:
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs131-146 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs194-197 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs112-137
The GetDbConnection() method provides direct access to the underlying NpgsqlConnection for advanced scenarios:
Usage example:
This enables integration with Npgsql-specific features, Entity Framework Core PostgreSQL provider, or Dapper.
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs124-127 src/Audit.NET.PostgreSql/README.md123-128
The package targets multiple frameworks with different Npgsql versions:
| Target Framework | Npgsql Version |
|---|---|
| net462 | 8.0.3 |
| netstandard2.0 | 8.0.3 |
| netstandard2.1 | 8.0.3 |
| net6.0 | 9.0.2 |
| net8.0 | 10.0.1 |
This ensures compatibility with both legacy .NET Framework applications and modern .NET applications while using the latest Npgsql features for each platform.
Sources: src/Audit.NET.PostgreSql/Audit.NET.PostgreSql.csproj8 src/Audit.NET.PostgreSql/Audit.NET.PostgreSql.csproj38-48
The provider uses parameterized queries to prevent SQL injection:
Custom column parameters are named @c0, @c1, @c2, etc. Null values are converted to DBNull.Value.
Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs356-371 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs373-389
The provider generates type casting for JSON columns based on DataJsonType:
This produces:
@data for STRING typeCAST (@data AS JSON) for JSON typeCAST (@data AS JSONB) for JSONB typeSources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs391-396
The PostgreSQL provider includes comprehensive integration tests that run against a containerized PostgreSQL instance in the CI/CD pipeline.
Tests are marked with NUnit categories for selective execution:
| Test Category | Coverage |
|---|---|
| Configuration | Fluent API, direct configuration, extension methods |
| JSON Storage | JSON, JSONB, String types, null JSON column |
| Custom Columns | Insert, update, null values |
| Query Operations | GetEvent, EnumerateEvents with WHERE/ORDER BY/LIMIT |
| Replace Operations | Sync and async ReplaceEvent |
| Edge Cases | Missing events, empty queries, null data columns |
Sources: test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs15-18 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs27-109 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs453-600
Refresh this wiki