Ensure N:ALL relation ship for some rows

Question

I have a simple N:M relation ship:

Table User:

• Column ID
• Column username
• Column is_superuser (Boolean)

Table Permission:

• Column ID
• Column Name

Table UserPermission:

• Column ID
• ForeignKey User
• ForeignKey Permission

I want to ensure that if the user has "is_superuser=True" then he should have all available Permission set via N:M Table UserPermission.

Typical operations:

• Update or insert on table "User": user gets set "is_superuser". Now the UserPermission needs to have all permissions for this user
• A new permission gets added to the Permission table. Now the UserPermission needs to get N new rows (for each user with is_superuser=True).

How to ensure this with Postgres 9.6?

Sub-question (please leave a comment): Is there a name for this pattern?

Answer 1

This is a mistake. Just join the tables and infer permissions.

CREATE TABLE users (
  userid       serial  PRIMARY KEY,
  username     text    UNIQUE,
  is_superuser bool    NOT NULL DEFAULT false
);
CREATE TABLE permissions (
  permid  serial PRIMARY KEY,
  name    text
);
CREATE TABLE user_permission (
  permid  int NOT NULL REFERENCES permissions,
  userid  int NOT NULL REFERENCES users,
  PRIMARY KEY (permid, userid)
);

INSERT INTO users ( username, is_superuser ) VALUES
  ( 'ecarroll', true ),
  ( 'jbob', false );

INSERT INTO permissions (name) VALUES ('delete');
INSERT INTO permissions (name) VALUES ('select');

INSERT INTO user_permission (permid, userid) VALUES (2,2);

Now we write a query that cross joins users with permissions so we can see all a users permissions. And, then we left outer join that for all users that are not superusers, with the linking table. We infer if they has_permission if they're is_superuser OR /have entry in linking table/. The is_superuser is a superior source: we no longer even care about what's in the linking table if we have that bool set. This makes your job simple and efficient. You don't have to keep the tables in sync.

SELECT
  users.userid,
  users.username,
  permissions.name,
  users.is_superuser OR user_permission.userid IS NOT NULL AS has_permission
FROM users
CROSS JOIN permissions
LEFT OUTER JOIN user_permission
  ON NOT is_superuser
  AND user_permission.userid = users.userid
  AND user_permission.permid = permissions.permid
ORDER BY users.userid;

Produces

 userid | username |  name  | has_permission 
--------+----------+--------+----------------
      1 | ecarroll | delete | t
      1 | ecarroll | select | t
      2 | jbob     | delete | f
      2 | jbob     | select | t
(4 rows)