Designing an IdP User Store

Hello! Welcome to the first lesson in our new module, Architecting Identity Platforms: Foundations.

In the previous module, we completed our deep dive into bot management by designing a system that synthesizes multiple detection signals into a final, actionable verdict. We focused on distinguishing human users from automated threats at the application layer.

Now, we pivot to a foundational pillar of modern application security: Identity and Access Management (IAM). Instead of just identifying users, we will now architect the systems that authenticate and authorize them. We'll start from first principles to design an Identity Provider (IdP)—the core engine that manages user identities, verifies their credentials, and issues the security assertions that grant them access to applications.

Our journey begins with the most fundamental component of any IdP: the user store. This brings us to today's learning outcome:

Design the user store for an Identity Provider (IdP), covering data modeling, secure credential storage, and scaling strategies.

In this lesson, we will architect the database schema and storage strategy for holding user identity data. We will focus on three critical aspects:

1. Data Modeling: How to structure the user data, define relationships, and ensure integrity.
2. Secure Credential Storage: The non-negotiable techniques for storing passwords safely, moving far beyond plaintext.
3. Scaling Strategies: How to design the user store to support millions of users across thousands of organizations in a multi-tenant architecture.

Let's begin by laying the foundation: the data model.

1. Data Modeling the User Store

Before writing any CREATE TABLE statements, we must understand the entities and relationships our IdP will manage. The primary entity is, of course, the user.

A good database schema should be a clear reflection of your application's domain. The following article provides an excellent set of principles for database design that we will apply throughout this lesson.

Applying these principles, let's design a basic users table.

Core User Attributes and Primary Key

A user has several key attributes: an identifier, contact information, a password, and status metadata.

A crucial first decision is the primary key. As recommended in the article, we will use a surrogate key—an identifier with no business meaning—instead of a natural key like an email address. This prevents cascading updates if the user changes their email.

We have a choice between an auto-incrementing integer (BIGSERIAL) and a UUID.

• Auto-incrementing Integers: Simple, fast, and space-efficient. However, they are predictable (/users/123 implies /users/124 exists), which can be a minor information leak.
• UUIDs: Non-sequential and globally unique, which is excellent for distributed systems and hides information. They are larger, and randomly generated UUIDs (v4) can cause database index fragmentation. Time-ordered variants like UUIDv7 or ULIDs are often preferred at scale.

For an IdP that may need to operate in a distributed environment, starting with a UUID is a robust choice.

Here is a first draft of our users table in PostgreSQL syntax:

