Is my passwordless auth (magic link OTPW) system secure?

Question

I've implemented a passwordless auth system (where the users only login using a 'magic link' sent to their email) on my own. I know designing security systems without experience is really bad, so I'm asking to know if my system has any obvious holes. Here's how it works:

1. User types their email & a unique username in JS client (SPA)
2. The email & username is sent to my API server (in GET/query string)
3. The API server consults the Users Database (see below for the design of this DB) to see if the email already exists
4. If it doesn't, the email is added to the Users Database along with the username (new user)
5. If it does, the API server ensures that the username matches the email, else responds with an error
6. The API Server generates a high-entropy GUID (long, random string) and stores it in the Users Database (in the same row as email & username)
7. The API Server emails the user with a link containing the GUID in a query string to a special '/auth/' route
8. The API Server stores the exact time this email was sent in the Users Database
9. If the link was not pressed within 30 minutes of that time, the GUID is removed from the Users Database (so the user has to login a again)
10. If the link was pressed within time, the API Server generates a new Session GUID, stores it in Users Database & puts in a cookie that's exchanged in all upcoming requests (ie. every time a new request happens, the API Server ensures the Session GUID matches the one in the Users Database, indicating they're logged in)
11. The Session GUID is removed from the Users Database if the user decides to log out
12. The Session GUID is also removed every 3 days (so users have to log in every 3 days)

The Users Database has the following columns, with the username being the primary key:

| username | email | magic_link_guid | magic_link_guid_date | session_guid | session_guid_date |

Some issues that I don't know how to fix:

1. A database call is made for each request, that can't be good
2. Handling user roles would be really difficult with this method
3. This seems susceptible to XSS/CRSF attacks

Again, I know rolling out my own system is bad, but I can't find a ready-made passwordless system for .NET/C# :(.

Answer 1

What you describe is a token authentication system. Is is not so bad, and as it has been said in comments it a the common way to implement a forgotten password feature.

Some well know applications such as redmine natively offer such an authentication system to smooth the implementation of automatic operations using an API.

One problem is the duration of the token what you call the session GUID. Here you force the user to connect at least every three days which is really intensive. If it is for professional reasons, employees are generally allowed to take holidays longer that 3 days, and for non professional task doing them every 3 days is enthusiam (from the silver badge in SE network for visiting a site for 30 consecutive days). What is a user goes for a track in mountain without network access? That means that you will have to intensively use the password generation that only relies on username+email which is not enough secure for a generalized use. But you also know that the longer the session GUID is used, the more risk of being stolen exists.

Another weakness is the handling of a permanent cookie client side to store the token. That is more or less storing a password in clear text on the client which is known to be bad. This alone should scare any security aware user. For example in redmine, the token is generated and stored in the app, and the user generated one just before using it for its API requests.

Now for your questions:

• A database call is made for each request, that can't be good

  No problem here. Caches are meant for mitigate that problem. Do not even try to do it by hand but just rely on a well known cache. Also as you speak of a browser client side, you can just rely on HTTP (or HTTPS) sessions once a user is authenticated

• Handling user roles would be really difficult with this method

  User roles shall not be handled client side. You should have a table describing the roles allowed to a user: User_ID | Role_ID. When you find a user, you search its roles and store them server side in the session. Or rely on the cache if you use a non connected API.

• This seems susceptible to XSS/CRSF attacks

  It may or not be a problem. But you should at least ensure that the token cookie is associated only with your own domain! And also rely on common mitigation ways on forms.

TL/DR: This is a token authentication system. It can be used as a complement of a main authentication system but do has serious weaknesses in the described implementations if used alone. In the opposite, the remaining questions are not really a problem and can be answered in the common way.