3 files changed, 199 insertions, 0 deletions
diff --git a/app/src/lib/server/db.ts b/app/src/lib/server/db.ts
new file mode 100644
index 0000000..dcfcfd0
--- /dev/null
+++ b/app/src/lib/server/db.ts
@@ -0,0 +1,20 @@
+import pg from "pg";
+import { env } from "$env/dynamic/private";
+
+const pool = new pg.Pool({
+	database: env.POSTGRES_DB || "postgres",
+	user: env.POSTGRES_USERNAME || "postgres",
+	host: env.POSTGRES_HOST || "localhost",
+	port: Number(env.POSTGRES_PORT || 5432),
+});
+
+// Prevent errors in idle clients from terminating Node process.
+// See: https://node-postgres.com/features/pooling
+// See: https://nodejs.org/api/events.html#error-events
+pool.on("error", (err, client) => {
+	console.error("Database error: ", err, client);
+});
+
+export function getDbConnection(): Promise<pg.PoolClient> {
+	return pool.connect();
+}
diff --git a/app/src/lib/server/sessions.ts b/app/src/lib/server/sessions.ts
new file mode 100644
index 0000000..1b829ff
--- /dev/null
+++ b/app/src/lib/server/sessions.ts
@@ -0,0 +1,141 @@
+import type pg from "pg";
+import type { User } from "./users";
+
+export type SessionValidationResult =
+	| { session: Session; user: User }
+	| { session: null; user: null };
+
+export interface Session {
+	token: string;
+	userId: number;
+	expiresAt: Date;
+}
+
+/** Generates a random string which can be used as a session token. */
+function generateSessionToken(): string {
+	const bytes = new Uint8Array(20);
+	crypto.getRandomValues(bytes);
+	const token = encodeBase32LowerCaseNoPadding(bytes);
+	return token;
+}
+
+function encodeBase32LowerCaseNoPadding(input: Uint8Array): string {
+	const alphabet = "abcdefghijklmnopqrstuvwxyz234567"; // Lowercase alphabet
+	let output = "";
+	let bits = 0;
+	let buffer = 0;
+
+	for (const byte of input) {
+		buffer = (buffer << 8) | byte;
+		bits += 8;
+
+		while (bits >= 5) {
+			output += alphabet[(buffer >> (bits - 5)) & 0x1f];
+			bits -= 5;
+		}
+	}
+
+	// Handle remaining bits (if any) - No padding, so just add if > 0.
+	if (bits > 0) {
+		output += alphabet[(buffer << (5 - bits)) & 0x1f];
+	}
+
+	return output;
+}
+
+/**
+ * The amount of milliseconds since last access, a session remains valid for when it is created.
+ *
+ * It is currently set to 30 days.
+ */
+const VALID_MILLISECONDS = 1000 * 60 * 60 * 24 * 30;
+
+/** Creates a new session for the user with the given `userId`. */
+export async function createSession(dbConn: pg.PoolClient, userId: number): Promise<Session> {
+	const token = generateSessionToken();
+	const session: Session = {
+		token: token,
+		userId,
+		expiresAt: new Date(Date.now() + VALID_MILLISECONDS),
+	};
+	await dbConn.query("INSERT INTO sessions(token, user_id, expires_at) VALUES ($1, $2, $3);", [
+		session.token,
+		session.userId,
+		session.expiresAt,
+	]);
+	return session;
+}
+
+/**
+ * Validates a session token. It happens in two parts:
+ *
+ * 1. Does a token exist?
+ * 2. Is that token NOT expired?
+ *
+ * As a side effect, the session is touched (i.e. it's expiration is delayed) if validation suceeds.
+ * This is to avoid unnecessary logouts, as session validation indicates that the session is still in use.
+ *
+ * @returns A session + user pair if the session is valid, or `null` for both otherwise.
+ */
+export async function validateSessionToken(
+	dbConn: pg.PoolClient,
+	token: string,
+): Promise<SessionValidationResult> {
+	// Step 1
+	const result = await dbConn.query(
+		`SELECT * FROM sessions
+		 INNER JOIN users ON users.id = sessions.user_id
+		 WHERE token = $1;`,
+		[token],
+	);
+	console.debug(result);
+	if (result.rowCount === 0) {
+		return { session: null, user: null };
+	}
+
+	const session: Session = {
+		token,
+		userId: result.rows[0].user_id,
+		expiresAt: result.rows[0].expires_at,
+	};
+	const user: User = {
+		id: result.rows[0].id, // Luckily JOIN avoids collision...
+		email: result.rows[0].email,
+		firstName: result.rows[0].first_name,
+		lastName: result.rows[0].last_name,
+		role: result.rows[0].role,
+	};
+	console.debug("Session with token %s: %o, %o", token, session, user);
+
+	// Step 2.
+	const now = Date.now();
+	if (now >= session.expiresAt.getTime()) {
+		await invalidateSession(dbConn, session.token);
+		return { session: null, user: null };
+	}
+
+	// "Touch" the session, if it is about to expire.
+	// We only do this a bit into the period to avoid superflous database writes.
+	if (now >= session.expiresAt.getTime() - VALID_MILLISECONDS / 2) {
+		session.expiresAt = new Date(session.expiresAt.getTime() + VALID_MILLISECONDS / 2);
+		await dbConn.query("UPDATE sessions SET expires_at = ? WHERE id = ?;", [
+			session.expiresAt,
+			session.token,
+		]);
+	}
+
+	return { session, user };
+}
+
+/** Invalidates the session with token `sessionToken`. */
+export async function invalidateSession(
+	dbConn: pg.PoolClient,
+	sessionToken: string,
+): Promise<void> {
+	await dbConn.query("DELETE sessions WHERE token = ?;", [sessionToken]);
+}
+
+/** Invalidates all sessions for the user with the id `userId`. */
+export async function invalidateAllSessions(dbConn: pg.PoolClient, userId: number): Promise<void> {
+	await dbConn.query("DELETE sessions WHERE user_id = ?", [userId]);
+}
diff --git a/app/src/lib/server/users.ts b/app/src/lib/server/users.ts
new file mode 100644
index 0000000..c233fda
--- /dev/null
+++ b/app/src/lib/server/users.ts
@@ -0,0 +1,38 @@
+import type pg from "pg";
+
+/** A row from the `users` column. */
+export interface User {
+	id: number;
+	email: string;
+	firstName: string;
+	lastName: string;
+	role: "gardener" | "owner";
+}
+
+/**
+ * Retrieves a user by their email and password.
+ *
+ * The password should be passed unaltered. All validation is done on the DB server.
+ *
+ * @returns The user, or `undefined` on authentication failure.
+ */
+export async function getUser(
+	dbConn: pg.PoolClient,
+	email: string,
+	password: string,
+): Promise<User | undefined> {
+	let result = await dbConn.query(
+		"SELECT * FROM users WHERE email = $1 AND password_hash = crypt($2, password_hash);",
+		[email, password],
+	);
+	if (result.rowCount == 0) {
+		return undefined;
+	}
+	return {
+		id: result.rows[0].id,
+		email: result.rows[0].email,
+		firstName: result.rows[0].first_name,
+		lastName: result.rows[0].last_name,
+		role: result.rows[0].role,
+	};
+}