Restgresql is a REST API server I've written in C++ and the mongoose library which provides a HTTPS webserver to retrieve website assets from a PostgreSQL database.
The following packages are required to build restgresql.
sudo apt-get install cmake libboost-all-dev libssl-dev libpqxx-dev libbz2-dev liblzma-dev libzstd-dev
Clone the repository with git
git clone https://github.com/ademarest/restgresql.git
Change directory into the restgresql folder.
cd restgresql
Create a build directory in the restgresql folder.
mkdir build
Change the working directory to the build directory.
cd build
Run the cmake command from the build directory.
cmake ../src/CMakeLists.txt
Compile the project with make in the build directory.
make
There will now be a runnable binary in the build directory which can be used to start the API server.
Install the postgresql and postgresql client packages for Ubuntu.
sudo apt-get install postgresql postgresql-client-common
Start and enable the PostgreSQL service
sudo systemctl enable --now postgresql
The default user account for PostgreSQL on Ubuntu is postgres. Sudo as this user to log into the database with the psql command line interface tool.
sudo -u postgres psql
Set a secure password of your choosing for the postgres user.
ALTER USER postgres with encrypted password 'yoursecurepasswordhere'
Exit psql
\q
After configuring the password, edit the file /etc/postgresql/*/main/pg_hba.conf to use scram-sha-256 as PostgreSQL's authentication method. The * in the aforementioned file path is the version of the PostgreSQL server. At the time of this documentation the version is 16, and the path is thus /etc/postgresql/16/main/pg_hba.conf.
Comment out these two lines, this will ensure postgres is only accessible via a password encrypted connection.
#local all postgres peer #local all all peer
Restart the PostgreSQL service
sudo systemctl restart postgresql
Log into the postgres server with the following command and enter the account password when prompted. This will connect to the localhost server via a SSL connection.
psql --host=127.0.0.1 --username=postgres --password
Execute the following command to create the production database.
CREATE DATABASE mysite;
Create the production schema for website content.
CREATE SCHEMA content;
Create a read-only user which will execute select queries by the restgresql.
CREATE ROLE restgresql WITH LOGIN PASSWORD 'yoursecurepasswordhere';
Grant connection access to the restgresql user.
GRANT CONNECT ON DATABASE mysite to restgresql;
Grant usage of the content schema to the restgresql user.
GRANT USAGE ON SCHEMA content to restgresql;
Grant the read only user permissions to query all tables in the content schema.
GRANT SELECT ON ALL TABLES IN SCHEMA content TO restgresql;
Next, alter the default privileges on the content schema to set the correct permissions on all tables which will be created in the following steps.
ALTER DEFAULT PRIVILEGES IN SCHEMA content GRANT SELECT ON TABLES TO restgresql;
Create the tables to hold markdown post data, game information, and images.
CREATE TABLE "content".games ( game_id int4 NOT NULL, game_path varchar NULL, CONSTRAINT games_pkey PRIMARY KEY (game_id) ); CREATE TABLE "content".images ( image_id int4 NOT NULL, image_file_name varchar NOT NULL, image bytea NOT NULL, CONSTRAINT images_image_file_name_key UNIQUE (image_file_name), CONSTRAINT images_pkey PRIMARY KEY (image_id) ); CREATE TABLE "content".post_categories ( post_category_id int4 NOT NULL, post_category varchar NULL, CONSTRAINT post_categories_pkey PRIMARY KEY (post_category_id) ); CREATE TABLE "content".posts ( post_id int4 NOT NULL, game_id int4 NULL, post_category_id int4 NULL, post_uid varchar NULL, post_title varchar NULL, post_subtext varchar NULL, post_preview_image_url varchar NULL, post_datetime timestamp DEFAULT now() NULL, post_author varchar NULL, post_markdown_content text NULL, post_js_resource_key varchar NULL, CONSTRAINT posts_pkey PRIMARY KEY (post_id) );
Create a view to simplify querying the data. PostgreSQL has a cool ability where the USING keyword will join tables on a common column.
CREATE OR REPLACE VIEW "content".vw_posts AS SELECT p.post_id, p.game_id, p.post_category_id, p.post_uid, p.post_title, p.post_subtext, p.post_preview_image_url, p.post_datetime, p.post_author, p.post_markdown_content, p.post_js_resource_key, g.game_path, pc.post_category FROM content.posts p LEFT JOIN content.games g USING (game_id) LEFT JOIN content.post_categories pc USING (post_category_id);
Some of the functions in the API take parameters from the URI to query the database. While the queries executed by restgresql are parameterized by the pqxx library, it's good practice to query via a function as functions cannot modify the database.
Create the functions which restgresql will query with the following commands.
CREATE OR REPLACE FUNCTION content.fn_get_image_by_id(prm_image_id integer) RETURNS SETOF content.images LANGUAGE sql AS $function$ select i.* from content.images i where i.image_id = prm_image_id $function$; CREATE OR REPLACE FUNCTION content.fn_get_image_by_filename(prm_image_file_name character varying) RETURNS SETOF content.images LANGUAGE sql AS $function$ select i.* from content.images i where image_file_name = prm_image_file_name $function$; CREATE OR REPLACE FUNCTION content.fn_get_post_by_id(prm_post_id integer) RETURNS SETOF content.vw_posts LANGUAGE sql AS $function$ select p.* from content.vw_posts p where p.post_id = prm_post_id $function$; CREATE OR REPLACE FUNCTION content.fn_get_recent_posts(prm_how_many_posts integer) RETURNS SETOF content.vw_posts LANGUAGE sql AS $function$ select p.* from content.vw_posts p order by p.post_datetime desc limit prm_how_many_posts; $function$; CREATE OR REPLACE FUNCTION content.fn_get_recent_articles(prm_how_many_articles integer) RETURNS SETOF content.vw_posts LANGUAGE sql AS $function$ select p.* from content.vw_posts p where p.post_category = 'article' order by p.post_datetime desc limit prm_how_many_articles; $function$; CREATE OR REPLACE FUNCTION content.fn_get_recent_projects(prm_how_many_projects integer) RETURNS SETOF content.vw_posts LANGUAGE sql AS $function$ select p.* from content.vw_posts p where p.post_category = 'project' order by p.post_datetime desc limit prm_how_many_projects; $function$;
The database is now configured for restgresql.
Create a restgresql.json configuration file in the restgresql executable working directory which contains the following information to reflect your setup.
Replace the key and cert to reflect the key and cert of your website and change the password to reflect your restgresql user's username and password.
{ "certPath": "/home/andrew/rsa/dev/dev.crt", "keyPath": "/home/andrew/rsa/dev/dev.key", "dbConnString": "dbname=mysite user=restgresql password=mypassword host=localhost port=5432" }
restgresql can now be started and it will serve data.