Ensure user role exists

[NickEmpetvee]

Hi guys.

The SQL User Management documentation includes the below code to check if a user role exists. Shouldn't this trigger happen before the insert or update rather than after in order to prevent the change if the role doesn't exist?

create function
basic_auth.check_role_exists() returns trigger as $$
begin
  if not exists (select 1 from pg_roles as r where r.rolname = new.role) then
    raise foreign_key_violation using message =
      'unknown database role: ' || new.role;
    return null;
  end if;
  return new;
end
$$ language plpgsql;

create constraint trigger ensure_user_role_exists
  after insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.check_role_exists();

[laurenceisla]

Hi!

in order to prevent the change if the role doesn't exist?

If I'm not mistaken, the RAISE error will rollback the transaction and the insert won't be completed, even if it's in an AFTER trigger. But still, I agree that it's more accurate to have it in a BEFORE trigger.

[NickEmpetvee]

Thanks that was what I was looking to confim. I believe the RAISE does roll back the transaction as you said.