CREATE TABLE users (
    -- Using UUID for a globally unique, non-sequential primary key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- User's email, used for login and communication. Must be unique.
    email VARCHAR(255) NOT NULL UNIQUE,

    -- We will define this column in the next section.
    password_hash VARCHAR(255) NOT NULL,

    -- User status, e.g., 'active', 'suspended', 'pending_verification'
    status VARCHAR(50) NOT NULL CHECK (status IN ('active', 'suspended', 'pending_verification')) DEFAULT 'pending_verification',

    -- Audit timestamps, automatically managed by the database.
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Index for fast lookups by email, which is critical for login.
CREATE INDEX idx_users_email ON users(email);

This simple schema already incorporates several best practices from the article: a surrogate primary key, a UNIQUE constraint on email, a CHECK constraint to enforce valid status values, and non-nullable audit timestamps.

However, the password_hash column is currently just a placeholder. Storing credentials securely is arguably the most critical function of the user store. Let's design that next.

2. Secure Credential Storage

Storing passwords in plaintext is a critical vulnerability. If the database is compromised, every user's account is instantly accessible. The correct approach is to store a hash of the password.

The following video gives a quick, clear overview of the fundamental concepts.

The video introduces two key ideas: hashing and salting. To build a production-grade system, we need to go deeper. The OWASP Password Storage Cheat Sheet is the industry-standard guide.

Let's synthesize what we've learned into a set of architectural requirements for our credential storage system.

1. Use a Modern, Slow Hashing Algorithm: Fast hashes like MD5 or SHA-256 are designed for speed and are easily brute-forced with modern hardware. We must use an algorithm specifically designed for password hashing, which is intentionally slow and resource-intensive. Argon2id is the current best-in-class recommendation.

2. Incorporate a Salt: A salt is a unique, random value generated for each user. It's combined with the password before hashing. This ensures that two users with the same password will have different hashes, rendering rainbow tables useless. Modern password hashing libraries handle salt generation and storage automatically.

3. Use a Sufficient Work Factor (Cost): Algorithms like Argon2id and bcrypt have configurable cost parameters (iterations, memory usage, parallelism). This "work factor" allows you to tune how slow the hashing function is, making it more expensive for an attacker to run brute-force attempts. The goal is to make it as slow as you can tolerate on your login flow (e.g., ~100-500ms).

4. Consider a Pepper (Optional but Recommended): A pepper is a secret value, shared across all users, that is added to the password hash. Unlike a salt, the pepper is not stored in the database. It should be stored separately, for example, in a secrets manager (like HashiCorp Vault or AWS KMS). This adds another layer of defense; an attacker who steals only the database backup still cannot crack the hashes without also compromising the pepper.

The output of a modern hashing function like Argon2id is a single string that conveniently packages the algorithm name, cost factors, the salt, and the final hash. For example: $argon2id$v=19$m=65536,t=3,p=4$c29tZXNhbHQ$RdescudvJCsgt3ub+b+dWRWvVRun/UhIKxgYmpM9ZFA.

Therefore, our password_hash column in the users table should simply be a VARCHAR or TEXT field large enough to hold this output string (e.g., VARCHAR(255) is usually sufficient).

3. Scaling the User Store

Our current schema works well for a single application, but an IdP is often a multi-tenant service, meaning it serves many different organizations (tenants) from a single shared infrastructure. This introduces significant scaling and data isolation challenges.

The following video provides an excellent breakdown of the architectural patterns for multi-tenancy.

The three models present a classic trade-off:

Model	Isolation	Cost & Complexity	Best For
Shared Database, Shared Schema	Low	Low	Most SaaS applications, startups, SMBs.
Shared Database, Schema per Tenant	Medium	Medium	Applications with moderate compliance needs or customization per tenant.
Database per Tenant	High	High	Large enterprises, government clients, highly regulated industries.

For a general-purpose IdP, the Shared Database, Shared Schema model is the most common and scalable starting point. Let's adapt our schema for it.

We need two main changes:

1. A tenants table to store information about each organization.
2. A tenant_id foreign key in the users table to associate each user with their organization.

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    -- other tenant-specific settings...
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Modified users table for multi-tenancy
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Foreign key linking to the tenants table
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL CHECK (status IN ('active', 'suspended', 'pending_verification')) DEFAULT 'pending_verification',

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- A user's email must now be unique *within their tenant*, not globally.
    UNIQUE (tenant_id, email)
);

-- Critical index for isolating queries by tenant.
CREATE INDEX idx_users_tenant_id ON users(tenant_id);

-- The email lookup index should now also include tenant_id.
CREATE INDEX idx_users_tenant_email ON users(tenant_id, email);

General Database Scaling Strategies

Beyond the multi-tenancy model, several other database scaling techniques are essential for an IdP user store.

• Indexing (Already Done): We've already added indexes on foreign keys (tenant_id) and high-cardinality columns used in lookups (email). This is the most important first step for performance.
• Replication (Read Replicas): You can create read-only copies of your database. Authentication writes (login attempts updating last_login_at) go to the primary database, while read-heavy operations (e.g., fetching user profile data for an application) can be directed to replicas. This separates read and write workloads.
• Sharding (Horizontal Partitioning): As the users table grows to billions of rows, a single database can become a bottleneck. Sharding partitions the table across multiple database servers. For our multi-tenant design, a natural sharding key is tenant_id. All data for a specific tenant resides on the same shard, which maintains data locality and simplifies queries.

A key principle here is to design for sharding from the beginning. By including tenant_id in all primary keys (or at least in all queries), you make it much easier to introduce sharding later without a major application rewrite.

Conclusion

In this lesson, we have designed the architectural foundation of our Identity Provider: the user store. We addressed the three critical pillars of data modeling, security, and scalability.

Key Takeaways:

• Data Model: Start with a clear schema using surrogate primary keys (like UUIDs), appropriate constraints (UNIQUE, CHECK), and audit timestamps. Your database schema should enforce your business rules.
• Secure Credential Storage: Never store plaintext passwords. Use a modern, slow, salted password hashing algorithm like Argon2id. The password_hash field in your database stores the full output of the hashing function, including the salt and cost factors.
• Multi-Tenancy: For a scalable IdP, design for multi-tenancy from day one. The shared database/schema model with a tenant_id column is a common and effective pattern.
• Scaling: Proper indexing is non-negotiable. Plan for future growth by using a design that naturally supports replication and sharding (e.g., partitioning by tenant_id).

We have successfully designed a system to store user credentials securely and scalably. The next logical step is to design the system that uses these credentials.

Preview of the Next Lesson:

In our next lesson, we will address the learning outcome: "Architect the credential verification flow, including password hashing, MFA integration, and timing attack protection." We will build the login logic that takes a user's submitted password, securely compares it against the stored hash, and integrates Multi-Factor Authentication (MFA) to create a robust, layered authentication process.