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)