N
Neon•2mo ago
gradual-turquoise

security_invoker = on

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" I figured out the probably the way how this works in Neon is different because after removing the 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) AS
SELECT
    u.id as user_id,
    u.email,
    u.name,
    r.name as role_name
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id;
DROP VIEW IF EXISTS user_roles_detailed;
CREATE VIEW user_roles_detailed WITH (security_invoker = on) AS
SELECT
    u.id as user_id,
    u.email,
    u.name,
    r.name as role_name
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN 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.
1 Reply
optimistic-gold
optimistic-gold•2mo ago
Hey! I was able to replicate, and I fixed it by using security_invoker = true instead seems like an issue with drizzle studio not liking the "on" representation of a bool

Did you find this page helpful?