Authentication Options

Authentication configuration for NpgsqlRest endpoints including login/logout handling, claims mapping, and Basic Authentication.

Overview

json

{
  "NpgsqlRest": {
    "AuthenticationOptions": {
      "DefaultAuthenticationType": null,
      "StatusColumnName": "status",
      "SchemeColumnName": "scheme",
      "BodyColumnName": "body",
      "ResponseTypeColumnName": "application/json",
      "HashColumnName": "hash",
      "PasswordParameterNameContains": "pass",
      "DefaultUserIdClaimType": "user_id",
      "DefaultNameClaimType": "user_name",
      "DefaultRoleClaimType": "user_roles",
      "SerializeAuthEndpointsResponse": false,
      "ObfuscateAuthParameterLogValues": true,
      "PasswordVerificationFailedCommand": null,
      "PasswordVerificationSucceededCommand": null,
      "UseUserContext": false,
      "ContextKeyClaimsMapping": {
        "request.user_id": "user_id",
        "request.user_name": "user_name",
        "request.user_roles": "user_roles"
      },
      "ClaimsJsonContextKey": null,
      "IpAddressContextKey": "request.ip_address",
      "UseUserParameters": false,
      "ParameterNameClaimsMapping": {
        "_user_id": "user_id",
        "_user_name": "user_name",
        "_user_roles": "user_roles"
      },
      "ClaimsJsonParameterName": "_user_claims",
      "IpAddressParameterName": "_ip_address",
      "LoginPath": null,
      "LogoutPath": null,
      "BasicAuth": {
        "Enabled": false,
        "Realm": null,
        "Users": {},
        "SslRequirement": "Required",
        "UseDefaultPasswordHasher": true,
        "ChallengeCommand": null
      }
    }
  }
}

General Settings

Setting	Type	Default	Description
`DefaultAuthenticationType`	string	`null`	Authentication type for `ClaimsIdentity`. Auto-detected from database name if `null` and login endpoint exists.
`SerializeAuthEndpointsResponse`	bool	`false`	Return routine response from auth endpoints if not written by auth handler.
`ObfuscateAuthParameterLogValues`	bool	`true`	Obfuscate parameter values in logs for auth endpoints to protect credentials.

Column names used to read values from the login routine response.

Setting	Type	Default	Description
`StatusColumnName`	string	`"status"`	Column for success/failure. Boolean or numeric HTTP status code (200 = success).
`SchemeColumnName`	string	`"scheme"`	Column for authentication scheme override.
`BodyColumnName`	string	`"body"`	Column for response body message.
`ResponseTypeColumnName`	string	`"application/json"`	Column for response content type.
`HashColumnName`	string	`"hash"`	Column for password hash verification.

Password Handling

Setting	Type	Default	Description
`PasswordParameterNameContains`	string	`"pass"`	Identifies password parameter (first param containing this string).
`PasswordVerificationFailedCommand`	string	`null`	Command executed on password verification failure.
`PasswordVerificationSucceededCommand`	string	`null`	Command executed on password verification success.

Password Verification Command Parameters

Both PasswordVerificationFailedCommand and PasswordVerificationSucceededCommand receive:

Parameter	Type	Description
`$1`	text	Authentication scheme used for login.
`$2`	text	User ID.
`$3`	text	Username.

Default Claim Types

Setting	Type	Default	Description
`DefaultUserIdClaimType`	string	`"user_id"`	Claim type for user ID.
`DefaultNameClaimType`	string	`"user_name"`	Claim type for username.
`DefaultRoleClaimType`	string	`"user_roles"`	Claim type for user roles.

User Context (PostgreSQL Context Variables)

Map authenticated user claims to PostgreSQL session context variables. Enable for specific endpoints using the user_context annotation, or enable globally with UseUserContext.

Setting	Type	Default	Description
`UseUserContext`	bool	`false`	Enable automatic claim-to-context mapping for all endpoints. Override per-endpoint with user_context annotation.
`ContextKeyClaimsMapping`	object	(see below)	Map of PostgreSQL context keys to claim names. Key is the context variable name, value is the claim type.
`ClaimsJsonContextKey`	string	`null`	Context key for all claims serialized as JSON. Set to `"request.user_claims"` to enable.
`IpAddressContextKey`	string	`"request.ip_address"`	Context key for client IP address.

