Postgres Row Level Security (RLS)

[Open Ocean]

I'm trying to figure out how to add RLS to tables using sqlalchemy and alembic.

I'm changing from schema per tenant to row per tenant in a multi-tenant db.

My approach is:
- use pg setting 'tenant.id' to identify the current tenant
- add tenant column to tables with server_default=func.current_setting('tenant.id') to automatically set the tenant on insert.
- enable rls on tables: ALTER TABLE table ENABLE ROW LEVEL SECURITY
- create rls policy on tables: CREATE POLICY xxx ON table USING (tenant = current_setting('tenant.id'))
- set tenant.id at start of transaction: SET LOCAL tenant.id = current_tenant;

I don't know how to enable rls or create rls policy on the tables without adding custom sql to the alembic version file.

Is there another/better way to do this?

Thanks,

Brian Hill

[Mike Bayer]

this is a highly postgresql-specific set of commands so using op.execute() with the ALTER commands you need seems to be the most direct and obvious way to achieve this, I'm not sure what the downside would be?    if it's the redundancy, simply create a function in your applicaiton that is given the operations context and then runs the required functions, then Alembic version files can import that function and run it as needed.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/306a335a-9c87-4705-9db5-b0768d396b36n%40googlegroups.com.

[Open Ocean]

that worked great. i used the rewriter with process_revision_directives to look for tenant columns in create table, add/drop column operations and add the alter table and policy commands to the version upgrade/downgrade.
thanks,
brian