Table Overview
Space and Time has many types of tables, covering many use-cases.
Space and Time allows for many different choices when creating tables, to provide greate flexibility on how best to satisfy your use-case. This guide will outline the type of tables available, which work together, the variety of ways in which they are secured, and some example use-cases.
For reference, a simple example of a table in Space and Time may look like:
CREATE TABLE SXTTEMP.EXAMPLE_TABLE_000123
( MyData_ID INTEGER
, MyData_Name VARCHAR
, MyData_Date DATE
, MyData_TS. TIMESTAMP
, PRIMARY KEY (MyData_ID)
)
WITH "public_key=$RESOURCE_PUBLIC_KEY"
There are four ways to assign options / permissions to Tables in the Space and Time Database:
- Table Creation Settings - permissions or options granted directly to the table during table creation, and are applicable to everyone on the network, regardless of any other option types
- Authentication (Ledger Tables) - permissions or options granted by nature of your UserID or Subscription
- Authorization - permissions or options granted by biscuits (decentralized authorization via a self-custoded bearer token that the owner of the table can create and share)
- Column Encryption - Options on encrypting column data
Table Options: TABLE CREATION SETTINGS
Tables in Space and Time Database can be set with default permissions at table create time (DDL execution), that are granted to everyone on the network by default, regardless of other permission types. Because these permissions are so widely applied, it's important to understand and set appropriately to your use-case.
TABLE CREATION SETTING: Access_Type
Access type defines the global default access type for the table or view. For example, you can make the table globally public or private. To set the default table permission, you add the property access_type=
to the WITH
statement during table creation. If omitted, SxT will default to "Permissioned" (aka Private) which is the most restrictive default permission.
-
PERMISSIONED (default setting) - No access is provided by default, and every request must include a biscuit authorization. If not set, your table will default to a Permissioned table.
- Syntax: modify your
WITH
statement to:
WITH "public_key=$RESOURCE_PUBLIC_KEY, access_type=PERMISSIONED"
(or omitaccess_type
) - Use-Cases: private or internal organizational data, such as CRM data, private application logs, organizational master data, user analytics, etc.
- Syntax: modify your
-
PUBLIC_READ - Everyone on the network will have
SELECT
access by default, allowing anyone on the network to run queries on the data. For example, all SxT Blockchain tables are PUBLIC_READ.- Syntax: modify your
WITH
statement to:
WITH "public_key=$RESOURCE_PUBLIC_KEY, access_type=PUBLIC_READ"
- Use-Cases: publicly accessible data like weather data, stock or blockchain data, social feeds, test data, or anonymized data for educational or hackathon purposes.
- Syntax: modify your
-
PUBLIC_APPEND - Everyone on the network will have
SELECT,INSERT
permissions by default, allowing anyone on the network to query OR insert new data. Only users with the appropriate biscuit authorization can update or delete data, or modify the table's structure. This is sometimes used as a public (append-only) logging mechanism, although it's important to note that anyone on the network can add any number of new records.- Syntax: modify your
WITH
statement to:
WITH "public_key=$RESOURCE_PUBLIC_KEY, access_type=PUBLIC_APPEND"
- Use-Cases: Rare, but some publicly accessible logs or anonymized data collection points.
- Syntax: modify your
-
PUBLIC_WRITE - Everyone on the network will have
SELECT,INSERT,UPDATE,DELETE
permissions by default, allowing anyone on the network to query, add, delete, or alter any data in the table. Only users with the appropriate biscuit authorization can modify the table or view's structure. This is often used by Space and Time in demo projects, as it allows easy and open access to try SxT features, without needing to worry about biscuits. Because literally anyone can do anything to the data, this is not typically used outside of demos.- Syntax: modify your
WITH
statement to:
WITH "public_key=$RESOURCE_PUBLIC_KEY, access_type=PUBLIC_WRITE"
- Use-Cases: Publically accessible temporary data caches (insert / process / delete), or test-dev tables , or for simplified demos of data processes (where data quality doesn't matter)
- Syntax: modify your
TABLE CREATION SETTING: Tamperproof vs Optimistic
Space and Time allows users to make tables either Optimistic or Tamperproof:
- Optimistic tables (default table setting) are regular SQL tables and support the full range of data types and SQL operations, however will not work with Proof of SQL tamperproof SQL. Because of the Optimistic nature of the table, they will always be served by trusted / permissioned nodes on the Space and Time network. Optimistic tables are typically mutable, but can also be immutable.
- Tamperproof tables are special immutable and cryptography tamperproof tables that must conform to Proof of SQL standards in order to provide subsecond prover times. These temporary cryptographic constraints somewhat limit data types and SQL functions today, however (a) Proof of SQL is constantly being updated to cover a more of SQL, and even in it's current state, unlocks massive value for zk-trustless use-cases like smart contracts.
It is possible to run optimistic queries on tamperproof tables, but you cannot run tamperproof queries on optimistic tables.
Creating Tamperproof Tables
Below walks through requirements when creating a tamperproof table. Tamperproof queries are only possible on tamperproof tables, as only tamperproof tables generate cryptographic commitments for all newly ingested data - a requirement for proof generation.
Creating a tamperproof table requires a few special settings, which are marked out in the example below:
CREATE TABLE MySchema.My_TpTable_v1 (
MyPK_ID bigint not null primary key
,some_textData varchar not null
,some_intData bigint not null
,PROOF_ORDER bigint not null /* <-- this is required */
)
with "tamperproof=true, immutable=true, access_type=public_read, public_key=$RESOURCE_PUBLIC_KEY" /* <-- all required */
Tamperproof Requirements
- PROOF_ORDER - an ordered, unique identifier for the data
- This exists for technical reasons, later versions of Proof of SQL will remove this requirement.
- The order of this column must match the order of the data inserted, however the values need not be consecutive, only ordered.
- The application performing the data insert will need to manage the correct ordering of values (i.e., there is no Auto-Number yet)
- The column must be named
PROOF_ORDER
- It can optionally be the primary key for the table, but not required
- NOT NULL - any column you wish to use in proof generation cannot allow nulls
- You can have nullable columns, however they cannot be used for proof generation (aka cannot be used in a tamperproof query)
- WITH Statement has two required entries:
tamperproof=true
- table should allow ZK Proofsimmutable=true
- table is append-onlypublic_key
- is required for ALL tables, tamperproof or not
- Data Types have varying levels of restrictions
- Check out this page for the latest mapping of data type / feature mapping
For more reading, check out this section on tamperproof table best practices, or this guide on running tamperproof queries.
Creating Optimistic Tables
Optimistic tables are "normal" non-tamperproof tables, and are the default state of any table. For example, the CREATE TABLE
statement at the very top of this guide would be an Optimistic table, since it is not explicitly tamperproof.
Although rare, you can create an Immutable Optimistic table, by adding to the WITH
statement:
WITH "immutable=true, public_key=$RESOURCE_PUBLIC_KEY"
or, if you want to be explicit:
WITH "tamperproof=false, immutable=true, public_key=$RESOURCE_PUBLIC_KEY"
Immutable tables, whether tamperproof or optimistic, CANNOT BE MODIFIED, regardless of the Default Table Permission setting. While INSERTS
and SELECTS
are allowed, DELETES
, UPDATES
, and DROP TABLE
statements are all disallowed, regardless of other permission settings. This is by design, to prevent a malicious actor from mutating the table by saving all records, and dropping / modifying / reinserting all records.
Take great care when creating Immutable tables - they cannot be dropped or modified!
TABLE CREATION SETTING: Permissioned Views
While not technically a table, permissioned views provide alternative and limited access to a table without exposing the underlying table itself, by using a view. Essentially it allows a view's permission to emulate that of a table.
For example, imagine an Employee table, and you want to provide views for each department. You can do this with a normal view, however, employees will also have access to all underlying tables (whether they understand that or not). A permissioned view could be created for each department, with WHERE
statements that constrain the returned data to only one department. You could then issue biscuits to each user or department that allow them to access the Permissioned view, but with NO ACCESS to the underlying table.
The user would still need to use biscuits to authorize, however that authorization is only for the table, not any underlying tables. This is a way to setup "row level security" for a group of employees.
Permissioned Views are in pre-release right now - check back soon for full details!
Table Options: AUTHENTICATION (Ledger Tables)
Some table behavior can change depending on who is logged in, and what that particular user is trying to do. For example, you can configure tables that allows anyone to insert new rows, but each user can only select out their rows.
AUTHENTICATION: Ledger Tables
Ledger tables are multi-tenant tables that are similar to a blockchain ledger, where any permissionless authenticated user can insert rows of data, but the ledger tables' read authorization can be configured.
Ledger tables are designed to allow anyone to add new items to the table, however, each user can only select out rows that they themselves have inserted. Alternatively, you can allow limit selects to the entire subscription, allowing one organization to share a set of rows of a larger ledger.
Ledger tables are in pre-release right now - check back soon for full details!
Table Options: AUTHORIZATION (Biscuits)
These are options and / or permissions that are controlled by the decentralized authorization (biscuits).
Biscuits are a cryptographically secure decentralized authorization key that allows the holder of a table's private key to define and distribute authorizations to anyone, in a way that is both safe and organizationally manageable.
How Biscuits Work
Biscuits are created locally (client) with the table's Private key, and verified on the network (server) with the table's Public key.
The process of creating a biscuit looks like:
-
Create an ED25519 public/private keypair
-
Create the desired table, with the Public key from #1 added to the
WITH
statement (required) -
Submit the final
CREATE TABLE...
statement to the Space and Time network to be created, again including the Public key. -
With the same Private key from #1, create a biscuit with the appropriate constraints and authorizations.
-
The user submits a query to the network, and includes a biscuit.
-
The table verifies the biscuit instructions, filters and capabilities using it's Public key, which must match the corresponding Private key from step #4
How to Define a Biscuit?
To create a biscuit, you need a "datalog" - text that defines the authorization granted and to whom, in terms of filter "checks" and "capabilities." This datalog text is then signed by the private key that matches the table's public key.
Capabilities
Capabilities are always structured as:sxt:capability("<operation>", "<resource>");
Operations are: dql_select
, dml_insert
, dml_update
, dml_delete
, ddl_create
, ddl_drop
Resources are objects in the Space and Time network, most commonly Tables and Views. It is also possible to use the wildcard character *
to represent ALL capabilities and/or resources (as long as the resource's public key matches).
A few examples:
sxt:capability("dql_select", "mySchema.myTable");
sxt:capability("dml_insert", "mySchema.myTable");
sxt:capability("dml_update", "mySchema.myTable");
sxt:capability("dml_delete", "mySchema.myTable");
sxt:capability("*", "mySchema.myTable");
sxt:capability("dql_select", "*");
sxt:capability("*", "*");
"Wildcard" biscuits are fine for development, but should rarely (if ever) be used in production.
Checks
Checks are structured like IF statements, and allow standard operations like equality, inequality, greater than, less than, etc. Currently you can write checks on:
- Timestamp
- UserID logged in
- Subscription associated with the UserID
A few examples:
check if sxt:user("MyuserId");
check if sxt:subscription("<Users_Subcription>");
check if time($time), $time <= 2024-07-01T12:00:00Z;
Each line is evaluated alone, meaning they behave like AND operations. If you would like an OR operation (i.e., UserABC OR Subscription123) add them together on one line:
check if sxt:user("Chuck") or sxt:subscription("abc123_example_subscription");
check if time($time), $time <= 2024-07-01T13:00:00Z or $time >= 2024-07-01T12:00:00Z;
Time format must be ISO8601 compliant, as seen above.
A full combined biscuit might look something like:
sxt:capability("dql_select", "mySchema.myTable");
sxt:capability("dml_insert", "mySchema.myTable");
check if sxt:user("Chuck") or sxt:subscription("abc123_example_subscription");
check if time($time), $time <= 2025-07-01T12:00:00Z;
Once you have the datalog (text above) and table's private key, you can put them together into a biscuit!
The SXT CLI does much of this work for you, and is a far easier approach. To see biscuit creation in action, check out this recipe!
How to Create a Biscuit with the CLI?
The easiest way to create new biscuits is to use the SXT CLI. You can create a biscuit for a resource (table or view) before or after creating the resource - it doesn't matter. You only need the private key and one capability to create a biscuit.
Confirm prerequisites: SXTCLI 0.0.6 or higher is installed and working, and you have loaded your table's public and private keys into environment variables RESOURCE_PUBLIC_KEY
and RESOURCE_PRIVATE_KEY
.
To test:
# display character length of private key, and full public key:
Demo~$ echo -n $RESOURCE_PRIVATE_KEY | wc -m
64
Demo~$ echo $RESOURCE_PUBLIC_KEY
5CA5D53A2F58227676063C8187CEB171962B86909CF59205CBDCEEE98E2BDB69
# display version of sxtcli, and help menu:
Demo~$ sxtcli version
Version: 0.0.6
Demo~$ sxtcli help
Usage: <main class> [COMMAND]
Commands:
help Display help information about the specified command.
authenticate Perform platform authentication commands.
biscuit Perform biscuit commands.
discover Perform resource discovery commands.
sql Perform SQL commands.
sql-support Perform supporting SQL commands.
subscription Perform subscription commands.
activity Perform activity retrieval commands.
version Displays the version information
Build the biscuit: for the example biscuit below, we'll assume:
- the table:
mySchema.myTable
- granting:
select
andinsert
permissions only - restricted only to: UserID
Alice
in the SubscriptionIDabc123456789def
- expiring end of year, 2024
This set of criteria would look like:
sxtcli biscuit generate table \
--privateKey=$RESOURCE_PRIVATE_KEY \
--resources="mySchema.myTable" \
--operations=SELECT,INSERT \
--users="Alice" \
--subscriptions="abc123456789def" \
--expires="2024-12-31T23:59:59Z"
The above SXTCLI command will create the biscuit, and print the full list of capabilities and checks, as well as the biscuit key at the bottom. Given the above sample input, the expected output would be:
Biscuit content:
Facts:
sxt:capability("dml_insert","myschema.mytable");
sxt:capability("dql_select","myschema.mytable");
Checks:
check if sxt:user("Alice");
check if sxt:subscription("abc123456789def");
check if time($time), $time <= "2024-12-31T23:59:59Z";
Biscuit:
EtwCCvEBCg5zeHQ...
To confirm that the biscuit key contains the correct information, you can visit BiscuitSec.org and paste the above Biscuit key into the "Encoded token" textbox on the main page, and it will display the contents:
It is possible to create one biscuit that is good for many tables, but requires all tables be created with the same public key, so they can all be verify by the biscuit created with the same private key.
Best Practices for Biscuits
- Treat biscuits like the sensitive keys they are! Do not let them get out into the wild, there is no way to revoke, except to recreate your table with a brand new keypair.
- Always add a
check if sxt:subscription("<Your_Subcription>");
so that the biscuit will only work if the user belongs to your SxT subscription.- Allows admins to quickly remove biscuit access by removing the UserID from the SxT subscription. Thus, biscuits will self-adjust for new hires and terminations.
- It also reduces risk if the biscuit is accidently lost / picked up by a malicious actor - it will only work for them if they are ALSO in your SxT organization.
- Force key rotation by setting expiration dates on all biscuits.
- It is also possible to create ephemerial biscuits - i.e., if you have an automated process that has access to the table's private key, it can create a biscuit with an expiration that aligns with the ACCESS_TOKEN timeout, and only for the current expected operations. i.e., unlike the private key, biscuits can be created JIT and need not be persistent.
See this page for a deep dive on biscuits.
Table Options: ENCRYPTION
Some behavior or table permissions / options are characteristics of the API endpoint used to make the request. These are often used for behavior that needs to interoperate with other table options without interfering. For example, Encryption is managed via endpoints, so that the full range of access_types
remain available to developers.
COLUMN ENCRYPTION
Data can be encrypted to provide additional data privacy. Unlike permissions, which restrict access to rows, encrypted data can be exposed or not, but remain in an unrecognizable / unusable state until decrypted.
Encrypted data is a function of the API endpoint used. The APIs are nearly identical and have interoperable signatures. For example:
Endpoint URL: https://api.spaceandtime.dev
Operation | Normal Table Data | Encrypted Table Data |
---|---|---|
INSERT | /v1/sql/dml | /v1/encryption/sql/dml |
SELECT | /v1/sql/dql | /v1/encryption/sql/dql |
This does allow for the mixing of encrypted and non-encrypted data, however, that also requires the correct API endpoint be used on the correct corresponding rows. If you retrieve encrypted rows without decrypting, you will get encrypted data.
As a best practice, keep the entire table encrypted or unencrypted, rather than selectively encrypting parts of a table.
Check out the different types of encryption available here!
Updated 17 days ago