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_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.