External OAuth Authentication

NpgsqlRest supports OAuth authentication with popular external providers including Google, LinkedIn, GitHub, Microsoft, and Facebook.

Overview

json

{
  "Auth": {
    "External": {
      "Enabled": true,
      "SigninUrl": "/signin-{0}",
      "ReturnToPath": "/",
      "LoginCommand": "select * from external_login($1,$2,$3,$4,$5)"
    }
  }
}

Settings Reference

Setting	Type	Default	Description
`Enabled`	bool	`false`	Enable external OAuth providers.
`BrowserSessionStatusKey`	string	`"__external_status"`	sessionStorage key for auth status (HTTP status code).
`BrowserSessionMessageKey`	string	`"__external_message"`	sessionStorage key for auth message.
`SigninUrl`	string	`"/signin-{0}"`	Sign-in page URL pattern. `{0}` is replaced with provider name.
`SignInHtmlTemplate`	string	(see below)	HTML template for the sign-in page.
`RedirectUrl`	string	`null`	URL to redirect after auth. Usually auto-detected.
`ReturnToPath`	string	`"/"`	Default path to redirect after auth completes.
`ReturnToPathQueryStringKey`	string	`"return_to"`	Query string key for dynamic return path.
`LoginCommand`	string	`"select * from external_login($1,$2,$3,$4,$5)"`	PostgreSQL command to execute after OAuth login. Uses the same result processing logic as the login endpoint.
`ClientAnalyticsData`	string	(JavaScript object)	Browser analytics data sent to login command.
`ClientAnalyticsIpKey`	string	`"ip"`	JSON key for client IP in analytics data.

SignInHtmlTemplate

HTML template for the sign-in page shown during communication with the OAuth provider. This is typically a simple loading page. You can customize this to show your own loading animation, spinner, or branding. Format placeholders:

{0} - Provider name (e.g., "Google", "GitHub")
{1} - JavaScript to redirect to the external auth provider

Default value:

html

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8" />
  <title>Talking To {0}</title>
</head>
<body>
  Loading...
  {1}
</body>
</html>

The LoginCommand is a PostgreSQL command that executes after successful OAuth authentication. It uses the same result set conventions as the login annotation - column names, special columns (status, scheme, body), and claim handling all work identically.

For full details on how the result set is processed (return type requirements, special columns, claim types, status codes), see the Login Endpoint Conventions documentation.

Parameters

The LoginCommand receives up to five parameters:

Parameter	Type	Description
`$1`	text	External login provider name (e.g., "google", "github")
`$2`	text	User's email address
`$3`	text	User's display name
`$4`	text/json/jsonb	Raw JSON data from the OAuth provider
`$5`	text/json/jsonb	Browser analytics data (screen size, timezone, etc.)

Result Set Conventions

The command must return a named record (table). The result is processed using the same rules as login endpoints:

Special columns: status, scheme, body control login behavior (see Special Columns)
All other columns: Become security claims (column name = claim type, column value = claim value)
Empty result: Returns 401 Unauthorized
Multiple rows: Only the first row is processed

sql

create function external_login(
    _provider text,
    _email text,
    _name text,
    _data jsonb,
    _analytics jsonb
)
returns table(status boolean, id int, name text, email text, provider text)
language plpgsql as $$
declare
    _user_id int;
begin
    -- Find or create user
    select id into _user_id from users where email = _email;

    if _user_id is null then
        insert into users (email, name, created_via)
        values (_email, _name, _provider)
        returning id into _user_id;
    end if;

    -- Return claims (same format as login endpoint)
    return query
    select
        true as status,
        _user_id as id,
        _name as name,
        _email as email,
        _provider as provider;
end;
$$;

OAuth Providers

NpgsqlRest includes pre-configured defaults for Google, LinkedIn, GitHub, Microsoft, and Facebook. For these providers, you only need to set ClientId and ClientSecret - all URL settings have sensible defaults.

Minimal Configuration

For pre-configured providers, this is all you need:

json

{
  "Auth": {
    "External": {
      "Enabled": true,
      "Google": {
        "Enabled": true,
        "ClientId": "{GOOGLE_CLIENT_ID}",
        "ClientSecret": "{GOOGLE_CLIENT_SECRET}"
      }
    }
  }
}

The AuthUrl, TokenUrl, InfoUrl, and EmailUrl settings are optional and only needed if:

The provider changes their endpoints
You need custom OAuth scopes
You're defining a custom provider not listed below