database

Database Module

This module stores device sessions for the auth-middleware project in PostgreSQL. User accounts are owned by your application; this module only references them by email.

Requirements

• PostgreSQL 14+
• An existing users table whose email column is a PRIMARY KEY or has a UNIQUE constraint, so a foreign key can reference it

Installation

go get github.com/lucap9056/auth-middleware/database/v2

Usage

import "github.com/lucap9056/auth-middleware/database/v2"

userRef, err := database.WithUserEmailReference("users(email)")
if err != nil {
	return err
}

db, err := database.NewDatabase(dsn,
	database.WithAutoCreateSchema(true),
	userRef,
)
if err != nil {
	return err
}
defer db.Close()

Options

User Email Reference

Format: [<schema>.]<table>(<column>)[:<type>]

The reference is case-insensitive and normalized to lowercase. <type> must match the type of the referenced column.

Auto Create Schema

When enabled, NewDatabase creates the schema, which requires DDL privileges. When disabled, create the schema yourself using schema.Generate, or with the SQL below.

Schema

CREATE TABLE IF NOT EXISTS auth_user_devices (
    device_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_name TEXT NOT NULL,
    user_email TEXT NOT NULL,
    secret TEXT NOT NULL,
    generation INT NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_auth_user_devices FOREIGN KEY (user_email)
        REFERENCES users (email)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_auth_user_devices_user_email ON auth_user_devices(user_email);
CREATE INDEX IF NOT EXISTS idx_auth_user_devices_updated_at ON auth_user_devices(updated_at);

API