Catena - SQL on a blockchain

Catena is a distributed database based on a blockchain, accessible using SQL. Catena timestamps database transactions (SQL) in a decentralized way between nodes that do not or cannot trust each other, while enforcing modification permissions (‘grants’) that were agreed upon earlier.

A Catena blockchain contains SQL transactions that, when executed in order, lead to the agreed-upon state of the database.
The transactions are automatically replicated to, validated by, and replayed on participating clients. A Catena database
can be connected to by client applications using the PostgreSQL wire protocol (pq).

Only SQL statements that modify data or structure are included in the blockchain. This is very similar to replication logs
used by e.g. MySQL (‘binlog’).

Building

macOS

Catena builds on macOS. You need a recent version of XCode (>=8.3.2) on your system. Use the following commands to clone
the Catena repository and build in debug configuration:

git clone https://github.com/pixelspark/catena.git catena
cd catena
swift build

It is also possible to generate an XCode project and build Catena from it:

swift package generate-xcodeproj

Linux

Building on Linux is fully supported. To build, first ensure Swift 4 is installed and is in the PATH. Then
ensure clang and required libraries are present:

apt install clang build-essential libicu-dev libcurl4-openssl-dev openssl libssl-dev
git clone https://github.com/pixelspark/catena.git catena
cd catena
swift build

The above was tested on Ubuntu 16.04 as well as Debian Stretch with Swift 4.

Note: Due to the fact that there is no cross-platform WebSocket client implementation in Swift
(the current implementation uses Starscream), outgoing peer connections are not supported for the Linux client.
Incoming peer connections are possible and can be used by the client to ‘talk back’, so the client is functional
regardless.

Docker

A docker image is available at the Docker Hub. To build the
Docker image from source (useful if you don’t have / can’t install Swift locally, for instance):

git clone https://github.com/pixelspark/catena ./catena
cd ./catena
docker build -t pixelspark/catena .

Building the web client

Catena includes a web client, resources from the Resources/ folder. A built version of the web client is
included. To build from scratch, first install the required build tools, then use gulp to compile and bundle files:

cd Resources/
npm install
gulp

Use gulp watch to have gulp recompile files on change.

Running

Natively

The following command starts Catena and initializes a new chain (replace ‘debug’ with ‘release’ after building
a release version):

./.build/debug/Catena -p 8338 -m -i -s 'my seed string'

The -i switch tells Catena to initialize a chain (this deletes any persisted data, which is stored by default in catena.sqlite in the current directory). The -s switch provides Catena with a string that tells it which genesis block to accept. To enable block mining, add the ‘-m’ command line switch.

To start another peer locally, use the following:

./.build/debug/Catena -p 8340 -s 'my seed string' -j ws://[email protected]:8338 -d peer2.sqlite

Note, the node URL in the command above can be copied from the output of the first node. Only one node is required in order to bootstrap (nodes will perform peer exchange).

Running with Docker

docker pull pixelspark/catena
docker run -p 8338:8338 -p 8339:8339 pixelspark/catena [ARGS]

Note: the port number on which Catena listens inside the container must be equal to the port number used outside the
container (as Catena advertises it to peers).

Using Catena

Web client

Catena provides a web interface on port 8338 (default), which can be used for introspecting the blockchain and performing queries. It also provides a WebSocket service (on the same port) which is used for communication between peers. Provide the --no-web-client command-line option to disable the web client and associated API (note that the currently exposed API does not allow for any mutating actions).

SQL interface

The (private) SQL interface is available on port 8339 (by default). If you set a different HTTP port (using the ‘-p’ command line switch), the SQL interface will assume that port+1. You can connect to the SQL interface using the PostgreSQL command line client:

psql -h localhost -p 8334 -U random

Your username should be the public key (generated by Catena) and your password the private key. Catena will print the public
and private key for the ‘root’ user when initializing a new chain (with the -i option) and will also print a psql command line for
you to use to connect as root.

Permissions

By default, any user can execute a CREATE DATABASE statement; this creates a database, of which the invoker becomes the owner. The invoker can subsequently use GRANT and REVOKE statements to grant other users rights on the database.

