db.h File Reference

Opsick DB interaction functions. More...

#include <stddef.h>
#include <stdint.h>
#include <libpq-fe.h>
#include "user.h"

Include dependency graph for db.h:

Go to the source code of this file.

Functions
int	opsick_db_init (const char *dbconn_filepath)
PGconn *	opsick_db_connect ()
void	opsick_db_disconnect (PGconn *dbconn)
uint64_t	opsick_db_get_schema_version_number ()
uint64_t	opsick_db_get_last_used_userid ()
void	opsick_db_last_128_bytes_of_ciphertext (uint8_t out[128])
uint64_t	opsick_db_get_last_db_schema_version_nr_lookup ()
int	opsick_db_does_user_id_exist (PGconn *dbconn, uint64_t user_id)
int	opsick_db_create_user (PGconn *dbconn, const char *pw, uint64_t exp_utc, const char *public_key_ed25519, const char *encrypted_private_key_ed25519, const char *public_key_curve448, const char *encrypted_private_key_curve448, uint64_t *out_user_id)
int	opsick_db_delete_user (PGconn *dbconn, uint64_t user_id)
uint64_t	opsick_db_count_users (PGconn *dbconn)
int	opsick_db_get_user_metadata (PGconn *db, uint64_t user_id, struct opsick_user_metadata *out_user_metadata)
int	opsick_db_set_user_pw (PGconn *dbconn, uint64_t user_id, const char *new_pw)
int	opsick_db_set_user_totps (PGconn *dbconn, uint64_t user_id, const char *new_totps)
int	opsick_db_get_user_body (PGconn *dbconn, uint64_t user_id, char **out_body, size_t *out_body_length)
int	opsick_db_set_user_body (PGconn *dbconn, uint64_t user_id, const char *body)
int	opsick_db_set_user_exp (PGconn *dbconn, uint64_t user_id, uint64_t new_exp)
int	opsick_db_set_user_keys (PGconn *dbconn, uint64_t user_id, const char *new_pubkey_ed25519, const char *new_prvkey_ed25519, const char *new_pubkey_curve448, const char *new_prvkey_curve448)
void	opsick_db_free ()

Detailed Description

Opsick DB interaction functions.

Author

Raphael Beck

Function Documentation

opsick_db_connect()

PGconn * opsick_db_connect

(

)

Connects to the opsick db.

Returns

NULL if connection couldn't be established; the postgres connection reference otherwise.

opsick_db_count_users()

uint64_t opsick_db_count_users

(

PGconn *

dbconn

)

Counts the total amount of users stored in the db.

Parameters

dbconn

Postgres db connection reference to use for the query.

Returns

The total number of users currently in the db.

opsick_db_create_user()

int opsick_db_create_user	(	PGconn *	dbconn,
		const char *	pw,
		uint64_t	exp_utc,
		const char *	public_key_ed25519,
		const char *	encrypted_private_key_ed25519,
		const char *	public_key_curve448,
		const char *	encrypted_private_key_curve448,
		uint64_t *	out_user_id
	)

Adds a new user to the DB.

Parameters

dbconn	Postgres db connection reference to use for the query.
pw	The user's password (hashed).
exp_utc	When the user expires (UTC).
public_key_ed25519	The user's public Ed25519 key.
encrypted_private_key_ed25519	The user's encrypted private Ed25519 key.
public_key_curve448	The user's public Curve448 key.
encrypted_private_key_curve448	The user's encrypted private Curve448 key.
out_user_id	Where to write the ID of the freshly created user into.

Returns

0 on success; error code in case of a failure.

opsick_db_delete_user()

int opsick_db_delete_user	(	PGconn *	dbconn,
		uint64_t	user_id
	)

Deletes a user from the DB.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	The user ID.

Returns

0 on success; 1 if the user was not found or deletion from db failed for some other unknown reason.

opsick_db_disconnect()

void opsick_db_disconnect

(

PGconn *

dbconn

)

Disconnects from the opsick db.

Parameters

dbconn

The postgres connection handle to disconnect.

opsick_db_does_user_id_exist()

int opsick_db_does_user_id_exist	(	PGconn *	dbconn,
		uint64_t	user_id
	)

Checks whether a given user id exists or not.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	The user id to check.

Returns

0 if the user does not exist in the db; 1 if it does exist.

opsick_db_free()

void opsick_db_free

(

)

This frees all the related resources.

opsick_db_get_last_db_schema_version_nr_lookup()

uint64_t opsick_db_get_last_db_schema_version_nr_lookup

(

)

