tandem
/
numerus
2
0
Fork 0
Code Issues 39 Pull Requests Packages Projects 1 Releases 1 Wiki Activity
numerus/test/check_cookie.sql

113 lines
3.4 KiB
MySQL
Raw Permalink Normal View History

Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
-- Test check_cookie
set client_min_messages to warning;
create extension if not exists pgtap;
reset client_min_messages;
begin;
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
select plan(21);
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
set search_path to auth, numerus, public;
Move check_cookie to public and give access to authenticator I do not want to give access to authenticator until i know who the user is, herefore that function can not be in the numerus schema as the authenticator user can not see it.
2023-01-18 13:12:59 +00:00
select has_function('public', 'check_cookie', array ['text']);
select function_lang_is('public', 'check_cookie', array ['text'], 'plpgsql');
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
select function_returns('public', 'check_cookie', array ['text'], 'name');
Move check_cookie to public and give access to authenticator I do not want to give access to authenticator until i know who the user is, herefore that function can not be in the numerus schema as the authenticator user can not see it.
2023-01-18 13:12:59 +00:00
select is_definer('public', 'check_cookie', array ['text']);
select volatility_is('public', 'check_cookie', array ['text'], 'stable');
select function_privs_are('public', 'check_cookie', array ['text'], 'guest', array []::text[]);
select function_privs_are('public', 'check_cookie', array ['text'], 'invoicer', array []::text[]);
select function_privs_are('public', 'check_cookie', array ['text'], 'admin', array []::text[]);
select function_privs_are('public', 'check_cookie', array ['text'], 'authenticator', array ['EXECUTE']);
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
set client_min_messages to warning;
truncate auth."user" cascade;
reset client_min_messages;
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
insert into auth."user" (user_id, email, name, password, role, cookie, cookie_expires_at)
values (1, 'demo@tandem.blog', 'Demo', 'test', 'invoicer', '44facbb30d8a419dfd4bfbc44a4b5539d4970148dfc84bed0e', current_timestamp + interval '1 month')
, (9, 'admin@tandem.blog', 'Demo', 'test', 'admin', '12af4c88b528c2ad4222e3740496ecbc58e76e26f087657524', current_timestamp + interval '1 month')
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
;
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
prepare user_info as
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
select current_user_email(), current_user_cookie();
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
select is (
check_cookie('44facbb30d8a419dfd4bfbc44a4b5539d4970148dfc84bed0e/demo@tandem.blog'),
'invoicer'::name,
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
'Should validate the cookie for the first user'
);
select results_eq (
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'user_info',
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
$$ values ('demo@tandem.blog', '44facbb30d8a419dfd4bfbc44a4b5539d4970148dfc84bed0e') $$,
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'Should have updated the settings with the user info'
);
select is (
check_cookie('12af4c88b528c2ad4222e3740496ecbc58e76e26f087657524/admin@tandem.blog'),
'admin'::name,
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
'Should validate the cookie for the second user'
);
select results_eq (
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'user_info',
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
$$ values ('admin@tandem.blog', '12af4c88b528c2ad4222e3740496ecbc58e76e26f087657524') $$,
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'Should have updated the settings with the other user info'
);
select is (
check_cookie('44facbb30d8a419dfd4bfbc44a4b5539d4970148dfc84bed0e/admin@tandem.blog'),
'guest'::name,
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
'Should only match with the correct email'
);
select results_eq (
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'user_info',
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
$$ values ('', '') $$,
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'Should have updated the settings with a guest user'
);
select is (
check_cookie('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/admin@tandem.blog'),
'guest'::name,
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
'Should only match with the correct cookie value'
);
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
select results_eq (
'user_info',
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
$$ values ('', '') $$,
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'Should have left the settings with a guest user'
);
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
update "user" set cookie_expires_at = current_timestamp - interval '1 minute';
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
select is (
check_cookie('44facbb30d8a419dfd4bfbc44a4b5539d4970148dfc84bed0e/demo@tandem.blog'),
'guest'::name,
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
'Should not allow expired cookies'
);
select results_eq (
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'user_info',
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
$$ values ('', '') $$,
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'Should have left the settings with a guest user'
);
select is (
check_cookie('12af4c88b528c2ad4222e3740496ecbc58e76e26f087657524/admin@tandem.blog'),
'guest'::name,
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
'Should not allow expired cookied for the other user as well'
);
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
select results_eq (
'user_info',
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
$$ values ('', '') $$,
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
'Should have left the settings with a guest user'
);
Implement login cookie, its verification, and logout At first i thought that i would need to implement sessions, the ones that keep small files onto the disk, to know which user is talking to the server, but then i realized that, for now at least, i only need a very large number, plus the email address, to be used as a lookup, and that can be stored in the user table, in a separate schema. Had to change login to avoid raising exceptions when login failed because i now keep a record of login attemps, and functions are always run in a single transaction, thus the exception would prevent me to insert into login_attempt. Even if i use a separate procedure, i could not keep the records. I did not want to add a parameter to the logout function because i was afraid that it could be called from separate users. I do not know whether it is possible with the current approach, since the settings variable is also set by the same applications; time will tell.
2023-01-17 19:48:50 +00:00
select *
from finish();
rollback;