More granular permissions can be granted by using template grants. These grant a user the permission to execute
a certain parametrized query with self-chosen parameter values. The template query is hashed and stored in the
grants table. Any query whose hash (once parameter values removed) matches that of a template granted to the
user can be executed. Template grants can be most easily created through the web client.

The template grant can further restrict the set of allowable parameter values using an IF-statement:

IF ?amount > 0 THEN UPDATE balance SET balance = balance + ?amount WHERE iban = ?iban ELSE FAIL END;

FAQ

Is Catena a drop-in replacement for a regular SQL database?

No. The goal of Catena is to make it as easy as possible for developers and administrators that are used to working with
SQL to adopt blockchain technology. Catena supports the PostgreSQL (pq) wire protocol to submit queries, which allows
Catena to be used from many different languages (such as PHP, Go, C/C++). However, there are fundamental differences
between Catena and ‘regular’ database systems:

• Catena currently does not support many SQL features.
• Catena’s consistency model is very different from other databases. In particular, any changes you make are not immediately visible nor confirmed. Transactions may roll back at any time depending on which transactions are included in the ‘winning’ blockchain.
• Catena will (in the future) check user privileges when changing or adding data, but can never prevent users from seeing all data (all users that are connected to a Catena blockchain can ‘see’ all transactions). Of course it is possible to set up a private chain.

Which SQL features are supported by Catena?

Catena supports a limited subset of SQL (Catena implements its own SQL parser to sanitize and canonicalize SQL queries).
Currently, the following types of statements are supported:

• CREATE TABLE foo (bar TEXT, baz INT);
• INSERT INTO table (x, y, z) VALUES (‘text’, 1337);
• SELECT DISTINCT *, x, y, ‘value’, 123 FROM table LEFT JOIN other ON x=y WHERE x=10;
• DELETE FROM foo WHERE x=10;
• UPDATE foo SET bar=baz WHERE bar=1;
• DROP TABLE foo;

What kind of blockchain is implemented by Catena?

Catena uses a Blockchain based on SHA-256 hashes for proof of work, with configurable difficulty. Blocks contain
transactions which contain SQL statements. Catena is written from scratch and is therefore completely different from
Bitcoin, Ethereum etc.

How does a Catena node talk to other nodes?

Catena nodes expose an HTTP/WebSocket interface. A node connects to the WebSocket interface of all other nodes it knows
about (initially specified from the command line) to fetch block information and exchange peers. In order for two nodes
to be able to communicate, at least one must be able to accept incoming connections (i.e. not be behind NAT or firewall).

What is the consistency model for Catena?

SQL statements are grouped in transactions, which become part of a block. Once a block as been accepted in the blockchain and
is succeeded by a sufficient number of newer blocks, the block has become an immutable part of the blockchain ledger.

As new blocks still run the risk of being ‘replaced’ by competing blocks that have been mined (which may or may not include
a recent transaction), the most recent transactions run the risk of being rolled back.

How are changes to a Catena blockchain authenticated?

Transactions are required to be signed with a private key.

A transaction that modifies any table or row needs to be signed with a private key that has the privilege to modify that specific table or row. Privilege grants are stored in a special ‘grants’ table (which, in turn, can be modified by those that have a grant to modify that table).

To prevent replay of signed transactions, Catena stores a transaction counter for each public key, which is atomically incremented for every transaction that is executed. A transaction will not execute (again) if it has a lower transaction number than the latest number recorded in the blockchain. To successfully submit a transaction, the invoker must increment the counter by one for each transaction (or use a different key each time).

Where does the name come from?

Catena is Italian for ‘chain’.

Can I run a private Catena chain?

Chains are identified by their genesis (first) block’s hash. To create a private chain, use the ‘-s’ option to specify a different starting seed.

MIT license

Copyright (c) 2017 Pixelspark, Tommy van der Vorst

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Contributing

We welcome contributions of all kinds - from typo fixes to complete refactors and new features. Just be sure to contact us if you want to work on something big, to prevent double effort. You can help in the following ways:

• Open an issue with suggestions for improvements
• Submit a pull request (bug fix, new feature, improved documentation)

Note that before we can accept any new code to the repository, we need you to confirm in writing that your contribution is made available to us under the terms of the MIT license.