When was the last time somebody checked the db schema version number?

Returns

UTC timestamp of the last schema version lookup.

opsick_db_get_last_used_userid()

uint64_t opsick_db_get_last_used_userid

(

)

Gets the id of the last active user.

Returns

User ID of the last user who interacted with the server.

opsick_db_get_schema_version_number()

uint64_t opsick_db_get_schema_version_number

(

)

Gets the current DB schema version number (via a SELECT statement).

This number is increased with every DB schema migration.

Returns

The current db schema version number.

opsick_db_get_user_body()

int opsick_db_get_user_body	(	PGconn *	dbconn,
		uint64_t	user_id,
		char **	out_body,
		size_t *	out_body_length
	)

Retrieves a user's body from the db.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	User id.
out_body	Pointer to an output body string that will contain the retrieved user body (will be left untouched if the user couldn't be found). This will be malloc'ed on success, so don't forget to free()!
out_body_length	[OPTIONAL] Where to write the output body length into (can be NULL if you don't need it).

Returns

0 on success; 1 if the user was not found or fetch from db failed.

opsick_db_get_user_metadata()

int opsick_db_get_user_metadata	(	PGconn *	db,
		uint64_t	user_id,
		struct opsick_user_metadata *	out_user_metadata
	)

Retrieves a user's metadata from the db.

Parameters

db	Postgres db connection reference to use for the query.
user_id	The user ID.
out_user_metadata	Where to write the found metadata into (this will be left alone if the user wasn't found)

Returns

0 on success; 1 if the user was not found or fetch from db failed for some other unknown reason.

opsick_db_init()

int opsick_db_init

(

const char *

dbconn_filepath

)

Initializes the db client, connecting to sqlite and setting up everything that's needed to query the database.

This terminates opsick with a status code of -1 in case of a failure!

Parameters

dbconn_filepath

The full path to the (hopefully well-protected) text file containing the postgres connection string to use for Opsick. Ideally, you'd chown this to the Opsick process owner and chmod this to 400 (read-only, and only by the file owner and no one else).

Returns

1 if the db initialization succeeded; 0 if it failed (e.g. bad connection).

opsick_db_last_128_bytes_of_ciphertext()

void opsick_db_last_128_bytes_of_ciphertext

(

uint8_t

out[128]

)

Gets the last 128B of trafficked ciphertext.

Returns

opsick_db_set_user_body()

int opsick_db_set_user_body	(	PGconn *	dbconn,
		uint64_t	user_id,
		const char *	body
	)

Updates a user's body in the db.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	User id.
body	The new body to write into the db.

Returns

0 on success; non-zero on failure.

opsick_db_set_user_exp()

int opsick_db_set_user_exp	(	PGconn *	dbconn,
		uint64_t	user_id,
		uint64_t	new_exp
	)

Sets a new expiration datetime (UTC) to a user in the db.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	ID of the user whose expiration date needs to be changed.
new_exp	The new UTC timestamp of when the user account will become read-only.

Returns

0 on success; non-zero on failure.

opsick_db_set_user_keys()

int opsick_db_set_user_keys	(	PGconn *	dbconn,
		uint64_t	user_id,
		const char *	new_pubkey_ed25519,
		const char *	new_prvkey_ed25519,
		const char *	new_pubkey_curve448,
		const char *	new_prvkey_curve448
	)

Updates a user's key pairs in the db.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	User id.
new_pubkey_ed25519	The new ed25519 public key (NUL-terminated C-string).
new_prvkey_ed25519	The new ed25519 encrypted private key (NUL-terminated C-string).
new_pubkey_curve448	The new curve448 public key (NUL-terminated C-string).
new_prvkey_curve448	The new curve448 encrypted private key (NUL-terminated C-string).

Returns

0 on success; non-zero on failure.

opsick_db_set_user_pw()

int opsick_db_set_user_pw	(	PGconn *	dbconn,
		uint64_t	user_id,
		const char *	new_pw
	)

Changes a user's password in the db.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	User ID whose password you want to change.
new_pw	The new pw hash.

Returns

0 on success; 1 on failure.

opsick_db_set_user_totps()

int opsick_db_set_user_totps	(	PGconn *	dbconn,
		uint64_t	user_id,
		const char *	new_totps
	)

Changes a user's TOTPS (TOTP secret for 2FA) in the db.

Parameters

dbconn	Postgres db connection reference to use for the query.
user_id	User ID whose TOTPS you want to change.
new_pw	The new TOTPS (base32 encoded).

Returns

0 on success; 1 on failure.