I have a view that a coworker created based on the authentication example for postgrest. I was wondering now if it should use the WITH local CHECK OPTION
. It currently uses a trigger. It does not work as expected. The view is:
CREATE OR REPLACE VIEW users AS
SELECT
actual.role AS role,
'***' :: TEXT AS pass,
actual.id AS id,
actual.email AS email,
actual.first_name AS first_name,
actual.last_name AS last_name,
actual.verified AS verified,
actual.blocked AS blocked
FROM basic_auth.users AS actual,
(SELECT rolname
FROM pg_authid
WHERE pg_has_role(current_user, oid, 'member')
) AS member_of
WHERE actual.role = member_of.rolname
-- needed for update/insert ???
-- WITH local CHECK OPTION;
DROP TRIGGER IF EXISTS update_users
ON users;
CREATE TRIGGER update_users
INSTEAD OF INSERT OR UPDATE OR DELETE ON
users FOR EACH ROW EXECUTE PROCEDURE update_users();
I use my browser UI to sign up/register a new user. Then if I go to users view in the DB (through my Postico postgres GUI client), edit the view's record so that I make it "verified = true", and change the role from "verfication_pending" to "verfiied_user". The underlying table does get updated, BUT I can't signin/login with that user (invalid credentials error from postgREST). If I update the basic_auth.users table directly with the exact same edits as I do to the view (again through my postico GUI), I can sign in successfully with that user through the browser. So there is something different happening when the update occurs through the view.
Another thing I was concerned about with the trigger: Is that looping over every user in the users view? Or is it looping over a set of just the user record that is being updated? (I wish there was some optional, well-defined, built in auth mechanism for postgREST)