Authentication and authorization demystified by example and experience

Hello everyone,

Today I would like to ask for your help on authorization and authentication

So at the moment i have the following migration

defmodule PhxApi.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :username, :string
      add :email, :string
      add :password_hash, :text
      add :role, :string
      add :confirmed, :boolean
      add :attempts, :integer
      add :locked, :boolean


    create unique_index(:users, [:email])
    create unique_index(:users, [:username])

The following migration is used to take into account the following scenarios:

  • User
    • registration
    • login
    • account confirmation
    • user block by username and email

What would you add more to this migration and in general to take into account other security scenarios?

Also how you deal with users ip where would you store them?

Would you use ETS or mnesia for account confirmation?

Also other suggestions or you person experiences are welcomed
Thanks in advance

I’d take a look at Pow or phauxth and how they handle things.

Why would you want to store IPs in the database?

I’m wondering why :attempts is a string. Sounds like it should be an integer.
Also while you have a confirmed field I’d additionally propose to separate email from unconfirmed_email, so on a change you don’t loose the already confirmed email until the new one is confirmed as well.


Where would you recommend storing the ips?

You are right it should be a integer

Also very cool suggestion by using the unconfirmed_email and the confirmed_email strategy to have both

Thanks for providing your thoughts and ideas on this matter.

Depends on why you want to store them.

Based on prev. conversations in another topic of yours: If you want to pin sessions to IPs, then it’s only natural to put the IP in the session itself. It will neatly cleanup after itself.

1 Like

Yes but thinking a little bit about attacks and user behavior wouldn’t it be a good idea to store them also somewhere else for the following case:

  • A non logged in user(bot or human) makes multiple requests just to generate a ddos(DENIAL OF SERVICE ATTACK) attack how can i stop it if they are not logged in?

Can you explain your idea please? (I’m still quite new)
Why do you need some kind of store in addition to the db for account confirmation?

This is a valid use-case for storing an IP for a bit longer timeframe, possibly in a db, but this has nothing to do with a users table though. This would at least live in a totally separate part/table of your application. Generally I’d also suggest to move prevention of such attacks up the stack and use e.g. fail2ban or similar software or do it even at the dns level with cloudflare. This way your application(s) don’t need to deal with those issues.

1 Like

Mensia and ets are databases that are using the ram of the server so they are much faster for read and delete then a normal db.

Redis is a good example of this.

Also because you need the confirmation token which is basically a secret string to verify if the user that clicked the link form the email you sent for the account confirmation, is who it pretends to be and not someone else that wants to take control over another users account.

This idea is used for the confirmation of the account also if a user forgets a password.

If what i wrote is unclear please tell me and i will try to explain in another way.

Why would you want to use ets or mnesia for that information though? Either the token can validate itself (signed token) or you need a persistent db, which you already have with ecto.

In the past I used redis because those tokens don’t have a very long existence they are only necessary for that verification also they need to expire in let’s say 15 minutes.
So reading and writting to the db may become expensive.

Correct me if I’m wrong, but you did not really explain the reason WHY you want to use mnesia or ets for the account confirmation.

For example: is the reason because you do not want to store that secret token in the database? (security)
is the reason because of some performance optimization? (but then why limit performance optimization for that very specific case?)

Even if you use the RAM, won’t that create scalability issues (i.e. RAM is not shared among multiple servers) ?

Got it.

I’m not aware of any timeout feature in mnesia as well and ETS would mean you’ll lose your tokens if the machine stops (e.g. deployment) as well as the data not being shared between machines. Usually you’ll just periodically cleanup such tables by removing everything already expired. It often doesn’t matter if there are a bunch of recently expired tokens still in the db.

OK, because in node from where i come from that was the method used to handle this scenarios.

I would use it like this:

  • User creates account then:

  • a function generates the token and stores it in redis like this {account_conf_token: “token”}

  • another functions create a link with the token

  • nodemailer send confirmation email with the link generated with token towards user

  • the user clicks the link in the email and makes a request

  • the request is handled by another function that validates the token if it is valid then delete the token from redis and update the user account to confirmed

else the user gets an error of unauthorized or page not found.

This process repeats for the forgot password scenario.

The token can be used only once

Also redis would remember the last modifications when the server is restarted, I don’t know how but i have to look into it.

Found this

There is an option for redis to write to disk before server shutdown

Then were would mnesia and ets be useful if they don’t have a persistence option?

ETS is not persistent (memory only), mnesia/dets is. But why introduce the complexity of another storage if you’re already using ecto. You used redis before, but what was the reason for another storage in the first place?

The workflow you posted above will be exactly the same with elixir. I’m not questioning the flow, but the choice of storage. Also keep in mind that even no storage is an option. E.g. you could use Phoenix.Token for the tokens, which you can validate at a later time without having something stored.

The idea was for performance. To write and delete from redis is faster then from mongodb or postresql when it comes to node applications.

What would I do when I want to delete the token after i use it for that particular request(account_confirm or forgot_password). If I use Phoenix tokens?

Because in node I used this strategy to be able to get rid of tokens that were already used, but by using Phoenix Token i would have to wait for that token to expire.

So how would I handle the scenario were a malicious users makes multiple requests using the same link with the token that hasn’t expired yet?

Thanks @LostKobrakai for your thoughts and the idea exchange on this topic.

Also waiting for others to join this discussion and bring some ideas or experiences into it.

Fair enough. I’d first validate that ecto is indeed not fast enough, which depends a lot on your setup and numbers of registration per timeframe. If so mnesia or redis can still be useful.

There’s no deletion, because there’s no storage (besides what you sent the user). But do you care? Usually confirmation of an email address is idempotent. You can confirm it once, twice, however often, the result will always be the same. Unless it’s ddos level amounts of requests (different topic) it should be fine to just let users confirm their emails multiple times.