While logging with Serilog, I cannot add users to the column I created in Postgresql

Question

I am creating a custom column via LoggerConfiguration below. Then, using the middleware and the UsernameColumnWriter class I wrote, I want to print the name of the logged in user on Postgresql when the request is made, in this column. The table and column are forming without any problems. However, when I make a request, the user_name column remains empty, although log records are created.

Serilog configurations:

Logger log = new LoggerConfiguration()
.WriteTo.PostgreSQL(builder.Configuration.GetConnectionString("ConnectionString"),
    "Logs",
    needAutoCreateTable: true,
    columnOptions: new Dictionary<string, ColumnWriterBase>
    {
        {"message", new RenderedMessageColumnWriter(NpgsqlDbType.Text)},
        {"message_template", new MessageTemplateColumnWriter(NpgsqlDbType.Text)},
        {"level", new LevelColumnWriter(true , NpgsqlDbType.Varchar)},
        {"time_stamp", new TimestampColumnWriter(NpgsqlDbType.Timestamp)},
        {"exception", new ExceptionColumnWriter(NpgsqlDbType.Text)},
        {"log_event", new LogEventSerializedColumnWriter(NpgsqlDbType.Json)},
        {"user_name", new UsernameColumnWriter()}
    })
.WriteTo.Debug()
.WriteTo.Console()
.MinimumLevel.Information()
.CreateLogger();

builder.Host.UseSerilog(log);

Middleware:

app.UseSerilogRequestLogging();
app.UseHttpLogging();

app.UseAuthentication();
app.UseAuthorization();

app.Use(async (context, next) => {
    var username = context.User?.Identity?.IsAuthenticated != null || true ? 
        context.User.Identity.Name : null;
    LogContext.PushProperty("user_name", username);
    await next();
});

My Class:

    public class UsernameColumnWriter : ColumnWriterBase {
        public UsernameColumnWriter() : base(NpgsqlDbType.Varchar) {
        }

        public override object GetValue(LogEvent logEvent, IFormatProvider formatProvider = null) {
        var (username, value) = logEvent.Properties.FirstOrDefault(p => p.Key == "user_name");
        return value?.ToString() ?? null;
    }
}

Answer 1

You want to use properties from LogContext but your configuration misses the required section :

...
.Enrich.FromLogContext()
...

So your final configuration should look like the below :

Logger log = new LoggerConfiguration()
.WriteTo.PostgreSQL(builder.Configuration.GetConnectionString("3pmConnectionString"),
    "Logs",
    needAutoCreateTable: true,
    columnOptions: new Dictionary<string, ColumnWriterBase>
    {
        {"message", new RenderedMessageColumnWriter(NpgsqlDbType.Text)},
        {"message_template", new MessageTemplateColumnWriter(NpgsqlDbType.Text)},
        {"level", new LevelColumnWriter(true , NpgsqlDbType.Varchar)},
        {"time_stamp", new TimestampColumnWriter(NpgsqlDbType.Timestamp)},
        {"exception", new ExceptionColumnWriter(NpgsqlDbType.Text)},
        {"log_event", new LogEventSerializedColumnWriter(NpgsqlDbType.Json)},
        {"user_name", new UsernameColumnWriter()}
    })
.WriteTo.Debug()
.WriteTo.Console()
.MinimumLevel.Information()
.Enrich.FromLogContext() // <--
.CreateLogger();