Storing user passwords in a separate SQL table

A typical email/password authentication flow requires storing a user's password on the backend side. Of course, it goes without saying that users' passwords must be stored in a hashed form and never exposed.

Let's say that we are working with a relational database and create a SQL table that will store details about our users. Most often, you would use a table that can be represented with this SQL.

CREATE TABLE "Users" (
    "id" UUID NOT NULL PRIMARY KEY,
    "email" TEXT NOT NULL,
    "name" TEXT
    "password" TEXT NOT NULL
);

Another approach is to store the password in a separate table.

CREATE TABLE "Users" (
    "id" UUID NOT NULL PRIMARY KEY,
    "email" TEXT NOT NULL,
    "name" TEXT
);

CREATE TABLE "Passwords" (
    "id" UUID NOT NULL PRIMARY KEY,
    "password" TEXT NOT NULL,
    "userId" UUID NOT NULL UNIQUE,
    CONSTRAINT "Password_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

Now, whenever we really want to get the password for a user, which is usually only when you are logging in a user and creating an access token, you need to explicitly join Users table with Passwords table.

Although hashed, passwords should still never be exposed because that makes them prone to offline brute force attacks. Using this approach minimizes the chance of accidentally exposing the passwords.