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
On Thursday, March 4, 2021 at 12:24:39 PM UTC-5 Mike Bayer wrote:
> 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.
>
>
> On Thu, Mar 4, 2021, at 12:21 PM, Open Ocean wrote:
>
> 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
>
>
>
> --
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/306a335a-9c87-4705-9db5-b0768d396b36n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/d2d3fe04-ed52-4e1a-89aa-3de5fb2d3170n%40googlegroups.com.
