pgtenant

README

Pgtenant

This is pgtenant, a library for adding automatic multitenant safety to Postgresql database queries. It works within the standard Go database/sql framework.

In a nutshell, you write code like this as usual:

rows, err := db.QueryContext(ctx, "SELECT foo FROM bar WHERE baz = $1", val)

but it works as if you had written:

rows, err := db.QueryContext(ctx, "SELECT foo FROM bar WHERE baz = $1 AND tenant_id = $2", val, tenantID)

This happens intelligently, by parsing the SQL query (rather than by dumb textual substitution). A large subset of Postgresql’s SQL language is supported.

This eliminates data-leak bugs in multitenant services that arise from forgetting to scope queries to a specific tenant.

The actual name of your tenant_id column is configurable, but every table must be defined to include one.

For documentation, see https://godoc.org/github.com/bobg/pgtenant.

For more about this package and its history, see https://medium.com/@bob.glickstein/tenant-isolation-in-hosted-services-d4eb75f1cb54

Documentation

Overview

Package pgtenant automatically converts Postgresql queries for tenant isolation.

Database connections made with this package will automatically modify SQL queries always to include "... AND tenant_id = ..." expressions in WHERE clauses, and to append a "tenant_id" column to any INSERT.

This happens intelligently, by parsing the SQL query (rather than by dumb textual substitution). A large subset of Postgresql's SQL language is supported.

This eliminates data-leak bugs arising from forgetting to scope queries to a specific tenant in multitenant services.

The actual name of your tenant_id column is configurable, but every table must be defined to include one.

This implementation covers a lot of the Postgresql query syntax, but not all of it. If you write a query that cannot be transformed because of unimplemented syntax, and if that query is tested with TransformTester, then TransformTester will emit an error message and a representation of the query's parse tree that together should help you to add that syntax to the transformer. (Alternatively, the author will entertain polite and patient requests to add missing syntax.)

Example

package main

import (
	"context"
	"log"

	"github.com/bobg/pgtenant"
)

func main() {
	// This is the list of permitted queries,
	// each mapped to a `Transformed` pair:
	// the string it should transform to,
	// and the number of the added positional parameter for the tenant ID.
	// Your package should include a unit test
	// that calls TransformTester with this same map
	// to ensure the pre- and post-transformation strings are correct.
	whitelist := map[string]pgtenant.Transformed{
		"INSERT INTO foo (a, b) VALUES ($1, $2)": {
			Query: "INSERT INTO foo (a, b, tenant_id) VALUES ($1, $2, $3)",
			Num:   3,
		},
		"SELECT a FROM foo WHERE b = $1": {
			Query: "SELECT a FROM foo WHERE b = $1 AND tenant_id = $2",
			Num:   2,
		},
	}

	db, err := pgtenant.Open("postgres:///mydb", "tenant_id", whitelist)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Constrain your SQL queries to a specific tenant ID
	// by placing it in a context object
	// that is then used in calls to ExecContext and QueryContext.
	ctx := context.Background()
	ctx = pgtenant.WithTenantID(ctx, int64(17))

	// This is automatically transformed to
	//   "INSERT INTO foo (a, b, tenant_id) VALUES ($1, $2, $3)"
	// with positional arguments 326, 3827, and 17.
	_, err = db.ExecContext(ctx, "INSERT INTO foo (a, b) VALUES ($1, $2)", 326, 3827)
	if err != nil {
		log.Fatal(err)
	}

	// This is automatically transformed to
	//   "UPDATE foo SET a = $1 WHERE b = $2 AND tenant_id = $3
	// with positional parameters 412, 3827, and 17,
	// even though this query does not appear in driver.Whitelist.
	// (A context produced by WithQuery bypasses that check.)
	const query = "UPDATE foo SET a = $1 WHERE b = $2"
	_, err = db.ExecContext(pgtenant.WithQuery(ctx, query), query, 412, 3827)
	if err != nil {
		log.Fatal(err)
	}
}

Index

• Variables
• func ID(ctx context.Context) (driver.Value, error)
• func IsSuppressed(ctx context.Context) bool
• func Open(dsn, tenantIDCol string, whitelist map[string]Transformed) (*sql.DB, error)
• func Suppress(ctx context.Context) context.Context
• func TransformTester(t *testing.T, tenantIDCol string, m map[string]Transformed)
• func WithQuery(ctx context.Context, query string) context.Context
• func WithTenantID(ctx context.Context, tenantID driver.Value) context.Context
• type Conn
  • func (c *Conn) Begin() (driver.Tx, error)
  • func (c *Conn) Close() error
  • func (c *Conn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)
  • func (c *Conn) Prepare(query string) (driver.Stmt, error)
  • func (c *Conn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)
• type Connector
  • func (c *Connector) Connect(ctx context.Context) (driver.Conn, error)
  • func (c *Connector) Driver() driver.Driver
• type Driver
  • func (d *Driver) Open(name string) (driver.Conn, error)
  • func (d *Driver) OpenConnector(name string) (driver.Connector, error)
• type Transformed

Examples

• Package

Variables

var ErrNoID = errors.New("no ID")

ErrNoID is the error produced when no tenant ID value has been attached to a context with WithTenantID.

var ErrUnknownQuery = errors.New("unknown query")

ErrUnknownQuery indicates a query that cannot be safely transformed.