Default Context Mapping

json

{
  "ContextKeyClaimsMapping": {
    "request.user_id": "user_id",
    "request.user_name": "user_name",
    "request.user_roles": "user_roles"
  }
}

Custom Context Mapping Example

Map additional claims to custom context keys:

json

{
  "ContextKeyClaimsMapping": {
    "request.user_id": "user_id",
    "request.user_name": "user_name",
    "request.user_roles": "user_roles",
    "request.user_email": "email",
    "request.tenant_id": "tenant_id"
  },
  "ClaimsJsonContextKey": "request.user_claims"
}

Access in PostgreSQL

sql

-- Access individual claims
select current_setting('request.user_id', true);
select current_setting('request.user_name', true);
select current_setting('request.user_roles', true);

-- Access client IP address
select current_setting('request.ip_address', true);

-- Access all claims as JSON (when ClaimsJsonContextKey is configured)
select current_setting('request.user_claims', true)::jsonb;

TIP

Always use true as the second parameter to current_setting() to avoid errors when the setting doesn't exist.

User Parameters

Map authenticated user claims to function parameters. Enable for specific endpoints using the user_parameters annotation, or enable globally with UseUserParameters.

Setting	Type	Default	Description
`UseUserParameters`	bool	`false`	Enable automatic claim-to-parameter mapping for all endpoints. Override per-endpoint with user_parameters annotation.
`ParameterNameClaimsMapping`	object	(see below)	Map of function parameter names to claim names. Key is the parameter name, value is the claim type.
`ClaimsJsonParameterName`	string	`"_user_claims"`	Parameter name that receives all claims serialized as JSON.
`IpAddressParameterName`	string	`"_ip_address"`	Parameter name that receives the client IP address.

Default Parameter Mapping

json

{
  "ParameterNameClaimsMapping": {
    "_user_id": "user_id",
    "_user_name": "user_name",
    "_user_roles": "user_roles"
  }
}

Custom Parameter Mapping Example

Map additional claims to custom parameter names:

json

{
  "ParameterNameClaimsMapping": {
    "_user_id": "user_id",
    "_user_name": "user_name",
    "_user_roles": "user_roles",
    "_email": "email",
    "_tenant": "tenant_id"
  },
  "ClaimsJsonParameterName": "_user_claims",
  "IpAddressParameterName": "_ip_address"
}

Example Function Using Parameters

sql

create function get_user_data(
    _user_id text,
    _user_name text,
    _user_roles text[],
    _ip_address text,
    _user_claims json
)
returns table (
    user_id int,
    user_name text,
    roles text[],
    ip text,
    all_claims json
)
language sql as $$
select
    _user_id::int,
    _user_name,
    _user_roles,
    _ip_address,
    _user_claims
$$;

comment on function get_user_data(text, text, text[], text, json) is '
authorize
user_params
';

TIP

Parameters with default values can be used without authentication. When the user is authenticated, claim values override the defaults.

Setting	Type	Default	Description
`LoginPath`	string	`null`	URL path for login endpoint. `null` disables login endpoint.
`LogoutPath`	string	`null`	URL path for logout endpoint. `null` disables logout endpoint.

The login command must follow these conventions:

Return at least one record for successful authentication
No records returned = 401 Unauthorized
All columns become user claims (column name = claim type, value = claim value)

Special columns:

Column	Type	Description
`status`	bool/int	Success indicator. Boolean or HTTP status code (200 = success).
`scheme`	text	Authentication scheme override.
`body`	text	Response body message.
`hash`	text	Password hash for verification.

Logout Command Convention

No return data = sign out default scheme
Returned values = scheme names to sign out (converted to string)

Basic Authentication

HTTP Basic Authentication support with Authorization: Basic base64(username:password) header.

json

{
  "NpgsqlRest": {
    "AuthenticationOptions": {
      "BasicAuth": {
        "Enabled": false,
        "Realm": null,
        "Users": {},
        "SslRequirement": "Required",
        "UseDefaultPasswordHasher": true,
        "ChallengeCommand": null
      }
    }
  }
}

