Permissions table



  • Ok, I've got these tables for users and groups:

    create extension unaccent;
    
    create function "slugify"("t" text) returns text as $$
            begin
                    return regexp_replace(trim(lower(regexp_replace(
                            translate(unaccent("t"), '-_', '  '),
                            '[^\w\s]+', '', 'gi'
                    ))), '\s+', '-', 'gi');
            end;
    $$ language plpgsql;
    
    create function "make_slug"() returns trigger as $$
            begin
                    new."slug" := slugify(new."name");
                    return new;
            end;
    $$ language plpgsql;
    
    create table "users" (
            "id" bigserial primary key,
            "name" varchar(255) not null,
            "slug" varchar(255) not null check ("slug" <> ''),
            "password" varchar(60),
            "join_date" timestamp with time zone not null default now()
    );
    
    create unique index "users_slug" on "users" ("slug");
    
    create index "users_join_date" on "users" ("join_date");
    
    create trigger "users_make_slug"
    before insert or update of "name", "slug" on "users"
    for each row
    execute procedure "make_slug"();
    
    create table "groups" (
            "id" bigserial primary key,
            "name" varchar(255) not null,
            "slug" varchar(255) not null check ("slug" <> '')
    );
    
    create unique index "groups_slug" on "groups" ("slug");
    
    create trigger "groups_make_slug"
    before insert or update of "name", "slug" on "groups"
    for each row
    execute procedure "make_slug"();
    
    insert into "groups" ("id", "name") values
    (-1, 'Registered Users'),
    (-2, 'Guests'),
    (-3, 'Administrators');
    
    create table "groups_users" (
            "group_id" bigint references "groups"("id") on delete cascade on update cascade,
            "user_id" bigint references "users"("id") on delete cascade on update cascade,
            primary key ("group_id", "user_id"),
            constraint "groups_users_not_guest" check ("group_id" <> -2)
    );
    
    create table "groups_groups" (
            "parent_group_id" bigint references "groups"("id") on delete cascade on update cascade,
            "group_id" bigint references "groups"("id") on delete cascade on update cascade,
            primary key ("parent_group_id", "group_id"),
            constraint "groups_groups_not_special" check ("parent_group_id" > 0 and "group_id" > 0)
    );
    
    create recursive view "groups_groups_flat" ("parent_group_id", "group_id") as
    select g."id", g."id" from "groups" as "g"
    union
    select gg."parent_group_id", ggf."group_id"
    from "groups_groups_flat" as ggf
    inner join "groups_groups" as gg
    on gg."group_id" = ggf."parent_group_id";
    
    create function "groups_has_ancestor"("ancestor_id" bigint, "child_id" bigint) returns boolean as $$
            begin
                    return exists (select 1 from "groups_groups_flat" where "parent_group_id" = "ancestor_id" and "group_id" = "child_id");
            end;
    $$ language plpgsql;
    
    alter table "groups_groups" add constraint "groups_groups_no_cycles" check(not "groups_has_ancestor"("group_id", "parent_group_id"));
    

    That is:

    • names are compared based on slugs
    • slugs must be unique in their domain (users or groups)
    • users can be in groups
    • groups are arranged in a DAG
    • negative-ID groups are special
      • they can't be edited through the web UI
      • they can't be added to group hierarchies
      • the "guests" group has no users and is checked for permissions directly
      • the "administrators" group has every possible permission as a "sudo" permission with the highest possible priority
      • the "registered users" group contains every user on the forum except guests
    • IDs are NEVER zero, which means I can use 0 to represent a nonexistent ID on the code side without needing to mess with pointers or nullable integers.

    Now, presumably, there will be other tables as well, for categories, topics, posts, and so on.

    I want to be able to grant or deny permissions to users and groups on various things and have permissions automatically flow through hierarchies. Here's what I have so far:

    create table "permissions" (
            "id" bigserial primary key,
            "key" varchar(255) not null,
            "priority" int not null,
            "reflexive" boolean not null default false,
            "grant" boolean not null,
            "grant_user_id" bigint references "users"("id") on delete cascade on update cascade,
            "grant_group_id" bigint references "groups"("id") on delete cascade on update cascade,
            "global" boolean not null default false,
            "user_id" bigint references "users"("id") on delete cascade on update cascade,
            "group_id" bigint references "groups"("id") on delete cascade on update cascade,
    
            constraint "permissions_single_grant" check (("grant_user_id" is null) <> ("grant_group_id" is null)),
            constraint "permissions_reflexive_group" check ("grant_group_id" is not null or not "reflexive"),
            constraint "permissions_single_target" check (
                    case when "user_id" is null then 0 else 1 end +
                    case when "group_id" is null then 0 else 1 end +
                    case when "global" then 1 else 0 end = 1
            )
    );
    
    create unique index "permissions_unique" on "permissions" ("key", "reflexive", coalesce("grant_user_id", 0), coalesce("grant_group_id", 0), "global", coalesce("user_id", 0), coalesce("group_id", 0));
    

    Now, this table definitely needs at least an index, but it's pretty clumsy to work with already: any given row will have mostly nulls in it. Additionally, I want to add "sudo" permissions, where a moderator wouldn't have the ability to edit other users' posts, but could do so in "sudo" mode. Sudo mode actions would be logged more aggressively than normal actions and would require some form of proof that the user isn't using the extra permissions by accident.

    How should the permissions table look?

    /cc @Weng (I know you get off to this kind of stuff)



  • @ben_lubar said in Permissions table:

    reflexive

    Oh, I just realized I hadn't explained this.

    A reflexive permission only applies to things owned by the acting user. So all users could have a reflexive "edit profile" permission globally. Come to think of it, it doesn't really make sense to restrict that to groups only since someone might want to allow a specific user to edit their own topic titles, but only in a specific category.



  • @ben_lubar said in Permissions table:

    (I know you get off to this kind of stuff)

    Stop stalking @Weng's bedroom.

    @ben_lubar said in Permissions table:

    I want to be able to grant or deny permissions to users and groups on various things and have permissions automatically flow through hierarchies.

    Well, first thing that comes to mind is "why can't it be two tables"?

    General "permission" table representing things like "can frobnicate widgets" - that, I assume, is what you already have keyed by key. users_permissions table with an user, permission and various flags - whether they're active, reflexive (you keep using that word), sudo-only, etc. groups_permissions table similarly, except with a group instead of a user.

    If you want to be able to add permissions like "can edit topic 12345 and 54321" - I'd personally add a nullable applicable_set column to users_permissions and a separate table where you store the set_id - topic_id mapping.

    Also, just think about how your queries would look like, and whether you can write them up to be simple enough. If not, rethink design and repeat.



  • @Maciejasjmj said in Permissions table:

    I'd personally add a nullable applicable_set column to users_permissions and a separate table where you store the set_id - topic_id mapping.

    Wouldn't I end up with the "sets" table just being an identity column?



  • @ben_lubar said in Permissions table:

    Wouldn't I end up with the "sets" table just being an identity column?

    If you want user XYZ to have edit_specific_posts permission on post 12345 and 54321, you'd have

    set_id | post_id
    1      | 12345
    1      | 54321
    

    Basically a one-to-many mapping table. I guess you could denormalize that and just duplicate the whole permission record, but given that the whole relationship is optional, it looks kind of smelly to me.

    Or actually, make set_id a users_permissions_id and you don't even need that column in the users_permissions table.



  • Ok, here's what I have now:

    create table "permissions" (
            "id" bigserial primary key,
            "key" varchar(255) not null unique
    );
    
    create table "permission_sets" (
            "id" bigserial primary key
    );
    
    create table "permission_sets_users" (
            "set_id" bigint not null references "permission_sets"("id") on delete cascade on update cascade,
            "user_id" bigint not null references "users"("id") on delete cascade on update cascade,
            primary key ("set_id", "user_id")
    );
    
    create table "permission_sets_groups" (
            "set_id" bigint not null references "permission_sets"("id") on delete cascade on update cascade,
            "group_id" bigint not null references "groups"("id") on delete cascade on update cascade,
            primary key ("set_id", "group_id")
    );
    
    create table "user_permissions" (
            "id" bigserial primary key,
            "user_id" bigint not null references "users"("id") on delete cascade on update cascade,
            "permission_id" bigint not null references "permissions"("id") on delete cascade on update cascade,
            "priority" bigint not null,
            "set_id" bigint references "permission_sets"("id") on delete restrict on update cascade,
    
            "grant" boolean not null,
            "sudo" boolean not null default false,
            "self" boolean not null default false
    );
    
    create unique index "user_permissions_unique" on "user_permissions" ("user_id", "permission_id", coalesce("set_id", 0), "sudo", "self");
    
    create table "group_permissions" (
            "id" bigserial primary key,
            "group_id" bigint not null references "groups"("id") on delete cascade on update cascade,
            "permission_id" bigint not null references "permissions"("id") on delete cascade on update cascade,
            "priority" bigint not null,
            "set_id" bigint references "permission_sets"("id") on delete restrict on update cascade,
    
            "grant" boolean not null,
            "sudo" boolean not null default false,
            "self" boolean not null default false
    );
    
    create unique index "group_permissions_unique" on "group_permissions" ("group_id", "permission_id", coalesce("set_id", 0), "sudo", "self");
    
    with "acting_user_groups" as (
            select ggf."parent_group_id" as "group_id"
            from "groups_users" as gu
            inner join "groups_groups_flat" as ggf
            on ggf."group_id" = gu."group_id"
            where gu."user_id" = $1
    ), "target_user_groups" as (
            select ggf."parent_group_id" as "group_id"
            from "groups_users" as gu
            inner join "groups_groups_flat" as ggf
            on ggf."group_id" = gu."group_id"
            where gu."user_id" = $2
    ), "applicable_sets" as (
            select ps."id"
            from "permission_sets" as ps
            inner join "permission_sets_users" as psu
            on ps."id" = psu."set_id"
            where psu."user_id" = $2
    
            union all
    
            select ps."id"
            from "permission_sets" as ps
            inner join "permission_sets_groups" as psg
            on ps."id" = psg."set_id"
            inner join "target_user_groups" as tug
            on psg."group_id" = tug."group_id"
    ) select ep."grant" from (
            select up."grant", up."priority", up."sudo", up."self"
            from "user_permissions" as up
            inner join "permissions" as p
            on p."id" = up."permission_id"
            where p."key" = $3
            and up."user_id" = $1
            and (up."set_id" is null or up."set_id" in (select "id" from "applicable_sets"))
    
            union all
    
            select gp."grant", gp."priority", gp."sudo", gp."self"
            from "group_permissions" as gp
            inner join "permissions" as p
            on p."id" = gp."permission_id"
            inner join "acting_user_groups" as aug
            on gp."group_id" = aug."group_id"
            where p."key" = $3
            and (gp."set_id" is null or gp."set_id" in (select "id" from "applicable_sets"))
    ) as ep
    where not ep."sudo" and (not ep."self" or $1 = $2)
    order by ep."priority" desc, ep."grant" asc
    limit 1;
    
                                                                         QUERY PLAN                                                                     
    ----------------------------------------------------------------------------------------------------------------------------------------------------
     Limit  (cost=15156.11..15156.12 rows=1 width=9)
       CTE acting_user_groups
         ->  Hash Join  (cost=4101.76..6318.60 rows=2917 width=8)
               Hash Cond: (groups_groups_flat.group_id = gu.group_id)
               ->  CTE Scan on groups_groups_flat  (cost=4073.07..5369.47 rows=64820 width=16)
                     CTE groups_groups_flat
                       ->  Recursive Union  (cost=0.00..4073.07 rows=64820 width=16)
                             ->  Seq Scan on groups g  (cost=0.00..10.70 rows=70 width=8)
                             ->  Merge Join  (cost=175.97..276.60 rows=6475 width=16)
                                   Merge Cond: (ggf.parent_group_id = gg.group_id)
                                   ->  Sort  (cost=47.08..48.83 rows=700 width=16)
                                         Sort Key: ggf.parent_group_id
                                         ->  WorkTable Scan on groups_groups_flat ggf  (cost=0.00..14.00 rows=700 width=16)
                                   ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
                                         Sort Key: gg.group_id
                                         ->  Seq Scan on groups_groups gg  (cost=0.00..28.50 rows=1850 width=16)
               ->  Hash  (cost=28.57..28.57 rows=9 width=8)
                     ->  Bitmap Heap Scan on groups_users gu  (cost=18.03..28.57 rows=9 width=8)
                           Recheck Cond: (user_id = 1)
                           ->  Bitmap Index Scan on groups_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                 Index Cond: (user_id = 1)
       CTE target_user_groups
         ->  Hash Join  (cost=4101.76..6318.60 rows=2917 width=8)
               Hash Cond: (groups_groups_flat_1.group_id = gu_1.group_id)
               ->  CTE Scan on groups_groups_flat groups_groups_flat_1  (cost=4073.07..5369.47 rows=64820 width=16)
                     CTE groups_groups_flat
                       ->  Recursive Union  (cost=0.00..4073.07 rows=64820 width=16)
                             ->  Seq Scan on groups g_1  (cost=0.00..10.70 rows=70 width=8)
                             ->  Merge Join  (cost=175.97..276.60 rows=6475 width=16)
                                   Merge Cond: (ggf_1.parent_group_id = gg_1.group_id)
                                   ->  Sort  (cost=47.08..48.83 rows=700 width=16)
                                         Sort Key: ggf_1.parent_group_id
                                         ->  WorkTable Scan on groups_groups_flat ggf_1  (cost=0.00..14.00 rows=700 width=16)
                                   ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
                                         Sort Key: gg_1.group_id
                                         ->  Seq Scan on groups_groups gg_1  (cost=0.00..28.50 rows=1850 width=16)
               ->  Hash  (cost=28.57..28.57 rows=9 width=8)
                     ->  Bitmap Heap Scan on groups_users gu_1  (cost=18.03..28.57 rows=9 width=8)
                           Recheck Cond: (user_id = 2)
                           ->  Bitmap Index Scan on groups_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                 Index Cond: (user_id = 2)
       CTE applicable_sets
         ->  Append  (cost=18.18..1187.50 rows=26991 width=8)
               ->  Nested Loop  (cost=18.18..62.21 rows=9 width=8)
                     ->  Bitmap Heap Scan on permission_sets_users psu  (cost=18.03..28.57 rows=9 width=8)
                           Recheck Cond: (user_id = 2)
                           ->  Bitmap Index Scan on permission_sets_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                 Index Cond: (user_id = 2)
                     ->  Index Only Scan using permission_sets_pkey on permission_sets ps  (cost=0.15..3.73 rows=1 width=8)
                           Index Cond: (id = psu.set_id)
               ->  Merge Join  (cost=441.40..855.38 rows=26982 width=8)
                     Merge Cond: (psg.group_id = tug.group_id)
                     ->  Sort  (cost=215.18..219.81 rows=1850 width=16)
                           Sort Key: psg.group_id
                           ->  Hash Join  (cost=60.85..114.79 rows=1850 width=16)
                                 Hash Cond: (psg.set_id = ps_1.id)
                                 ->  Seq Scan on permission_sets_groups psg  (cost=0.00..28.50 rows=1850 width=16)
                                 ->  Hash  (cost=32.60..32.60 rows=2260 width=8)
                                       ->  Seq Scan on permission_sets ps_1  (cost=0.00..32.60 rows=2260 width=8)
                     ->  Sort  (cost=226.22..233.51 rows=2917 width=8)
                           Sort Key: tug.group_id
                           ->  CTE Scan on target_user_groups tug  (cost=0.00..58.34 rows=2917 width=8)
       ->  Sort  (cost=1331.40..1331.44 rows=16 width=9)
             Sort Key: "*SELECT* 1".priority DESC, "*SELECT* 1"."grant"
             ->  Append  (cost=607.60..1331.32 rows=16 width=9)
                   ->  Subquery Scan on "*SELECT* 1"  (cost=607.60..623.72 rows=1 width=9)
                         ->  Nested Loop  (cost=607.60..623.71 rows=1 width=9)
                               Join Filter: (up.permission_id = p.id)
                               ->  Index Scan using user_permissions_unique on user_permissions up  (cost=607.45..615.54 rows=1 width=17)
                                     Index Cond: ((user_id = 1) AND (sudo = false) AND (self = false))
                                     Filter: ((NOT sudo) AND (NOT self) AND ((set_id IS NULL) OR (hashed SubPlan 6)))
                                     SubPlan 6
                                       ->  CTE Scan on applicable_sets  (cost=0.00..539.82 rows=26991 width=8)
                               ->  Index Scan using permissions_key_key on permissions p  (cost=0.14..8.16 rows=1 width=8)
                                     Index Cond: ((key)::text = 'view-email-address'::text)
                   ->  Subquery Scan on "*SELECT* 2"  (cost=638.02..707.60 rows=15 width=9)
                         ->  Hash Join  (cost=638.02..707.45 rows=15 width=9)
                               Hash Cond: (aug.group_id = gp.group_id)
                               ->  CTE Scan on acting_user_groups aug  (cost=0.00..58.34 rows=2917 width=8)
                               ->  Hash  (cost=638.01..638.01 rows=1 width=17)
                                     ->  Nested Loop  (cost=607.60..638.01 rows=1 width=17)
                                           ->  Index Scan using permissions_key_key on permissions p_1  (cost=0.14..8.16 rows=1 width=8)
                                                 Index Cond: ((key)::text = 'view-email-address'::text)
                                           ->  Index Scan using group_permissions_unique on group_permissions gp  (cost=607.45..629.84 rows=1 width=25)
                                                 Index Cond: ((permission_id = p_1.id) AND (sudo = false) AND (self = false))
                                                 Filter: ((NOT sudo) AND (NOT self) AND ((set_id IS NULL) OR (hashed SubPlan 7)))
                                                 SubPlan 7
                                                   ->  CTE Scan on applicable_sets applicable_sets_1  (cost=0.00..539.82 rows=26991 width=8)
    (88 rows)
    


  • Ok, I added a view for user-and-groups-that-the-user-is-in permissions:

    create view "guest_permissions_flat" as
            select gp."id", gp."permission_id", gp."priority", gp."set_id", gp."grant"
            from "group_permissions" as gp
            where gp."group_id" = -2;
    
    create view "user_permissions_flat" as
            select up."id", up."user_id", up."permission_id", up."priority", up."set_id", up."grant", up."sudo", up."self"
            from "user_permissions" as up
    union all
            select gp."id", gu."user_id", gp."permission_id", gp."priority", gp."set_id", gp."grant", gp."sudo", gp."self"
            from "group_permissions" as gp
            inner join "groups_groups_flat" as ggf
            on gp."group_id" = ggf."parent_group_id"
            inner join "groups_users" as gu
            on gu."group_id" = ggf."group_id";
    
    with "target_user_groups" as (
            select ggf."parent_group_id" as "group_id"
            from "groups_users" as gu
            inner join "groups_groups_flat" as ggf
            on ggf."group_id" = gu."group_id"
            where gu."user_id" = 2
    ), "applicable_sets" as (
            select ps."id"
            from "permission_sets" as ps
            inner join "permission_sets_users" as psu
            on ps."id" = psu."set_id"
            where psu."user_id" = 2
    
            union all
    
            select ps."id"
            from "permission_sets" as ps
            inner join "permission_sets_groups" as psg
            on ps."id" = psg."set_id"
            inner join "target_user_groups" as tug
            on psg."group_id" = tug."group_id"
    ) select upf."grant"
    from "user_permissions_flat" as upf
    inner join "permissions" as p
    on p."id" = upf."permission_id"
    where p."key" = 'view-email-address'
    and upf."user_id" = 1
    and (upf."set_id" is null or upf."set_id" in (select "id" from "applicable_sets"))
    and not upf."sudo"
    and (not upf."self" or 1 = 2)
    order by upf."priority" desc, upf."grant" asc
    limit 1;
    
                                                                          QUERY PLAN                                                                      
    ------------------------------------------------------------------------------------------------------------------------------------------------------
     Limit  (cost=15314.80..15314.80 rows=1 width=9)
       CTE target_user_groups
         ->  Hash Join  (cost=4101.76..6318.60 rows=2917 width=8)
               Hash Cond: (groups_groups_flat_1.group_id = gu_1.group_id)
               ->  CTE Scan on groups_groups_flat groups_groups_flat_1  (cost=4073.07..5369.47 rows=64820 width=16)
                     CTE groups_groups_flat
                       ->  Recursive Union  (cost=0.00..4073.07 rows=64820 width=16)
                             ->  Seq Scan on groups g  (cost=0.00..10.70 rows=70 width=8)
                             ->  Merge Join  (cost=175.97..276.60 rows=6475 width=16)
                                   Merge Cond: (ggf.parent_group_id = gg.group_id)
                                   ->  Sort  (cost=47.08..48.83 rows=700 width=16)
                                         Sort Key: ggf.parent_group_id
                                         ->  WorkTable Scan on groups_groups_flat ggf  (cost=0.00..14.00 rows=700 width=16)
                                   ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
                                         Sort Key: gg.group_id
                                         ->  Seq Scan on groups_groups gg  (cost=0.00..28.50 rows=1850 width=16)
               ->  Hash  (cost=28.57..28.57 rows=9 width=8)
                     ->  Bitmap Heap Scan on groups_users gu_1  (cost=18.03..28.57 rows=9 width=8)
                           Recheck Cond: (user_id = 2)
                           ->  Bitmap Index Scan on groups_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                 Index Cond: (user_id = 2)
       CTE applicable_sets
         ->  Append  (cost=18.18..1187.50 rows=26991 width=8)
               ->  Nested Loop  (cost=18.18..62.21 rows=9 width=8)
                     ->  Bitmap Heap Scan on permission_sets_users psu  (cost=18.03..28.57 rows=9 width=8)
                           Recheck Cond: (user_id = 2)
                           ->  Bitmap Index Scan on permission_sets_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                 Index Cond: (user_id = 2)
                     ->  Index Only Scan using permission_sets_pkey on permission_sets ps  (cost=0.15..3.73 rows=1 width=8)
                           Index Cond: (id = psu.set_id)
               ->  Merge Join  (cost=441.40..855.38 rows=26982 width=8)
                     Merge Cond: (psg.group_id = tug.group_id)
                     ->  Sort  (cost=215.18..219.81 rows=1850 width=16)
                           Sort Key: psg.group_id
                           ->  Hash Join  (cost=60.85..114.79 rows=1850 width=16)
                                 Hash Cond: (psg.set_id = ps_1.id)
                                 ->  Seq Scan on permission_sets_groups psg  (cost=0.00..28.50 rows=1850 width=16)
                                 ->  Hash  (cost=32.60..32.60 rows=2260 width=8)
                                       ->  Seq Scan on permission_sets ps_1  (cost=0.00..32.60 rows=2260 width=8)
                     ->  Sort  (cost=226.22..233.51 rows=2917 width=8)
                           Sort Key: tug.group_id
                           ->  CTE Scan on target_user_groups tug  (cost=0.00..58.34 rows=2917 width=8)
       ->  Sort  (cost=7808.69..7808.72 rows=11 width=9)
             Sort Key: up.priority DESC, up."grant"
             ->  Nested Loop  (cost=607.60..7808.63 rows=11 width=9)
                   Join Filter: (up.permission_id = p.id)
                   ->  Index Scan using permissions_key_key on permissions p  (cost=0.14..8.16 rows=1 width=8)
                         Index Cond: ((key)::text = 'view-email-address'::text)
                   ->  Append  (cost=607.45..7774.07 rows=2112 width=17)
                         ->  Index Scan using user_permissions_unique on user_permissions up  (cost=607.45..615.54 rows=1 width=17)
                               Index Cond: ((user_id = 1) AND (sudo = false) AND (self = false))
                               Filter: ((NOT sudo) AND (NOT self) AND ((set_id IS NULL) OR (hashed SubPlan 4)))
                               SubPlan 4
                                 ->  CTE Scan on applicable_sets  (cost=0.00..539.82 rows=26991 width=8)
                         ->  Subquery Scan on "*SELECT* 2"  (cost=4734.16..7158.53 rows=2111 width=17)
                               Filter: (("*SELECT* 2".set_id IS NULL) OR (hashed SubPlan 4))
                               ->  Hash Join  (cost=4126.86..6498.74 rows=4200 width=25)
                                     Hash Cond: (groups_groups_flat.parent_group_id = gp.group_id)
                                     ->  Hash Join  (cost=4101.76..6318.60 rows=2917 width=8)
                                           Hash Cond: (groups_groups_flat.group_id = gu.group_id)
                                           ->  CTE Scan on groups_groups_flat  (cost=4073.07..5369.47 rows=64820 width=16)
                                                 CTE groups_groups_flat
                                                   ->  Recursive Union  (cost=0.00..4073.07 rows=64820 width=16)
                                                         ->  Seq Scan on groups g_1  (cost=0.00..10.70 rows=70 width=8)
                                                         ->  Merge Join  (cost=175.97..276.60 rows=6475 width=16)
                                                               Merge Cond: (ggf_1.parent_group_id = gg_1.group_id)
                                                               ->  Sort  (cost=47.08..48.83 rows=700 width=16)
                                                                     Sort Key: ggf_1.parent_group_id
                                                                     ->  WorkTable Scan on groups_groups_flat ggf_1  (cost=0.00..14.00 rows=700 width=16)
                                                               ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
                                                                     Sort Key: gg_1.group_id
                                                                     ->  Seq Scan on groups_groups gg_1  (cost=0.00..28.50 rows=1850 width=16)
                                           ->  Hash  (cost=28.57..28.57 rows=9 width=8)
                                                 ->  Bitmap Heap Scan on groups_users gu  (cost=18.03..28.57 rows=9 width=8)
                                                       Recheck Cond: (user_id = 1)
                                                       ->  Bitmap Index Scan on groups_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                                             Index Cond: (user_id = 1)
                                     ->  Hash  (cost=21.50..21.50 rows=288 width=33)
                                           ->  Seq Scan on group_permissions gp  (cost=0.00..21.50 rows=288 width=33)
                                                 Filter: ((NOT sudo) AND (NOT self))
                               SubPlan 4
                                 ->  CTE Scan on applicable_sets  (cost=0.00..539.82 rows=26991 width=8)
    (82 rows)
    

  • Java Dev

    @ben_lubar If this is for nodebb, or otherwise for a platform with a persistent server process (IE not PHP), how often are you going to pull this from DB anyway? Isn't it more efficient to keep it cached in memory?



  • @PleegWat this isn't for NodeBB. It's a side project forum I'm making for !!Fun!! (which seems to be what everyone on tdwtf does at one point or other).

    Ideally, I'd like to have the database handle all the stuff that needs to be synchronized so that there could be multiple instances of the forum software on multiple machines attached to the same database (or a slave of the same master database) and banning someone would take effect immediately.


  • Java Dev

    @ben_lubar said in Permissions table:

    this isn't for NodeBB

    Could've guessed, nodebb isn't on postgres.

    @ben_lubar said in Permissions table:

    Ideally, I'd like to have the database handle all the stuff that needs to be synchronized so that there could be multiple instances of the forum software on multiple machines attached to the same database (or a slave of the same master database) and banning someone would take effect immediately.

    Yeah, that'll require some level of DB. Sure, you could do something like use a version field to check if your cache is up-to-date, but that's the kind of optimization you shouldn't do till you know you need it.



  • Let's throw in some more views to make this cleaner:

    create view "permission_sets_groups_flat" as
            select psg."set_id", ggf."group_id"
            from "permission_sets_groups" as psg
            inner join "groups_groups_flat" as ggf
            on ggf."parent_group_id" = psg."group_id";
    
    create view "permission_sets_users_flat" as
            select psu."set_id", psu."user_id"
            from "permission_sets_users" as psu
    union all
            select psg."set_id", gu."user_id"
            from "permission_sets_groups" as psg
            inner join "groups_groups_flat" as ggf
            on psg."group_id" = ggf."parent_group_id"
            inner join "groups_users" as gu
            on gu."group_id" = ggf."group_id";
    
    explain select upf."grant"
    from "user_permissions_flat" as upf
    inner join "permissions" as p
    on p."id" = upf."permission_id"
    where p."key" = 'view-email-address'
    and upf."user_id" = 1
    and (upf."set_id" is null or upf."set_id" in (select psuf."set_id" from "permission_sets_users_flat" as psuf where psuf."user_id" = 2))
    and not upf."sudo"
    and (not upf."self" or 1 = 2)
    order by upf."priority" desc, upf."grant" asc
    limit 1;
    
                                                                                QUERY PLAN                                                                            
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Limit  (cost=21969.60..21969.61 rows=1 width=9)
       ->  Sort  (cost=21969.60..21969.63 rows=11 width=9)
             Sort Key: up.priority DESC, up."grant"
             ->  Nested Loop  (cost=7688.05..21969.55 rows=11 width=9)
                   Join Filter: (up.permission_id = p.id)
                   ->  Index Scan using permissions_key_key on permissions p  (cost=0.14..8.16 rows=1 width=8)
                         Index Cond: ((key)::text = 'view-email-address'::text)
                   ->  Append  (cost=7687.91..21934.99 rows=2112 width=17)
                         ->  Index Scan using user_permissions_unique on user_permissions up  (cost=7687.91..7695.99 rows=1 width=17)
                               Index Cond: ((user_id = 1) AND (sudo = false) AND (self = false))
                               Filter: ((NOT sudo) AND (NOT self) AND ((set_id IS NULL) OR (hashed SubPlan 2)))
                               SubPlan 2
                                 ->  Append  (cost=18.03..7620.28 rows=26990 width=8)
                                       ->  Bitmap Heap Scan on permission_sets_users psu  (cost=18.03..28.57 rows=9 width=8)
                                             Recheck Cond: (user_id = 2)
                                             ->  Bitmap Index Scan on permission_sets_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                                   Index Cond: (user_id = 2)
                                       ->  Subquery Scan on "*SELECT* 2_1"  (cost=4153.38..7591.71 rows=26981 width=8)
                                             ->  Hash Join  (cost=4153.38..7321.90 rows=26981 width=8)
                                                   Hash Cond: (groups_groups_flat_1.parent_group_id = psg.group_id)
                                                   ->  Hash Join  (cost=4101.76..6318.60 rows=2917 width=8)
                                                         Hash Cond: (groups_groups_flat_1.group_id = gu_1.group_id)
                                                         ->  CTE Scan on groups_groups_flat groups_groups_flat_1  (cost=4073.07..5369.47 rows=64820 width=16)
                                                               CTE groups_groups_flat
                                                                 ->  Recursive Union  (cost=0.00..4073.07 rows=64820 width=16)
                                                                       ->  Seq Scan on groups g  (cost=0.00..10.70 rows=70 width=8)
                                                                       ->  Merge Join  (cost=175.97..276.60 rows=6475 width=16)
                                                                             Merge Cond: (ggf.parent_group_id = gg.group_id)
                                                                             ->  Sort  (cost=47.08..48.83 rows=700 width=16)
                                                                                   Sort Key: ggf.parent_group_id
                                                                                   ->  WorkTable Scan on groups_groups_flat ggf  (cost=0.00..14.00 rows=700 width=16)
                                                                             ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
                                                                                   Sort Key: gg.group_id
                                                                                   ->  Seq Scan on groups_groups gg  (cost=0.00..28.50 rows=1850 width=16)
                                                         ->  Hash  (cost=28.57..28.57 rows=9 width=8)
                                                               ->  Bitmap Heap Scan on groups_users gu_1  (cost=18.03..28.57 rows=9 width=8)
                                                                     Recheck Cond: (user_id = 2)
                                                                     ->  Bitmap Index Scan on groups_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                                                           Index Cond: (user_id = 2)
                                                   ->  Hash  (cost=28.50..28.50 rows=1850 width=16)
                                                         ->  Seq Scan on permission_sets_groups psg  (cost=0.00..28.50 rows=1850 width=16)
                         ->  Subquery Scan on "*SELECT* 2"  (cost=11814.61..14238.99 rows=2111 width=17)
                               Filter: (("*SELECT* 2".set_id IS NULL) OR (hashed SubPlan 2))
                               ->  Hash Join  (cost=4126.86..6498.74 rows=4200 width=25)
                                     Hash Cond: (groups_groups_flat.parent_group_id = gp.group_id)
                                     ->  Hash Join  (cost=4101.76..6318.60 rows=2917 width=8)
                                           Hash Cond: (groups_groups_flat.group_id = gu.group_id)
                                           ->  CTE Scan on groups_groups_flat  (cost=4073.07..5369.47 rows=64820 width=16)
                                                 CTE groups_groups_flat
                                                   ->  Recursive Union  (cost=0.00..4073.07 rows=64820 width=16)
                                                         ->  Seq Scan on groups g_1  (cost=0.00..10.70 rows=70 width=8)
                                                         ->  Merge Join  (cost=175.97..276.60 rows=6475 width=16)
                                                               Merge Cond: (ggf_1.parent_group_id = gg_1.group_id)
                                                               ->  Sort  (cost=47.08..48.83 rows=700 width=16)
                                                                     Sort Key: ggf_1.parent_group_id
                                                                     ->  WorkTable Scan on groups_groups_flat ggf_1  (cost=0.00..14.00 rows=700 width=16)
                                                               ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
                                                                     Sort Key: gg_1.group_id
                                                                     ->  Seq Scan on groups_groups gg_1  (cost=0.00..28.50 rows=1850 width=16)
                                           ->  Hash  (cost=28.57..28.57 rows=9 width=8)
                                                 ->  Bitmap Heap Scan on groups_users gu  (cost=18.03..28.57 rows=9 width=8)
                                                       Recheck Cond: (user_id = 1)
                                                       ->  Bitmap Index Scan on groups_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                                             Index Cond: (user_id = 1)
                                     ->  Hash  (cost=21.50..21.50 rows=288 width=33)
                                           ->  Seq Scan on group_permissions gp  (cost=0.00..21.50 rows=288 width=33)
                                                 Filter: ((NOT sudo) AND (NOT self))
                               SubPlan 2
                                 ->  Append  (cost=18.03..7620.28 rows=26990 width=8)
                                       ->  Bitmap Heap Scan on permission_sets_users psu  (cost=18.03..28.57 rows=9 width=8)
                                             Recheck Cond: (user_id = 2)
                                             ->  Bitmap Index Scan on permission_sets_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                                   Index Cond: (user_id = 2)
                                       ->  Subquery Scan on "*SELECT* 2_1"  (cost=4153.38..7591.71 rows=26981 width=8)
                                             ->  Hash Join  (cost=4153.38..7321.90 rows=26981 width=8)
                                                   Hash Cond: (groups_groups_flat_1.parent_group_id = psg.group_id)
                                                   ->  Hash Join  (cost=4101.76..6318.60 rows=2917 width=8)
                                                         Hash Cond: (groups_groups_flat_1.group_id = gu_1.group_id)
                                                         ->  CTE Scan on groups_groups_flat groups_groups_flat_1  (cost=4073.07..5369.47 rows=64820 width=16)
                                                               CTE groups_groups_flat
                                                                 ->  Recursive Union  (cost=0.00..4073.07 rows=64820 width=16)
                                                                       ->  Seq Scan on groups g  (cost=0.00..10.70 rows=70 width=8)
                                                                       ->  Merge Join  (cost=175.97..276.60 rows=6475 width=16)
                                                                             Merge Cond: (ggf.parent_group_id = gg.group_id)
                                                                             ->  Sort  (cost=47.08..48.83 rows=700 width=16)
                                                                                   Sort Key: ggf.parent_group_id
                                                                                   ->  WorkTable Scan on groups_groups_flat ggf  (cost=0.00..14.00 rows=700 width=16)
                                                                             ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
                                                                                   Sort Key: gg.group_id
                                                                                   ->  Seq Scan on groups_groups gg  (cost=0.00..28.50 rows=1850 width=16)
                                                         ->  Hash  (cost=28.57..28.57 rows=9 width=8)
                                                               ->  Bitmap Heap Scan on groups_users gu_1  (cost=18.03..28.57 rows=9 width=8)
                                                                     Recheck Cond: (user_id = 2)
                                                                     ->  Bitmap Index Scan on groups_users_pkey  (cost=0.00..18.03 rows=9 width=0)
                                                                           Index Cond: (user_id = 2)
                                                   ->  Hash  (cost=28.50..28.50 rows=1850 width=16)
                                                         ->  Seq Scan on permission_sets_groups psg  (cost=0.00..28.50 rows=1850 width=16)
    (97 rows)
    


  • By the way, can I write and (upf."set_id" is null or upf."set_id" in (select psuf."set_id" from "permission_sets_users_flat" as psuf where psuf."user_id" = 2)) as a join?


  • Discourse touched me in a no-no place

    @ben_lubar
    I'd do this in Oracle, no idea if it works on Postgres.

    left outer join permission_sets_users_flat as psuf on upf.set_id = psuf.set_id and psuf.user_id = 2

    edit: actually, that might not do exactly the same thing...



  • @ben_lubar said in Permissions table:

    By the way, can I write and (upf."set_id" is null or upf."set_id" in (select psuf."set_id" from "permission_sets_users_flat" as psuf where psuf."user_id" = 2)) as a join?

    I wouldn't recommend it. The IN lookup either evaluates to true or false. A join would either exclude the row, or include it as many times as it is appears in the view. Unless there is a pretty hard guarantee that won't happen, I would stick with the subquery. Besides, subqueries usually execute faster in cases like this.


  • Garbage Person

    I haven't finished reading the OP yet, nevermind the rest of the thread but:

    negative-ID groups are special
    they can't be edited through the web UI
    they can't be added to group hierarchies
    the "guests" group has no users and is checked for permissions directly
    the "administrators" group has every possible permission as a "sudo" permission with the highest possible priority
    the "registered users" group contains every user on the forum except guests
    IDs are NEVER zero, which means I can use 0 to represent a nonexistent ID on the code side without needing to mess with pointers or nullable integers.

    This is TRWTF!!!!!

    Guests, administrators and registered users should have fixed ID's (negative or not) to facilitate their special handling, but the can't-be-edited/can't-be-added-to-heirarchies business rules should be based on a pair of flags attached on the group record itself. This will allow for future expansion and use cases you cannot envision at this point.

    There's also an argument for sudoers being a settable flag rather than being tied to a specific group.



  • @Weng said in Permissions table:

    There's also an argument for sudoers being a settable flag rather than being tied to a specific group.

    sudo is a property of the permission, so you could give any user a sudo permission. You can also have a sudo permission that denies something, but I'm not sure when that'd be useful.

    @Weng said in Permissions table:

    the can't-be-edited/can't-be-added-to-heirarchies business rules should be based on a pair of flags attached on the group record itself

    There already has to be special handling based on ID, since users can be manually added and removed from the administrators group but not the other two. I can't do it through the permissions system because administrators can get through that. It'll probably be a property of the group ID, not a field on the database record.


  • Garbage Person

    Recommendations:

    A. Implement sudoers as two distinct group memberships. In regular mode, they're members of whatever. In sudo mode, they have unlimited permissions. You can do this by adding a column to the user-to-group relationship table, indicating whether using that membership requires sudoing.

    For example, I could be a member of Administrators and Registered Users. Under normal operations, I only ever have Registered Users permissions. But then when I need to do shenanigans, I pop into sudo mode and also gain an Administrators membership.

    Why does this need to go into the tables? Future expansion/unenvisioned use cases. You should sudo into a "Game Moderators" group that has access to all the Mafia chat areas or whatever.

    B. Drop the concept of priority. The Windows ACL model is good enough - By default, you don't have permission for a given action, unless you have at least one allow permission anywhere in your group memberships, you have permission - UNLESS you have a DENY permission anywhere in your group memberships, in which case you don't - UNLESS you are a member of Superusers, in which case you do.

    This lets you shortcut a lot of logic at every permissions check, and you will be checking permissions frequently, so it's performance critical. You can, however, still achieve all the effects of a prioritized system (you may need a few more groups in crazy edge cases) - and it's easier for administrators to grok.

    C. If you want to assign permissions directly to individual users, create each user a group object with just them in it, and prevent that group from being managed. This way, you only have to do your logic against the group relations. This allows you to remove user_id from your grants table.

    D. To facilitate the actual permissions check, when a user logs in, enumerate their group memberships. And then enumerate the membership of those groups in other groups. And so on and so forth, and stuff the resulting flattened list of enumerated groups in a database table as a cache. Redo this periodically thereafter - every 5 minutes or so.

    Whenever (rate limit this per user or it's a major DOS vector) a user hits a permission deny, redo that enumeration and check a second time. This could be optimized by globally storing the last time group memberships were changed aside from initial account creation, and not bothering to redo the check if they haven't. On a forum, group memberships are actually pretty static.

    Note that this cached permissions shenanigans will cause a time lag if a user loses permissions or is added to a DENY., though having permissions added will be immediate. Unless you go through and invalidate the cache for affected users when you change the permissions, which might or might not be practical, I haven't really thought through it.

    E. If you do D, your permissions check becomes 3 steps (in psuedosql):

    1. SELECT COUNT(*) FROM usergroupcache WHERE user = 'weng' AND group = 'administrators - if >0, return true
    2. SELECT grant, COUNT(*) FROM usergroupcache JOIN permissions ON groupid WHERE user = 'weng' GROUP BY grant - Assuming that grant=0 and 1 mean deny and allow respectively, if the deny result is >0, return false and if the allow result is >0, return true, otherwise return false.

    YMMV, HTH. I hate doing permissions systems, every last one of them ends up fiddly and awkward, but this is my current thinking.



  • @Weng said in Permissions table:

    A. Implement sudoers as two distinct group memberships. In regular mode, they're members of whatever. In sudo mode, they have unlimited permissions. You can do this by adding a column to the user-to-group relationship table, indicating whether using that membership requires sudoing.

    For example, I could be a member of Administrators and Registered Users. Under normal operations, I only ever have Registered Users permissions. But then when I need to do shenanigans, I pop into sudo mode and also gain an Administrators membership.

    Right now, any user or group can have sudo permissions, and users can be in multiple groups, so a Global Moderators group could be able to access the staff forum in normal mode and be able to edit other users' posts in sudo mode. So basically what you're suggesting but without needing two groups.

    @Weng said in Permissions table:

    B. Drop the concept of priority. The Windows ACL model is good enough

    • By default, you don't have permission for a given action

    nobody has any permissions

    • unless you have at least one allow permission anywhere in your group memberships, you have permission

    select exists (permission)

    • UNLESS you have a DENY permission anywhere in your group memberships, in which case you don't

    select has_permission order by has_permission asc limit 1

    • UNLESS you are a member of Superusers, in which case you do.

    sudoing administrators can skip the database round trip, but that doesn't help much when any user could have a sudo permission.

    For example, what if we had this permission set (in groups of ascending order of priority):

    • global moderators can see all topics and posts in sudo mode in every category
    • global moderators can edit other users' posts in sudo mode in every category

    • nobody can edit posts in the current game mafia topics
    • the current game faction/club ded topics are hidden to everybody
    • the current game main thread cannot be posted to by anybody

    • the current game main thread can be posted to by living players
    • the current game faction topics can be seen by club ded
    • the current game faction/club ded topics can be seen and posted to by their respective groups

    • current game mafia GMs (which is a subgroup of club ded) can edit posts in the current game's threads in sudo mode

    • [implicit] administrators can do anything in sudo mode

    We have at least two levels of explicit sudo that can't be combined, and unless there's a permission set for every topic other than the current game, this can't be done with the Windows ACL model.

    @Weng said in Permissions table:

    C. If you want to assign permissions directly to individual users, create each user a group object with just them in it, and prevent that group from being managed. This way, you only have to do your logic against the group relations. This allows you to remove user_id from your grants table.

    Yeah, it does feel kind of weird to have a whole separate table for user-specific permissions. It was kind of based on how Maciejasjmj has moderator permissions on the mafia forums, but making a group named "mafia GM" would be more semantically correct and easier to manage.

    @Weng said in Permissions table:

    D. To facilitate the actual permissions check, when a user logs in, enumerate their group memberships. And then enumerate the membership of those groups in other groups.

    I currently have a mapping from groups to the set of their ancestors as a materialized view, and groups being added or removed isn't something that's going to happen anywhere near as often as generic permissions checks, so this is effectively already just select group_id from magical_group_hierarchy_view join groups_users on ... where user_id = $1.

    Since this is a webapp, I can cache permissions for the duration of the page load. Caching things between page loads has the problem of where to store the data - I could store it in sessions, but the session data would need to be stored on the server somewhere, and at that point I've just moved the permissions data somewhere else in the database that I now need to manually update. I could also cache the data inside the user's encrypted authentication cookie, but that would get pretty long once there are a lot of permissions in the system.


  • Java Dev

    @ben_lubar said in Permissions table:

    You can also have a sudo permission that denies something, but I'm not sure when that'd be useful.

    In the same cases where a normal negative permission is useful, when the base permission is sudo?

    @ben_lubar said in Permissions table:

    I currently have a mapping from groups to the set of their ancestors as a materialized view

    Seems like it should be effective. I guess you might as well join permissions into that too?



  • @PleegWat said in Permissions table:

    I guess you might as well join permissions into that too?

    That'd bloat the table size quite a bit, and permissions change a lot more than the group hierarchy (although still less than the amount they're accessed).


  • Java Dev

    @ben_lubar said in Permissions table:

    That'd bloat the table size quite a bit, and permissions change a lot more than the group hierarchy

    You know your dataset best. I'd say everything that tends to change less than once a day is effectively constant.

    Of course, changes do tend to come in bursts.



  • @ben_lubar Password is nullable :wtf:


  • Java Dev

    @JazzyJosh Locked accouts?



  • @PleegWat Thought of that literally 2 seconds before you posted.



  • I came here because it said Breast Programming somewhere. Where are the pictures ?



  • @stillwater said in Permissions table:

    Where are the pictures ?

    0_1459828977325_upload-73396eb8-5302-41af-932c-f5e431582f12


  • Notification Spam Recipient

    @ben_lubar That's beautifully horrifying.



  • @ben_lubar Had no idea this was called Breast Programming. TIL.


  • Notification Spam Recipient

    @stillwater said in Permissions table:

    Had no idea this was called Breast Programming. TIL.

    Was. Title change, but of course.

    Also: No results for breasts in the search. I think this is a problem...

    https://what.thedailywtf.com/search/breast?in=titles&sortBy=timestamp&sortDirection=desc&showAs=posts



  • @Tsaukpaetra Surely someone will look into something as important as this ?


  • Notification Spam Recipient

    @stillwater said in Permissions table:

    Surely someone will look into something as important as this ?

    Shirley no longer works here, and I'm not entirely certain if they're hiring...