26 lines
832 B
SQL
26 lines
832 B
SQL
-- ©AngelaMos | 2026
|
|
-- 007_credentials.sql
|
|
|
|
-- +goose Up
|
|
CREATE TABLE credentials (
|
|
id BIGSERIAL,
|
|
session_id TEXT NOT NULL,
|
|
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
service_type service_type NOT NULL,
|
|
source_ip TEXT NOT NULL,
|
|
username TEXT NOT NULL,
|
|
password TEXT NOT NULL DEFAULT '',
|
|
public_key TEXT,
|
|
auth_method TEXT NOT NULL DEFAULT 'password',
|
|
success BOOLEAN NOT NULL DEFAULT FALSE,
|
|
PRIMARY KEY (id, timestamp)
|
|
) PARTITION BY RANGE (timestamp);
|
|
|
|
CREATE INDEX idx_creds_source_ip ON credentials (source_ip);
|
|
CREATE INDEX idx_creds_username ON credentials (username);
|
|
CREATE INDEX idx_creds_session ON credentials (session_id);
|
|
CREATE INDEX idx_creds_timestamp ON credentials (timestamp DESC);
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS credentials;
|