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"

"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) 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.

Neon•7mo ago•

2 replies

forward-apricot

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"

"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

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.

security_invoker = on

Similar Threads

security_invoker = on

Similar Threads

Similar Threads

Similar Threads