I have a VIEW in my local DB rining in Docker with POstgres v15. Works all good. I tried to migrate my DB in Neon and got and error
"Error: Invalid options boolean value for securityInvoker: on"
"Error: Invalid options boolean value for securityInvoker: on"
I figured out the probably the way how this works in Neon is different because after removing the
security_invoker = on
security_invoker = on
clause from my SQL statement solved the issue, and I could migrate in Neon wihtout any problem.
I had to drop the table and recreate it without the secoruty innvoker. So my new down migration file looks like this:
DROP VIEW IF EXISTS user_roles_detailed;CREATE VIEW user_roles_detailed WITH (security_invoker = on) ASSELECT u.id as user_id, u.email, u.name, r.name as role_nameFROM users uJOIN user_roles ur ON u.id = ur.user_idJOIN roles r ON ur.role_id = r.id;
DROP VIEW IF EXISTS user_roles_detailed;CREATE VIEW user_roles_detailed WITH (security_invoker = on) ASSELECT u.id as user_id, u.email, u.name, r.name as role_nameFROM users uJOIN user_roles ur ON u.id = ur.user_idJOIN roles r ON ur.role_id = r.id;
My question is, how to still allow the security to be on for RLS, as I understand that what this does? I have RLS enabeld in all tables in the DB, and this is the only view which is problematic.