NpgsqlRest

Appearance

Comments
Written with Claude

AUTHORIZE ​

Also known as

authorized, requires_authorization (with or without @ prefix)

Require authentication for the endpoint. Optionally restrict access by roles, user names, or user IDs.

Syntax ​

code
@authorize
@authorize <value1>, <value2>, <value3>, ...

Values can be role names, user names, or user IDs. Space-separated lists are also valid: @authorize admin editor john

Examples ​

Require Any Authenticated User ​

sql
sql
create function get_my_profile()
returns json
language sql
begin atomic;
select row_to_json(u) from users u where u.id = current_user_id();
end;

comment on function get_my_profile() is
'HTTP GET
@authorize';

Equivalent as a SQL file endpoint (sql/get-my-profile.sql):

sql
sql
-- HTTP GET
-- @authorize
select row_to_json(u) from users u where u.id = current_user_id();

Unauthenticated requests receive 401 Unauthorized.

Alternative Keywords ​

sql
sql
-- All of these are equivalent
comment on function func1() is 'HTTP
@authorize';

comment on function func2() is 'HTTP
@authorized';

comment on function func3() is 'HTTP
@requires_authorization';

Require Specific Role ​

sql
sql
create function delete_user(_id int)
returns void
language sql
begin atomic;
delete from users where id = _id;
end;

comment on function delete_user(int) is
'HTTP DELETE
@authorize admin';

Only users with the admin role can access this endpoint.

Authorize by User Name ​

Available since version 3.11.1

sql
sql
create function get_my_profile()
returns json
language sql
begin atomic;
select row_to_json(u) from users u where u.id = current_user_id();
end;

comment on function get_my_profile() is
'HTTP GET
@authorize john';

Only the user with user name john can access this endpoint. Matches against the DefaultNameClaimType claim.

Authorize by User ID ​

Available since version 3.11.1

sql
sql
create function get_account()
returns json
language sql
begin atomic;
select row_to_json(a) from accounts a where a.user_id = current_user_id();
end;

comment on function get_account() is
'HTTP GET
@authorize user123';

Only the user with user ID user123 can access this endpoint. Matches against the DefaultUserIdClaimType claim.

Multiple Roles ​

sql
sql
create function manage_content(_action text, _id int)
returns json
language sql
begin atomic;
...;
end;

comment on function manage_content(text, int) is
'HTTP POST
@authorize admin, editor, moderator';

Users must have at least one of the specified roles.

Mix of Roles and User Identifiers ​

Available since version 3.11.1

sql
sql
comment on function get_data() is
'HTTP GET
@authorize admin, user123, jane';

Access is granted if the user matches any of the specified values — whether it's a role name, user name, or user ID. Each value is checked against all three claim types (DefaultRoleClaimType, DefaultNameClaimType, DefaultUserIdClaimType).

Authorize Before HTTP ​

The order of annotations doesn't matter:

sql
sql
comment on function protected_func() is
'@authorize admin
HTTP GET';

Authorize on Separate Line ​

sql
sql
comment on function another_protected() is
'HTTP

@authorize';

Behavior ​

  • Returns 401 Unauthorized for unauthenticated requests
  • Returns 403 Forbidden when values are specified and user lacks a matching role, user name, or user ID
  • Works with all configured authentication providers (JWT, Cookie, Basic, etc.)
  • ALLOW_ANONYMOUS - Override to allow unauthenticated access
  • LOGIN - Mark as authentication endpoint
  • LOGOUT - Mark as sign-out endpoint

See Also ​

Comments