A multi-tenant schema using PostgreSQL row level security

To add multi-tenancy to an existing application I settled on the following: Create a tenant table which holds a row for every tenant. Tenants are externally identified by a unique key. Add a tenant_id column to every existing table which references tenant(id). Create a new role (application) without superuser privileges and grant it ownership of all tables. Create a row level security policy on each table to check that the tenant_id column is equal to current_setting('application.

Read More

Notes on PostgreSQL row level security

Row level security policies do not apply to the table owner (by default) By default RLS policies are not applied to the table owner - one needs to explicitly specify this using to public. create policy some_policy on some_table for all to public using (...); Row level security policies do not apply to superusers Any user that is a superuser, or has the bypassrls bit set will always bypass the RLS system.

Read More

Adventures with PostgreSQL large objects

Apart from the well-known TOAST functionality for storing large columns, PostgreSQL also contains a less-known large object facility. An in-depth explanation is found in the documentation, so suffice it to be said that large objects are stored the pg_largeobject and pg_largeobject_metadata system tables. They can be referenced by their oid and PostgreSQL provides a rather comprehensive set of manipulation functionalities, including importing from or exporting to files, random access, etc. That being said, they also come with a significant list of caveats, some of which will be given in more detail below.

Read More