Migrating from PostgreSQL to Centralized Authorization with SpiceDB

Migrating from PostgreSQL to Centralized Authorization with SpiceDB

SpiceDB is a database designed to store relationships and compute authorization decisions. With SpiceDB, you decouple authorization data from your applications and store it in a centralized service. This frees you to focus on writing your applications while leaving the crunchy authorization calculations to SpiceDB.

It is already easy to hit the ground running with SpiceDB when you’re writing a new application - just import one of the client libraries into your application and start hacking.

Today, we’re also simplifying the integration of existing applications into SpiceDB.

Introducing zed import

The latest version of zed introduces a new subcommand import to help you get external data into SpiceDB.

If you have been working on a SpiceDB schema in the playground, you can now import the schema (and test relationships) into a SpiceDB instance with:

zed import https://play.authzed.com/s/iksdFvCtvnkR/schema

Just paste a “share” link from the playground and zed will do the rest.

zed also supports importing directly from a gist or from pastebin:

zed import https://pastebin.com/8qU45rVK
zed import https://gist.github.com/ecordell/8e3b613a677e3c844742cf24421c08b6

Check zed import --help for more examples, including importing from a running spice serve-devtools instance or from a local file. Don’t see a service that you’d like to see supported? File an issue and let us know!

Postgres Import

The latest release of zed also introduces an experimental way to transform and import data from a PostgreSQL database into SpiceDB.

This is a great way to get data from an existing application into SpiceDB for testing, or for bootstrapping a transition away from bespoke, unmaintainable application authorization.

zed experiment import postgres will connect to PostgreSQL, generate an example SpiceDB schema and a config file that maps postgresql data into SpiceDB, and then attempt to sync that data into SpiceDB as relationships. There are a few ways you can try it out:

Self-Driving

This is good for getting some real data into SpiceDB for testing purposes. The importer reads the schema of the PostgreSQL database and makes a best-effort mapping config based on foreign-key relationships. For simple schemas, this may be all you need.

zed experiment import postgres --dry-run=false "postgres://postgres:secret@localhost:5432/mydb?sslmode=disable"
  • Prints out a zed schema + a config mapping from PostgreSQL to SpiceDB
  • Appends the generated zed schema to SpiceDB’s schema
  • Mirrors all relationships into SpiceDB according to that config

Dry-Run

Without any flags specified, the import will be a dry-run. It will print the generated SpiceDB schema and mapping to stdout, and will log relationships that would have been written to SpiceDB to stderr.

This can be a good place to start for writing your own config file.

zed experiment import postgres "postgres://postgres:secret@localhost:5432/mydb?sslmode=disable" > config.yaml
  • Prints out a zed schema + a config mapping from PostgreSQL to SpiceDB
  • Logs relationships that would have been written to SpiceDB

Custom Config

You might want to write your own importer config if:

  • You have a complex schema that the importer doesn’t generate a good mapping for
  • You want to generate relationships that differ from the foreign key relationships in PostgreSQL (i.e. if you have a join table)
  • You want to generate relationships that don’t correspond to foreign key constraints at all
  • You want to change the generated object type and relationship names

You can tell the importer exactly how to map rows from PostgreSQL into relationships in SpiceDB. Running the importer first as a dry-run is a good way to get an example config for your database.

zed experiment import postgres --config=config.yaml --append-schema=false "postgres://postgres:secret@localhost:5432/mydb?sslmode=disable"
  • Uses the provided config.yaml to write relationships into SpiceDB
  • If the required schema is already in SpiceDB, skip appending it with --append-schema=false

Example config.yaml

schema: |2
  definition customer {}

  definition contact {
      relation customer: customer
  }

  definition article {
      relation tags: tags
  }

  definition tags {
      relation article: article
  }
tables:
# for each row in the contacts table
- name: contacts
  relationships:
  # generate a relationship contact:<contact_id> customer customer:<customer_id>_<customer_name>
  - resource_type: contact
    resource_id_cols:
    - contact_id
    relation: customer
    subject_type: customer
    subject_id_cols:
    - customer_id
    - customer_name
# for each row in the article_tag table (a join table from articles <-> tags)
- name: article_tag
  relationships:
  # generate a relationship article:<article_id> tags tag:<tag_id>
  - resource_type: article
    resource_id_cols:
    - article_id
    relation: tags
    subject_type: tags
    subject_id_cols:
    - tag_id
  # generate a second relationship tags:<tag_id> article article:<article_id>
  - resource_type: tags
    resource_id_cols:
    - tag_id
    relation: article
    subject_type: article
    subject_id_cols:
    - article_id

Caveats

The PostgreSQL importer comes with some caveats, which is why we’ve labeled it an experiment:

  • The queries it emits are simple, and may be insufficient for very large datasets
  • Object IDs can only be generated from a set of column values, each interpted as the PostgreSQL ::text type.

If you’d like to discuss your use-case and potential solutions to these issues, feel free to file an issue or come chat with us in discord.

The Road to Connectors

import solves the problem of bootstrapping an application with existing data. After bootstrapping, the application should switch to reading and writing relationships in SpiceDB directly. For many cases, this is enough.

But there may be times when the data or application you want to use is not fully under your control, or when transitioning to SpiceDB clients would be too difficult to coordinate.

For those cases, we intend to support first-class connectors: services that run continuously to sync data from an external source into SpiceDB.

Over the coming months, we plan to explore the solution space for syncronizing external data into SpiceDB. If you’d like to noodle on these problems with us, please drop us a line in discord. We’d love to hear from you!

Recent Articles
blog-image
Consistent Hash Load Balancing for gRPC
Extending grpc-go with a custom resolver and loadbalancer implementations.
blog-image
The Architecture of SpiceDB
The architecture and code of SpiceDB.
blog-image
Migrating from PostgreSQL to Centralized Authorization with SpiceDB
Learn how to bootstrap SpiceDB from playground or PostgreSQL.