🦄🐘uniphant

1. About
2. Dependencies
3. Installation
   1. Running Ubuntu in VirtualBox
   2. Ubuntu 20.04.1
   3. Setting up SSL/TLS using Let's Encrypt/Certbot

1. About

uniphant is a full-stack demo project on how to integrate various PostgreSQL-centric components to work nicely together.

For a live demo of the latest released version, check out https://uniphant.dev.

2. Dependencies

🔐🐘webauthn for the WebAuthn Server.

PostgREST for the API.

3. Installation

3.1. Running Ubuntu in VirtualBox

1. Download and install VirtualBox
2. Download ubuntu-20.04.1-live-server-amd64.iso
3. Open VirtualBox
4. Click New and follow instructions
5. Click Settings and goto Network, Attached to: NAT, Click Port Forwarding
6. Add Host Port 2200 Guest Port 22, leave Host IP and Guest IP blank
7. Add Host Port 8080 Guest Port 80, leave Host IP and Guest IP blank
8. Click OK, Click OK
9. Click Start
10. Select ubuntu-20.04.1-live-server-amd64.iso
11. Follow instructions
12. To simplify logging in via SSH, you can install your GitHub user's SSH key. (optional)
    1. Install OpenSSH server: YES
    2. Import SSH identity: from GitHub
    3. GitHub username: [Enter your GitHub username]

3.2. Ubuntu 20.04.1

Connect to your VirtualBox machine, assuming the forwarded Host Port is 2200 and the username is uniphant:

ssh -p 2200 uniphant@localhost

The following exact step-by-step instructions assume a clean installation of Ubuntu.

# postgresql:
sudo apt-get -y dist-upgrade
sudo locale-gen "en_US.UTF-8"
sudo update-locale LC_ALL=en_US.UTF-8 LANG=en_US.UTF-8
sudo apt-get -y install gnupg
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql postgresql-server-dev-13 build-essential
sudo service postgresql start
sudo -u postgres createuser -s "$USER"
createdb -E UTF8 uniphant
createuser api -L -s
createuser web_anon -L
createuser postgrest -I
# pg-cbor:
git clone https://github.com/truthly/pg-cbor.git
(cd pg-cbor && make && sudo make install && make installcheck)
# Follow instructions at: https://github.com/joelonsql/pg_ecdsa_verify
# pg-webauthn:
git clone https://github.com/truthly/pg-webauthn.git
(cd pg-webauthn && make && sudo make install && make installcheck)
# uniphant:
git clone https://github.com/truthly/uniphant.git
cd uniphant || exit
(make && sudo make install && make installcheck)
psql -c "CREATE EXTENSION uniphant WITH SCHEMA public CASCADE" uniphant
# postgrest:
wget --quiet https://github.com/PostgREST/postgrest/releases/download/v7.0.1/postgrest-v7.0.1-linux-x64-static.tar.xz
tar xvf postgrest-v7.0.1-linux-x64-static.tar.xz
sudo cp postgrest /bin/postgrest
sudo mkdir -p /etc/postgrest
sudo cp postgrest.conf /etc/postgrest/config
sudo cp postgrest.service /etc/systemd/system/postgrest.service
sudo adduser --system --no-create-home postgrest
sudo systemctl enable postgrest
sudo systemctl start postgrest
# nginx:
sudo apt-get -y install nginx-light
sudo rm /etc/nginx/sites-enabled/default
sudo cp nginx.conf /etc/nginx/sites-available/uniphant
sudo ln -s /etc/nginx/sites-available/uniphant /etc/nginx/sites-enabled/uniphant
sudo ln -s "$HOME/uniphant/demo" /var/www/html/uniphant
sudo systemctl restart nginx

Installation complete.

You can now try a sign-up using curl from the command line:

curl 'http://localhost/api/rpc/sign_up' \
  -H 'Content-Type: application/json;charset=utf-8' \
  --data '{"username":"test"}' \
  -w "\n"

Use psql to check the content of the users table which should now contain one row:

psql -x uniphant

SELECT * FROM users;

-[ RECORD 1 ]------+-----------------------------------------------------------------------------------------------------------------------------------
user_id            | 1
user_random_id     | \x4e271a61903586427357f17d4e8e3c1c6a1512a6d6ce3d4de5748c9e15d0bb278e507f0df9911ea5c0d3b7bb159065eb867b5ac68acf92a649c293437fbe3410
username           | test

This is how far we get testing in the command line.

To complete a real sign-up and sign-in, we need to use a real browser, since the private/public key pairs can't be easily generated from the command line, since they depend on an Authenticator device, either built-in TouchID/FaceID, or an external device like a Yubikey.

To do so, browse to http://localhost:8080 to test a real sign-up and sign-in.

Note: Only works with Chrome and Safari. Firefox is currently not supported due to a bug.

Setting up SSL/TLS using self-signed cert

This is useful when tesing locally.

sudo openssl req -x509 -nodes -days 365 -newkey rsa:2048 -subj "/CN=uniphant" -keyout /etc/ssl/private/self-signed.key -out /etc/ssl/certs/self-signed.crt sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 128 sudo cp self-signed.conf /etc/nginx/snippets/self-signed.conf

Setting up SSL/TLS using Let's Encrypt/Certbot

sudo snap install core; sudo snap refresh core
sudo snap install --classic certbot
sudo ln -s /snap/bin/certbot /usr/bin/certbot
sudo certbot --nginx

Setting up pg_listen to reload schema automatically

# pg_listen:
git clone https://github.com/begriffs/pg_listen.git
cd pg_listen || exit
export PKG_CONFIG_PATH=~/postgresql/src/interfaces/libpq
make
cp pg_listen /usr/local/bin/
cp postgrest-reload-schema.sh /usr/local/bin/
pg_listen postgres://postgrest@/uniphant ddl_command_end $(which postgrest-reload-schema.sh)