Setting	Type	Default	Description
`Enabled`	bool	`false`	Enable Basic Authentication support.
`Realm`	string	`null`	Authentication realm. Uses `"NpgsqlRest"` if `null`.
`Users`	object	`{}`	Username/password dictionary. Value is password or hash depending on `UseDefaultPasswordHasher`.
`SslRequirement`	string	`"Required"`	SSL requirement: `"Ignore"`, `"Warning"`, or `"Required"`.
`UseDefaultPasswordHasher`	bool	`true`	Expect hashed passwords in configuration.
`ChallengeCommand`	string	`null`	PostgreSQL command for authentication challenge.

SSL Requirement Values

Value	Description
`Ignore`	Allow Basic Auth without SSL (debug log warning).
`Warning`	Issue log warning when connection is not secure.
`Required`	Enforce SSL/TLS connection.

Challenge Command Parameters

Parameter	Type	Description
`$1`	text	Username from Basic Auth header.
`$2`	text	Password from Basic Auth header.
`$3`	bool	Password validation result (`true`/`false`/`null` if no password defined).
`$4`	text	Basic Auth realm.
`$5`	text	Endpoint path.

Complete Example

Production configuration with user context and login endpoint:

json

{
  "NpgsqlRest": {
    "AuthenticationOptions": {
      "DefaultAuthenticationType": "MyApp",
      "StatusColumnName": "status",
      "SchemeColumnName": "scheme",
      "HashColumnName": "hash",
      "PasswordParameterNameContains": "password",
      "DefaultUserIdClaimType": "user_id",
      "DefaultNameClaimType": "user_name",
      "DefaultRoleClaimType": "user_roles",
      "ObfuscateAuthParameterLogValues": true,
      "UseUserContext": true,
      "ContextKeyClaimsMapping": {
        "request.user_id": "user_id",
        "request.user_name": "user_name",
        "request.user_roles": "user_roles"
      },
      "IpAddressContextKey": "request.ip_address",
      "UseUserParameters": false,
      "LoginPath": "/api/auth/login",
      "LogoutPath": "/api/auth/logout",
      "BasicAuth": {
        "Enabled": false
      }
    }
  }
}

Configuration with Basic Authentication:

json

{
  "NpgsqlRest": {
    "AuthenticationOptions": {
      "BasicAuth": {
        "Enabled": true,
        "Realm": "MyAPI",
        "SslRequirement": "Required",
        "UseDefaultPasswordHasher": true,
        "ChallengeCommand": "select * from basic_auth_login($1, $2, $3)"
      }
    }
  }
}

login annotation - Mark endpoint as sign-in
logout annotation - Mark endpoint as sign-out
user_context annotation - Enable user context mapping
user_parameters annotation - Enable user parameters mapping
basic_auth annotation - Enable Basic Authentication per endpoint
Comment Annotations Guide - How annotations work
Configuration Guide - How configuration works

Next Steps

Authentication - Configure authentication methods (Cookie, Bearer Token, OAuth)
NpgsqlRest Options - Configure general NpgsqlRest settings

Authentication Options ​

Overview ​

General Settings ​

Login Response Columns ​

Password Handling ​

Password Verification Command Parameters ​

Default Claim Types ​

User Context (PostgreSQL Context Variables) ​

Default Context Mapping ​

Custom Context Mapping Example ​

Access in PostgreSQL ​

User Parameters ​

Default Parameter Mapping ​

Custom Parameter Mapping Example ​

Example Function Using Parameters ​

Login and Logout Paths ​

Login Command Convention ​

Logout Command Convention ​

Basic Authentication ​

SSL Requirement Values ​

Challenge Command Parameters ​

Complete Example ​

Related ​

Next Steps ​

Authentication Options

Overview

General Settings

Login Response Columns

Password Handling

Password Verification Command Parameters

Default Claim Types

User Context (PostgreSQL Context Variables)

Default Context Mapping

Custom Context Mapping Example

Access in PostgreSQL

User Parameters

Default Parameter Mapping

Custom Parameter Mapping Example

Example Function Using Parameters

Login and Logout Paths

Login Command Convention

Logout Command Convention

Basic Authentication

SSL Requirement Values

Challenge Command Parameters

Complete Example

Related

Next Steps