Skip to main content

Identity Bridge: Firebase Auth ↔ PostgreSQL

The Acctz platform uses Firebase Authentication as the identity provider and PostgreSQL as the authorization and data layer. This page documents how the two systems connect, the role of the iam.users bridge table, how Row Level Security is activated per-request, and how role permissions are resolved.

The Problem This Solves​

↑ Back to top

The Acctz UI authenticates users via Firebase Auth. All financial data lives in PostgreSQL. The identity bridge answers: how does a Firebase JWT become a PostgreSQL user context that RLS can enforce?

Firebase Auth issues a signed JWT identifying who the user is. PostgreSQL's Row Level Security enforces what data they can see — but only once the Node.js service sets app.current_user_id in the session. The bridge maps the Firebase UID to a local UUIDv7 and activates that context on every request.

Architecture​

↑ Back to top

Request Lifecycle​

↑ Back to top

Every authenticated API request follows this sequence:

1. Client Obtains Firebase JWT​

The React app authenticates via Firebase Auth (email/password or Google). Firebase returns a signed JWT containing:

{
  "sub": "firebase-uid-abc123",
  "email": "user@example.com",
  "name": "Jane Smith",
  "iss": "https://securetoken.google.com/acctz-project-id",
  "exp": 1735689600
}

The JWT is sent on every API request in the Authorization header:

Authorization: Bearer eyJhbGciOiJSUzI1NiIs...

2. Node.js Verifies the JWT​

An auth middleware intercepts the request, extracts the Bearer token, and verifies it using the Firebase Admin SDK. This validates the signature, issuer, expiration, and audience claims. No credentials ever reach the Node.js service -- only the signed token.

3. Resolve Firebase UID → PostgreSQL User​

The middleware extracts uid and email from the decoded token and queries:

SELECT id FROM iam.users WHERE firebase_uid = 'firebase-uid-abc123';

On first login, if no row exists, the backend creates one:

INSERT INTO iam.users (id, firebase_uid, email, display_name, status, last_login_at)
VALUES (gen_uuidv7(), 'firebase-uid-abc123', 'user@example.com', 'Jane Smith', 'active', now());

The id returned is a UUIDv7 -- this is the identity used for all subsequent operations.

4. Activate Row Level Security Context​

Before executing any business logic, the backend sets a session-local variable:

SET LOCAL app.current_user_id = '0192d4e0-7b1a-7000-8abc-def012345678';

SET LOCAL scopes the variable to the current database transaction. When the transaction commits or rolls back, the variable disappears. This means there is no risk of session bleed between requests in a connection pool.

5. RLS Filters Automatically​

Every tenant-scoped table has a policy like:

CREATE POLICY tenant_isolation ON ledger.accounts
    USING (entity_id IN (SELECT ledger.accessible_entity_ids()));

The accessible_entity_ids() function reads app.current_user_id and returns only the entity IDs that user can access through their organization memberships:

CREATE FUNCTION ledger.accessible_entity_ids() RETURNS SETOF UUID AS $$
    SELECT e.id FROM ledger.entities e
    JOIN iam.org_memberships m ON m.org_id = e.org_id
    WHERE m.user_id = current_setting('app.current_user_id', true)::UUID
      AND m.status = 'active';
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

The result: every SELECT, UPDATE, and DELETE is automatically filtered to the current user's organizations and entities. Application code does not need to add WHERE entity_id IN (...) clauses -- PostgreSQL enforces it.

The iam.users Bridge Table​

↑ Back to top

This table is the single point of contact between Firebase and PostgreSQL:

CREATE TABLE iam.users (
    id              UUID PRIMARY KEY,       -- UUIDv7, used everywhere in PostgreSQL
    firebase_uid    VARCHAR(128) UNIQUE,    -- Firebase Auth UID, used for JWT lookup
    email           VARCHAR(255) UNIQUE NOT NULL,
    display_name    VARCHAR(255),
    avatar_url      TEXT,
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Why a Bridge Table Instead of Using Firebase UID Directly?​

  1. Independence -- If the identity provider changes (Firebase → Auth0 → Keycloak), only the firebase_uid column needs to be remapped. Every other table references users.id.

  2. UUIDv7 consistency -- All PostgreSQL foreign keys use UUIDv7. Firebase UIDs are opaque strings (28-char base64). Mixing types would break index performance and complicate joins.

  3. Admin capability -- Admins can manage users in PostgreSQL (suspend, reassign roles) without touching Firebase. The database is the authority on what a user can do; Firebase only knows who they are.

  4. Audit trail -- The audit.audit_log.user_id references the PostgreSQL UUID, making audit queries simple joins instead of cross-system lookups.

Organization → Entity → Data Flow​

↑ Back to top

Once a user is authenticated, the data access chain is:

A user can be a member of multiple organizations (e.g., an accountant managing several clients). Each organization can have multiple entities (e.g., separate books for different business units). RLS ensures a user only sees entities belonging to organizations where they have an active membership.

Role Resolution​

↑ Back to top

After identifying the user, the application layer checks permissions before executing write operations:

User → org_memberships → role_id → role_permissions → (resource, action)

For example, can user uuid-abc approve a journal entry in org uuid-org1?

SELECT 1 FROM iam.org_memberships m
JOIN iam.role_permissions rp ON rp.role_id = m.role_id
WHERE m.user_id = 'uuid-abc'
  AND m.org_id = 'uuid-org1'
  AND m.status = 'active'
  AND rp.resource = 'journal_entries'
  AND rp.action = 'approve';

If the query returns a row, the action is authorized. If not, the API returns 403 Forbidden.

RLS handles visibility (can you see it?). Role permissions handle capability (can you do something to it?).

Security Considerations​

↑ Back to top

ConcernMitigation
JWT tamperingFirebase Admin SDK verifies RSA signature against Google's public keys
Token expirationFirebase JWTs expire after 1 hour; the Admin SDK rejects expired tokens
Session bleed in connection poolSET LOCAL scopes the user ID variable to the current transaction only
Orphaned Firebase usersPeriodic sync job can reconcile Firebase user list with iam.users
RLS bypassThe application connects as a non-superuser role; only postgres superuser bypasses RLS
Direct database accesspgAdmin/psql connections without SET app.current_user_id see zero rows on RLS-protected tables

Further Reading​

↑ Back to top

Last updated on