Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- these are tables you likely already have in your database
-- 1. something representing the user
-- 2. something representing the object we want to filter by
CREATE TABLE users (
    id     varchar(100) PRIMARY KEY,
    name   varchar(40)
);
CREATE TABLE documents (
    id     varchar(100) PRIMARY KEY,
    name   varchar(40),
    contents_bucket varchar(100)
);


-- These tables are just used to track data from LookupPermissionSets 
-- and WatchPermissionSets - all you need to do is store the fields
-- directly from those apis.
CREATE TABLE member_to_set (
  member_type varchar(100),
  member_id varchar(100),
  member_relation varchar(100),
  set_type varchar(100),
  set_id varchar(100),
  set_relation varchar(100)
);

CREATE TABLE set_to_set (
  child_type varchar(100),
  child_id varchar(100),
  child_relation varchar(100),
  parent_type varchar(100),
  parent_id varchar(100),
  parent_relation varchar(100)
);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE


-- seed some base data; this would already exist in the application
INSERT INTO users (id, name) VALUES ('123', 'evan'), ('456', 'victor');
INSERT INTO documents (id, name) VALUES ('123', 'evan secret doc'), ('456', 'victor shared doc');

INSERT 0 2



INSERT 0 2


-- Sync data from Lookup/WatchPermissionSets
-- The APIs return type / id / relation name 
INSERT INTO member_to_set (member_type, member_id, member_relation, set_type, set_id, set_relation) 
  VALUES ('user', '123', '...', 'document', '123', 'view'), 
         ('user', '123', '...', 'group', 'shared', 'member'),
         ('user', '456', '...', 'group', 'shared', 'member');

INSERT INTO set_to_set (child_type, child_id, child_relation, parent_type, parent_id, parent_relation) 
  VALUES ('group', 'shared', 'member', 'document', '456', 'view');

INSERT 0 3



INSERT 0 1


-- join local data with LPS/WPS data to filter by specific permissions

-- find all documents evan can `view`
SELECT d.id FROM documents d
  INNER JOIN set_to_set s2s ON d.id = s2s.parent_id
  INNER JOIN member_to_set m2s ON m2s.set_id = s2s.child_id AND m2s.set_type = s2s.child_type AND m2s.set_relation = s2s.child_relation
  INNER JOIN users u ON u.id = m2s.member_id
  WHERE u.name = 'evan' 
    AND m2s.member_type = 'user' 
    AND m2s.member_relation = '...' 
    AND s2s.parent_type = 'document' 
    AND s2s.parent_relation='view'
UNION
SELECT d.id FROM documents d
  INNER JOIN member_to_set m2s ON d.id = m2s.set_id
  INNER JOIN users u ON u.id = m2s.member_id
  WHERE u.name = 'evan' 
    AND m2s.member_type = 'user' 
    AND m2s.member_relation = '...' 
    AND m2s.set_type = 'document' AND m2s.set_relation = 'view';

-- find all document victor can `view`
SELECT d.id FROM documents d
  INNER JOIN set_to_set s2s ON d.id = s2s.parent_id
  INNER JOIN member_to_set m2s ON m2s.set_id = s2s.child_id AND m2s.set_type = s2s.child_type AND m2s.set_relation = s2s.child_relation
  INNER JOIN users u ON u.id = m2s.member_id
  WHERE u.name = 'victor' 
    AND m2s.member_type = 'user' 
    AND m2s.member_relation = '...' 
    AND s2s.parent_type = 'document' 
    AND s2s.parent_relation='view'
UNION
SELECT d.id FROM documents d
  INNER JOIN member_to_set m2s ON d.id = m2s.set_id
  INNER JOIN users u ON u.id = m2s.member_id
  WHERE u.name = 'victor' 
    AND m2s.member_type = 'user' 
    AND m2s.member_relation = '...' 
    AND m2s.set_type = 'document' AND m2s.set_relation = 'view';


id


123

456



SELECT 2




id


456



SELECT 1


-- The above example shows the most flexible way to do this: you can update
-- your SpiceDB schema and sync new PermissionSets data without schema 
-- changes, but at the cost of more verbose SQL queries.

-- If you know that you only care about `user view document` then you can store
-- that data more concisely. This strategy can also be used to shard the data 
-- coming from the Materialize APIs so that it does not all land in one table.

CREATE TABLE user_to_set (
  user_id varchar(100),
  parent_set varchar(300)
);

CREATE TABLE set_to_document_view (
  child_set varchar(300),
  document_id varchar(100)
);

-- Storing from Lookup/WatchPermissionSets requires some simple transformation
-- compared to the previous example:
INSERT INTO user_to_set (user_id, parent_set) 
  VALUES ('123', 'document:123#view'), 
         ('123', 'group:shared#member'),
         ('456', 'group:shared#member');

-- Note the additional entry here to avoid a union in the final query
INSERT INTO set_to_document_view (child_set, document_id) 
  VALUES ('document:123#view', '123'), 
         ('group:shared#member', '456');


-- The queries are a bit simpler, though it can be used to answer any
-- permission check other than user#view@document
-- find all documents evan can `view`
SELECT d.id FROM documents d
  INNER JOIN set_to_document_view s2s ON d.id = s2s.document_id
  INNER JOIN user_to_set m2s ON m2s.parent_set = s2s.child_set 
  INNER JOIN users u ON u.id = m2s.user_id
  WHERE u.name = 'evan';

-- find all document victor can `view`
SELECT d.id FROM documents d
  INNER JOIN set_to_document_view s2s ON d.id = s2s.document_id
  INNER JOIN user_to_set m2s ON m2s.parent_set = s2s.child_set 
  INNER JOIN users u ON u.id = m2s.user_id
  WHERE u.name = 'victor';

CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 2




id


123

456



SELECT 2




id


456



SELECT 1