Persisting User Records

Create table migration

It’s time to implement database-backed persistence for Minotaur user records. I generate a migration file with mix ecto.gen.migration add_users_table and update the change callback with the logic generated by phx.gen.auth in my demo auth project.

defmodule Minotaur.Repo.Migrations.AddUsersTable do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION IF NOT EXISTS citext", ""

    create table(:users) do
      add :email, :citext, null: false
      add :hashed_password, :string, null: false
      add :username, :string, null: false

      timestamps(type: :utc_datetime)
    end

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

I run the migration locally for dev and test environments which complete successfully. I commit the change and build a new Docker image for production.

Deploying the new image runs into an error on startup:

19:01:31.828 [info] == Running 20240711180107 Minotaur.Repo.Migrations.AddUsersTable.change/0 forward
19:01:31.836 [info] execute "CREATE EXTENSION IF NOT EXISTS citext"
** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "citext"

    hint: Must have CREATE privilege on current database to create this extension.
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.16.2) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.11.3) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.16.2) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.3) lib/ecto/migration/runner.ex:311: Ecto.Migration.Runner.perform_operation/3
    (stdlib 5.2.3) timer.erl:270: :timer.tc/2

When I created the application user for PostgreSQL, I only granted the least privileges needed to run basic migration operations. This did not include the permission to run CREATE EXTENSION. I grant the application user CREATE permission for the production database. The migration runs succesfully after deploying again.

Adding user schema

User records for the application are currently stored within the in-memory state of an Elixir Agent. The UserStore module encapsulates all logic for reading and writing to the agent state. Swapping out the agent logic for a database interface should only require modifying the UserStore module.

I apply the changes to the module which cleans up the code to a few simple Ecto calls.

defmodule Minotaur.Accounts.UserStore do
  @moduledoc false

  alias Minotaur.Accounts.User
  alias Minotaur.Repo

  def get_user(id) do
    Repo.get(User, id)
  end

  def insert(changeset) do
    Repo.insert(changeset)
  end

  def get_by_username(username) do
    Repo.get_by(User, username: username)
  end

  def get_by_email(email) do
    Repo.get_by(User, email: email)
  end
end

The test suite is still green with these changes which makes me happy about my previous decisions to separate the storage implementation details from the business logic and only write tests around behavior.

I commit the changes and deploy to production. User registrations now persist between deployments!

Storing session tokens

The current implementation for session tokens in the application does not have a way to invalidate a session. To add this behavior, session tokens will need to be peristed and only considered valid if they exist in storage. I create a new migration file and use the phx.gen.auth generated files as a baseline for buildng the table. I am not adding email or password reset tokens yet, but I’ll still add the context field to the table.

defmodule Minotaur.Repo.Migrations.AddUsersTokensTable do
  use Ecto.Migration

  def change do
    create table(:users_tokens) do
      add :user_id, references(:users, on_delete: :delete_all), null: false
      add :token, :binary, null: false
      add :context, :string, null: false

      timestamps(type: :utc_datetime, updated_at: false)
    end

    create index(:users_tokens, [:user_id])
    create unique_index(:users_tokens, [:context, :token])
  end
end

I follow the same pattern of running migrations locally and deploying to production if everything looks good.

I swap out the existing session token module for the UserToken implementation from the generated files. I don’t bring over any functions related to email tokens or password reset tokens. I also copy over the generated test files for invalidating session tokens and update any application code required to make the tests pass.

Once tests are all green again, I commit the changes and deploy to production. The feature for invalidating session tokens is now live.