How to idiomatically do "In a single transaction, create a user if the table is empty"

Hi ! I was wondering how to idiomatically do the below in ash. The idea being when the app is being setup for the first time, the user should be able to create a admin user. But the action should only allow creating exactly one user in a single transaction.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

INSERT INTO users (column1, column2)
SELECT 'value1', 'value2'
WHERE NOT EXISTS (
    SELECT 1 FROM users
);

COMMIT;

This is a linked to and probably a continuation of https://elixirforum.com/t/how-to-expose-has-users-type-aggregate-in-accounts-users.

What I tried till now:

In users.ex:

    # This action is used to setup the first admin user when there are no other users in the system.
    # May not be an atomic action - concurrent requests may create multiple admin users.
    create :register_first_admin_with_password do
      description "Setup the first admin user when there are no other users in the system."

      argument :email, :ci_string do
        allow_nil? false
      end

      argument :password, :string do
        description "The proposed password for the admin user, in plain text."
        allow_nil? false
        constraints min_length: 8
        sensitive? true
      end

      argument :password_confirmation, :string do
        description "The proposed password for the admin user (again), in plain text."
        allow_nil? false
        sensitive? true
      end

      # Sets the email from the argument
      change set_attribute(:email, arg(:email))

      # Sets the role to admin
      change set_attribute(:role, :admin)

      # Sets the strategy name to password
      change set_context(%{strategy_name: :password})

      # Hashes the provided password
      change AshAuthentication.Strategy.Password.HashPasswordChange

      # Generates an authentication token for the user
      change AshAuthentication.GenerateTokenChange

      # Validates that this is the first user in the system
      validate App.Accounts.User.Validations.FirstUser

      # Validates that the password matches the confirmation
      validate AshAuthentication.Strategy.Password.PasswordConfirmationValidation

      metadata :token, :string do
        description "A JWT that can be used to authenticate the admin user."
        allow_nil? false
      end
    end

In validations:

defmodule App.Accounts.User.Validations.FirstUser do
  @moduledoc """
  Validation that ensures there are no existing users in the system.
  This is used for the setup_admin_with_password action to ensure it only runs
  when setting up the first admin user.
  """

  use Ash.Resource.Validation

  alias Ash.Error.Framework.AssumptionFailed

  @impl true
  def validate(_changeset, _opts, _context) do
    case App.Accounts.has_users!() do
      false -> :ok
      true -> {:error, AssumptionFailed.exception(message: "Users already exist")}
    end
  end

  @impl true
  def atomic(changeset, opts, context) do
    validate(changeset, opts, context)
  end

end

Slight digression, but does anyone know if it’s safe for only some transactions to be SERIALIZABLE like this? The docs imply it’s not (“…among concurrent serializable transactions…”) but I don’t trust that this isn’t just vague wording.

One would expect an optimistic system to fail serializable transactions which read keys written by non-serializable transactions, but I don’t know in detail how Postgres takes predicate locks. If it’s pessimistic then maybe a non-serializable transaction would be allowed to write a user row concurrently?

Either way I think you would be safe in this case in that you would at least not write two admin rows, but you might not be guaranteeing that the admin user is the first user. Which probably doesn’t matter here.

Okay, after some searching I found some discussion indicating that it’s not safe to mix isolation levels. The Postgres wiki also indicates that it’s not safe, and the docs indicate it in a couple of places (rather vaguely tbh) like I mentioned above.

It still wasn’t clear to me exactly how unsafe it is in practice, though, so I revisited the Postgres serializable paper which was published at the time (great paper btw). Apparently they implemented a new type of lock (“SIReadLock”) which is managed in a separate lock manager. Taking the wording from the docs I assume only serializable transactions interact with this lock manager.

The SiReadLocks are predicate locks and are taken when the transactions perform reads. According to the paper the lock manager can also check writes against the predicate locks. I assume only serializable transactions check their writes (which makes sense).

Therefore, I don’t think a serializable transaction can observe a concurrent write (against its predicate lock) from a non-serializable transaction if that write occurs after the read but before the commit. So I don’t think this transaction can guarantee the admin is the first user in the presence of other, non-serializable user transactions. If all transactions were serializable it would work as expected. I might still want to test this to confirm.

Apologies if this veers too far off-topic, I got a bit too curious :slight_smile: I’m sure Zach will pop in to answer your actual question soon enough!

3 Likes

Love the research :exploding_head: - TIL: Serializable isolation level is global in scope. This is very interesting. Makes me think if table level locks are the solution for my use-case. Given this API is called rarely, ideally only once, this should have minimal performance hit.

1 Like

I wonder if in this particular case you might be better off using “human-level locks”, i.e. manually disable signup until after the admin sets up the app for the first time, perhaps via a privileged interface like CLI. Or just set yourself as admin manually with psql, given you only have to do it once!

But I don’t know your use-case, if you’re dealing with non-technical admins it might not be so simple.

Do you actually need to guarantee no user signs up before the admin user? If it’s enough to guarantee there is only one admin user, what you posted should work fine. You could also use a unique constraint on the admin column, or you could use an advisory lock in the admin signup path. (How many times is that now, Zach? lol)

BTW, it is not actually so simple. The serializable transactions still conflict with each other. The problem is that that guarantees which only work sometimes become very difficult to understand. I think I know how it works now but you never know with this stuff tbh.

In my case, users cannot really signup. The idea is once an admin user is created after the first setup, they can create accounts for others include other admin users. So the flow would look something like -

  1. Create the initial bootstrapping admin user. (I did think of a cli or pre-seeding with env vars, but gearing it towards not so technical folks and a better UX).
  2. This admin user can “invite” other normal / admin users.
  3. Other admins can invite more users.

A very “locked” down system - Just admins can create more users. Regular users can still change passwords and stuff.

Wondering if I could contribute this as one type of auth strategy in ash. May be some day :slight_smile:

I think I get the mental model for it, need to dive for some clarity. Thanks for the links and references - got my weekend reading material :slight_smile:

1 Like

If only an admin user can create more users, that alone should be enough to enforce your invariant, no? There cannot be any users created before the admin user.

If you’re worried about a race where multiple admin users are created simultaneously, serializable (or an advisory lock) can prevent that, but I would think you need some sort of method to authenticate the first user anyway. Otherwise anybody could create the first admin user, right?

Generally this is done with some sort of “signup key” which is given to the first user over some other channel before they do the setup. If you have such a key (needed for security anyway) then that would also provide a guarantee that only one admin user is created (at first), since nobody else knows the key.

I think you can likely solve this at the data model level via essentially just a unique constraint. There can only be one “founding” user that is also an admin, and only an admin can create another admin. When you are setting up the app, you’ll be attempting to create a founding user, which will fail if another one has somehow been created. When creating an admin, you’ll be looking at the current user’s role to determine if they are an admin, which effectively means there clearly exists some other admin user allowing you to do this.

2 Likes

Another stupid solution: insert user with id=1 and rewind autoincrement on primary key (in transaction). If transaction fails with specific error you know user already there

Thanks for all the answers ! I decided not to complicate my life for a short windows of opportunity and stuck with a simple validation.

1 Like