Getting a client readable message from an Npgsql.PostgresException

Question

I'm writing a web api using PostgreSQL and am checking database constraints as part of the validation process, but I also have a global exception filter as a fallback in case something gets by when saving. My problem is that the exception doesn't seem to have any message that I can present to the client without some processing. The added image is of the PostgresException data from a breakpoint. For example, in this case I would want something along the lines of "Asset Number x already exists" or just "Asset Number must be unique". Is this something that can be configured somewhere? The place that makes the most sense is at the constraint creation code, but I couldn't find an option to do so.

modelBuilder.Entity<AssetItem>().HasIndex(item => new { item.AssetNumber }).IsUnique();

public class DbExceptionFilter : IExceptionFilter 
{
    private const string UNIQUE_EXCEPTION = "23505";
    public async void OnException(ExceptionContext context)
    {
        var exceptionType = context.Exception.InnerException.GetType().FullName;

        if (exceptionType == "Npgsql.PostgresException")
        {
            var pgException = (PostgresException) context.Exception.InnerException;

            switch(pgException.SqlState)
            {
                case UNIQUE_EXCEPTION:
                    var error = new {error = "Unique Error Here"};
                    await WriteJsonErrorResponse(context.HttpContext.Response, HttpStatusCode.BadRequest, error);
                    return;
            }
        } 
        else 
        {
            var error = new { error = "Unexpected Server Error"};
            await WriteJsonErrorResponse(context.HttpContext.Response, HttpStatusCode.InternalServerError, error);
            return;
        }
    }

    private async Task WriteJsonErrorResponse(HttpResponse response, HttpStatusCode statusCode, dynamic error)
    {
        response.ContentType = "application/json";
        response.StatusCode = (int) statusCode;
        await response.Body.WriteAsync(Encoding.ASCII.GetBytes(JsonConvert.SerializeObject(error)));
    }
}

Answer 1

The closest thing to a user-readable message that PostgreSQL provides is the message text exposed on PostgresException.

However, as a general rule it is not a good idea to expose database errors directly to users (including web API users): these are intended to the application directly interacting with the database (i.e. your application). These messages generally don't mean much to the users of your API, and more importantly they leak potentially sensitive information about your database schema and are therefore not secure. It's especially problematic to dump/serialize the entire exception to the user as you seem to be doing (with JsonConvert.SerializeObject).

The best practice here would be to identify legitimate database exceptions that the user may trigger, intercept these and return and appropriately-worded message of your own (e.g. "A user with that name already exists").

As a side note, to identify PostgresException, rather than getting the name of the exception and comparing to that, you can simply use C# pattern matching:

if (context.Exception.InnerException is PostgresException postgresException)
{
    // ...
}