When I clear the current user in ThreadLocalContext the new DB connection call returns default superuser connection. With this connection I can run update query on RLS enabled tables using JdbcTemplate. But if I try to call JPA save() method, I still get new row violates row-level security policy error
@Autowired
private DataSource dataSource;
ThreadLocalStorage.clearDBUser();
System.out.print(DataSourceUtils.getConnection(dataSource).getMetaData().getUserName());
//jdbcTemplate.update("update employee set is_deleted= true where id=?", id);
Employee emp = employeeService.findById(id);
emp.setIsDeleted(true);
employeeRepository.save(emp);
Output is ERROR: new row violates row-level security policy for table "employee"
As I have a SELECT RLS policy that filters deleted records, my UPDATE otherwise fails if DB role is not superuser. So, I have to make this switch happen before I call the JPA save() method.
ref SO Ans1
ref SO Ans2
ref So Ans3
Followed the above answers but could not resolve in using JPA save() method call and bypass RLS
