NpgsqlRest

Appearance

Comments
Written with Claude
IMPORTANT

As you may notice, this page and pretty much the entire website were obviously created with the help of AI. I wonder how you could tell? Was it a big "Written With Claude" badge on every page? I moved it to the top now (with the help of AI of course) to make it even more obvious. There are a few blogposts that were written by me manually, the old-fashioned way, I hope there will be more in the future, and those have a similar "Human Written" badge. This project (not the website), on the other hand, is a very, very different story. It took me more than two years of painstaking and unpaid work in my own free time. A story that, hopefully, I will tell someday. But meanwhile, what would you like me to do? To create a complex documentation website with a bunch of highly technical articles with the help of AI and fake it, to give you an illusion that I also did that manually? Like the half of itnernet is doing at this point? How does that makes any sense? Is that even fair to you? Or maybe to create this website manually, the old-fashioned way, just for you? While working a paid job for a salary, most of you wouldn't even get up in the morning. Would you like me to sing you a song while we're at it? For your personal entertainment? Seriously, get a grip. Do you find this information less valuable because of the way this website was created? I give my best to fix it to keep the information as accurate as possible, and I think it is very accurate at this point. If you find some mistakes, inaccurancies or problems, there is a comment section at the bottom of every page, which I also made with the help of the AI. And I woould very much appreciate if you leave your feedback there. Look, I'm just a guy who likes SQL, that's all. If you don't approve of how this website was constructed and the use of AI tools, I suggest closing this page and never wever coming back. And good riddance. And I would ban your access if I could know how. Thank you for your attention to this matter.

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';

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