Functions

func ID

func ID(ctx context.Context) (driver.Value, error)

ID returns the tenant ID carried by ctx.

func IsSuppressed

func IsSuppressed(ctx context.Context) bool

IsSuppressed tells whether ctx is context created by a call to Suppress, or is descended from such a context.

func Open

func Open(dsn, tenantIDCol string, whitelist map[string]Transformed) (*sql.DB, error)

Open is a convenient shorthand for either of these two sequences:

driver := &pgtenant.Driver{TenantIDCol: tenantIDCol, Whitelist: whitelist}
sql.Register(driverName, driver)
db, err := sql.Open(driverName, dsn)

and

driver := &pgtenant.Driver{TenantIDCol: tenantIDCol, Whitelist: whitelist}
connector, err := driver.OpenConnector(dsn)
if err != nil { ... }
db := sql.OpenDB(connector)

The first sequence creates a reusable driver object that can open multiple different databases. The second sequence creates an additional reusable connector object that can open the same database multiple times.

func Suppress

func Suppress(ctx context.Context) context.Context

Suppress returns a context that suppresses query transformation. Queries run verbatim as written.

func TransformTester

func TransformTester(t *testing.T, tenantIDCol string, m map[string]Transformed)

TransformTester runs the transformer on each query that is a key in m. They are sorted first for a predictable test ordering. Each query is tested in a separate call to t.Run. The output of each transform is compared against the corresponding value in m. A mismatch produces a call to t.Error. Other errors produce calls to t.Fatal.

Programs using this package should include a unit test that calls this function with the same value for m that is used in the Driver.Whitelist field.

func WithQuery

func WithQuery(ctx context.Context, query string) context.Context

WithQuery adds a query to the given context, "escaping" it to allow its use by a connection even if it does not appear in the driver's whitelist.

func WithTenantID

func WithTenantID(ctx context.Context, tenantID driver.Value) context.Context

WithTenantID adds a tenant ID to the given context. Any queries issued with the returned context will be scoped to tenantID. The dynamic type of tenantID must be one of:

• []byte
• int64
• float64
• string
• bool
• time.Time

(as described in the documentation for driver.Value).

Types

type Conn

type Conn struct {
	// contains filtered or unexported fields
}

Conn implements driver.Conn.

func (*Conn) Begin

func (c *Conn) Begin() (driver.Tx, error)

Begin implements driver.Conn.Begin.

func (*Conn) Close

func (c *Conn) Close() error

Close implements driver.Conn.Close.

func (*Conn) ExecContext

func (c *Conn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)

ExecContext implements driver.ExecerContext.ExecContext. The context must have a tenant-ID value attached from WithTenantID. The query must be attached to the context from WithQuery, or else appear as a key in the Whitelist field of the Driver from which c was obtained. The query is transformed to include any necessary tenant-ID clauses, and args extended to include the tenant-ID value from ctx.

func (*Conn) Prepare

func (c *Conn) Prepare(query string) (driver.Stmt, error)

Prepare prepares the given query string, transforming it on the fly for tenancy isolation. Callers using the resulting statement's Exec or Query methods must be sure to add an argument containing the tenant ID.

func (*Conn) QueryContext

func (c *Conn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)

QueryContext implements driver.QueryerContext.QueryContext. The context must have a tenant-ID value attached from WithTenantID. The query must be attached to the context from WithQuery, or else appear as a key in the Whitelist field of the Driver from which c was obtained. The query is transformed to include any necessary tenant-ID clauses, and args extended to include the tenant-ID value from ctx.

type Connector

type Connector struct {
	// contains filtered or unexported fields
}

Connector implements driver.Connector.

func (*Connector) Connect

func (c *Connector) Connect(ctx context.Context) (driver.Conn, error)

Connect implements driver.Connector.Connect.

func (*Connector) Driver

func (c *Connector) Driver() driver.Driver

Driver implements driver.Connector.Driver.

type Driver

type Driver struct {
	// TenantIDCol is the name of the column in all tables of the db schema
	// whose value is the tenant ID.
	TenantIDCol string

	// Whitelist maps SQL query strings to the output expected when transforming them.
	// It serves double-duty here:
	//
	//   1. It is a whitelist of permitted queries.
	//      Database connections created from this driver will refuse to execute a query
	//      unless it appears in this whitelist or is "escaped"
	//      by attaching it to a context object using WithQuery.
	//
	//   2. It is a cache of precomputed transforms.
	//
	// The whitelist is consulted by exact string matching
	// (modulo some minimal whitespace trimming)
	// using the query string passed to QueryContext or ExecContext.
	//
	// The value used here should also be used in a unit test that calls TransformTester.
	// That will ensure the pre- and post-transform queries are correct.
	Whitelist map[string]Transformed
	// contains filtered or unexported fields
}

Driver implements database/sql/driver.Driver and driver.DriverContext.

func (*Driver) Open

func (d *Driver) Open(name string) (driver.Conn, error)

Open implements driver.Driver.Open.

func (*Driver) OpenConnector

func (d *Driver) OpenConnector(name string) (driver.Connector, error)

OpenConnector implements driver.DriverContext.OpenConnector.

type Transformed

type Transformed struct {
	Query string
	Num   int
}

Transformed is the output of the transformer: a transformed query and the number of the positional parameter added for a tenant-ID value.