When working with PostgreSQL, there are some gems that allow
ActiveRecord models to work with multiple schemas. This practice is
common for multi-tenant applications where one would want to separate
client-specific information within their own schema.
The SQL syntax for accessing a different schema in a query uses dot
notation.
SELECT * FROM myschema.mytable WHERE myschema.mytable.mycolumn = 1;
However, having ActiveRecord append the schema name to tables in all
queries breaks attribute checks in declarative auth.
The attribute check code uses symbol bind variables for sanitation and
passes them into ActiveRecord through finder_options[:conditions]. It
would commonly look something like this:
However when using PostgreSQL schemas, the table name from the
ActiveRecord model comes back as public.clients resulting in the
conditions array looking like this:
This causes ActiveRecord to not match up the bind variables with the SQL
snippet resulting in a PostgrSQL query error.
To fix this, I use gsub to change the period into an underscore when
building the bindvar variable. With this change, I get a conditions
array that ActiveRecord will parse properly.
When working with PostgreSQL, there are some gems that allow ActiveRecord models to work with multiple schemas. This practice is common for multi-tenant applications where one would want to separate client-specific information within their own schema.
The SQL syntax for accessing a different schema in a query uses dot notation.
However, having ActiveRecord append the schema name to tables in all queries breaks attribute checks in declarative auth.
The attribute check code uses symbol bind variables for sanitation and passes them into ActiveRecord through finder_options[:conditions]. It would commonly look something like this:
However when using PostgreSQL schemas, the table name from the ActiveRecord model comes back as
public.clientsresulting in the conditions array looking like this:This causes ActiveRecord to not match up the bind variables with the SQL snippet resulting in a PostgrSQL query error.
To fix this, I use
gsubto change the period into an underscore when building thebindvarvariable. With this change, I get a conditions array that ActiveRecord will parse properly.