CODEWITHSUNDEEP
postgresql
xxx_coder_noscope
xxx_coder_noscope

Reputation: 85

Permissions check postgresql function

I have a function written in plpgsql that checks if user have permissions:


CREATE OR REPLACE FUNCTION check_permission(acc_id integer, permissions text[]) RETURNS boolean AS $$
        BEGIN
          SELECT DISTINCT p.name FROM person_role pr
          JOIN role_permission rp ON pr.role_id = rp.role_id
          JOIN permission p ON rp.permission_id = p.permission_id
          WHERE account_id = acc_id ;
          
          -- check
        END;
$$ LANGUAGE plpgsql;

How can i check that select includes all permissions from permissions text[] argument?

Upvotes: 0

Views: 369

Answers (1)

Bergi
Bergi

Reputation: 664405

You can unnest the array into a result set and from it subtract the role names from your result:

CREATE OR REPLACE FUNCTION check_permission(acc_id integer, permissions text[]) RETURNS boolean AS $$
  SELECT NOT EXISTS (
      SELECT unnest(permissions)
    EXCEPT
      SELECT DISTINCT p.name
      FROM person_role pr
      JOIN role_permission rp ON pr.role_id = rp.role_id
      JOIN permission p ON rp.permission_id = p.permission_id
      WHERE pr.account_id = acc_id
  );
$$ LANGUAGE sql;

It might however better express the intent to use a boolean aggregate over the permission table and a subquery:

SELECT bool_and(EXISTS(
  SELECT *
  FROM person_role pr
  JOIN role_permission rp ON pr.role_id = rp.role_id
  WHERE rp.permission_id = p.permission_id
    AND pr.account_id = acc_id
))
FROM permission p
WHERE p.name = ANY permissions -- or JOIN (SELECT unnest(permissions) AS name) USING (name)

Notice this will return NULL instead of TRUE when the permissions array is empty.

Upvotes: 2

Related Questions