NpgsqlRest

Appearance

Comments

BASIC_AUTH_COMMAND ​

Also known as

basic_authentication_command, challenge_command (with or without @ prefix)

Set the PostgreSQL command used to validate Basic Authentication credentials and return user claims.

Syntax ​

code
@challenge_command = <sql-command>
@basic_auth_command = <sql-command>
  • sql-command: A SQL SELECT statement that validates credentials and returns user claims.

Command Parameters ​

The challenge command receives up to 5 parameters in the following order:

ParameterTypeDescription
$1textUsername from the Authorization header
$2textPassword from the Authorization header (plain text)
$3booleanPre-validation result: true if password matched annotation credentials, false if not, null if no credentials were configured in the annotation
$4textRealm name (from annotation or default NpgsqlRest)
$5textRequest path (e.g., /api/my-endpoint)

Return Value ​

The challenge command result set is interpreted exactly the same as the LOGIN endpoint. This includes support for special columns and claim mapping.

Special Columns ​

Four special column names control authentication behavior (configurable in AuthenticationOptions):

ColumnDefault NameTypePurpose
Statusstatusboolean or intControls success/failure. true or 200 = success, false or other status code = failure
SchemeschemetextAuthentication scheme name for sign-in
BodybodytextResponse body message (for schemes that don't write body)
HashhashtextPassword hash for verification by NpgsqlRest

Authentication Success ​

Return a row with claim columns. Column names become claim types, values become claim values:

sql
sql
-- Successful authentication returns claims
select
    1 as name_identifier,        -- becomes ClaimTypes.NameIdentifier
    'john_doe' as name,          -- becomes ClaimTypes.Name
    'admin' as role;             -- becomes ClaimTypes.Role

Authentication Failure ​

Return either:

  • No rows (empty result set), OR
  • A row with a status column set to false (boolean) or a non-200 status code (integer)
sql
sql
-- Method 1: Return no rows
select * from users where false;

-- Method 2: Return status = false (boolean)
select false as status, null as name;

-- Method 3: Return status code (integer)
select 401 as status, 'Invalid credentials' as body;

For complete details on result set interpretation, see LOGIN - Special Columns.

Examples ​

Basic Challenge Command ​

sql
sql
-- Validation function that checks credentials in the database
create function auth_challenge_command(
    _user text,
    _password text,
    _valid boolean,
    _realm text,
    _path text
)
returns table (
    name_identifier int,
    name text,
    role text
)
language sql
begin atomic;
select
    u.id,
    u.username,
    u.role
from users u
where u.username = _user
  and u.password_hash = crypt(_password, u.password_hash);
end;

-- Endpoint using the challenge command
create function protected_resource(
    _user_claims json
)
returns text
language sql
begin atomic;
select _user_claims;
end;

comment on function protected_resource(json) is '
HTTP GET
@basic_auth
@challenge_command = select * from auth_challenge_command($1, $2, $3, $4, $5)
@user_params
';

Equivalent as a SQL file endpoint (sql/protected-resource.sql):

sql
sql
/*
HTTP GET
@basic_auth
@challenge_command = select * from auth_challenge_command($1, $2, $3, $4, $5)
@user_params
*/
select :_user_claims;

Challenge Command with Pre-Validated Password ​

When basic_auth includes credentials, the $3 parameter indicates if the password already matched:

sql
sql
create function auth_with_preval(
    _user text,
    _password text,
    _valid boolean,     -- true if password matched annotation credentials
    _realm text,
    _path text
)
returns table (
    name_identifier int,
    name text,
    password text,
    valid boolean,
    realm text,
    path text
)
language sql
begin atomic;
select 1, _user, _password, _valid, _realm, _path;
end;

-- Generate hash: ./npgsqlrest --hash my_password
-- Output: Myb55+6lW6iiUOI3opLkysOaS8J0NNIuQ+qE2SGaKs3r62ngDJROrhX75+zmLC7t

create function get_basic_auth_challenge_command_pass(
    _user_claims json
)
returns text
language sql
begin atomic;
select _user_claims;
end;

comment on function get_basic_auth_challenge_command_pass(json) is '
HTTP GET
@basic_auth my_name Myb55+6lW6iiUOI3opLkysOaS8J0NNIuQ+qE2SGaKs3r62ngDJROrhX75+zmLC7t
@challenge_command = select * from auth_with_preval($1, $2, $3, $4, $5)
@user_params
';

Test with:

bash
bash
# Generate header: ./npgsqlrest --basic_auth my_name my_password
curl -H "Authorization: Basic bXlfbmFtZTpteV9wYXNzd29yZA==" \
     http://localhost:5000/api/get-basic-auth-challenge-command-pass

# Returns: {"name_identifier":"1","name":"my_name","password":"my_password","valid":"True","realm":"NpgsqlRest","path":"/api/get-basic-auth-challenge-command-pass"}

Challenge Command That Denies Access ​

sql
sql
create function auth_challenge_command_failed(
    _user text,
    _password text,
    _valid boolean,
    _realm text,
    _path text
)
returns table (
    status boolean,          -- First column named 'status' controls auth
    name_identifier int,
    name text
)
language sql
begin atomic;
select false, 1, _user;      -- status = false means denied
end;

create function denied_endpoint(
    _user_claims json
)
returns text
language sql
begin atomic;
select _user_claims;
end;

comment on function denied_endpoint(json) is '
HTTP GET
@basic_auth
@challenge_command = select * from auth_challenge_command_failed($1, $2, $3, $4, $5)
@user_params
';

This will always return 401 Unauthorized because the status column is false.

Challenge Command Without Annotation Credentials ​

When basic_auth is used without credentials, $3 will be null:

sql
sql
create function get_basic_auth_challenge_command(
    _user_claims json
)
returns text
language sql
begin atomic;
select _user_claims;
end;

comment on function get_basic_auth_challenge_command(json) is '
HTTP GET
@basic_auth
@challenge_command = select * from auth_challenge_command($1, $2, $3, $4, $5)
@user_params
';

Test with:

bash
bash
# Any username/password combination will be passed to the challenge command
curl -H "Authorization: Basic eHh4Onl5eQ==" \  # xxx:yyy
     http://localhost:5000/api/get-basic-auth-challenge-command

# Returns: {"name_identifier":"1","name":"xxx","password":"yyy","valid":null,"realm":"NpgsqlRest","path":"/api/get-basic-auth-challenge-command"}

Behavior ​

  • The challenge command is executed for every request to the protected endpoint.
  • If both annotation credentials and a challenge command are configured, the password is first verified against annotation credentials, and the result is passed as $3.
  • The challenge command can implement custom logic such as:
    • Database-backed user authentication
    • Rate limiting based on failed attempts
    • IP-based access control using the path parameter
    • Audit logging of authentication attempts
    • Multi-factor authentication flows
  • If the challenge command returns a row (without status = false), the column names become claim types and values become claim values for the authenticated user.
  • Claims are accessible in the endpoint function via the user_params annotation.

Comments