https://authzed.com logo
Join Discord
Powered by
Schema changes to reduce wide fanout
# spicedb
s
@Joey (sorry for the direct ping) Would it make much a difference if we changed the 
resource
to be
Copy code
definition resource {
  relation reader: user#member | access_token | role#member | service_account#member
  relation writer: user#member | access_token | role#member | service_account#member
  relation manager: user#member | access_token | role#member | service_account#member

  --- CHANGE ---
  permission manage = manager
  permission write = writer + manager
  permission read = reader + writer + manager
}
This probably won't be too helpful, but the db query for check permission (at least I'm assuming) is taking upwards of ~4 seconds
Copy code
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query      | SELECT * FROM (SELECT object_id, userset_namespace, userset_object_id, userset_relation FROM relation_tuple WHERE pg_visible_in_snapshot(created_xid,  $1 ) =  $2  AND pg_visible_in_snapshot(deleted_xid,  $3 ) =  $4  AND namespace =  $5  AND relation =  $6  AND object_id IN ( $7 , $8 , $9 , $10 , $11 , $12 ) AND ((userset_relation <>  $13 ))) AS results WHERE pg_xact_status($14::text::xid8) != $15
calls      | 412446
rows       | 9293897951
total_time | 13122517.57
mean_time  | 31.82
max_time   | 3899.52
percentage | 10.01
j
rows       | 9293897951
how many relationships do you have?
s
That number is the number of rows returns from all queries collectively since the db started recording stats
Copy code
table_name         | estimated_rows 
----------------------------+----------------
 relation_tuple             |         132640
theres only 1.3M rows in the relation table
you can rough math rows / call ~ 22533 but its impossible to say for sure how many rows are returns per query
oh sorry, 130K. not 1.3M
j
what index is that query using?
it shouldn't be that slow by any means
s
not sure I can tell that with what we have
j
are you on PG?
s
yes
j
I'd first try to run a vacuum and analyze to restat the tables
if that doesn't work, try enabling pg_hint
s
I have this info
Copy code
schema_name |         table_name         | seq_scan | seq_tup_read | idx_scan  | avg_rows 
-------------+----------------------------+----------+--------------+-----------+----------
 public      | relation_tuple             |  1685295 | 212090742193 | 247449504 |   125847
 public      | relation_tuple_transaction |  3738654 |    635035637 |   5588748 |      169
 public      | namespace_config           | 13937378 |     63508279 |         0 |        4
 public      | schema_revision            |   773746 |       773746 |         0 |        1
 public      | alembic_version            |   286571 |       286571 |           |        1
 public      | metadata                   |   273190 |       273190 |         0 |        1
j
if you're willing to lock DB for a time, you can run 
VACUUM FULL ANALYZE
to fully analyze and vacuum but it WILL LOCK YOUR DB
s
I can probably run that on our dev cluster.
what are we trying to accomplish?
its notibly slower when we're checking for a permision that resolves to 
false
a 
true
comes back pretty quick.
j
false has to scan for everything
I'm trying to tell PG to use the proper index
those is no world where 
SELECT * FROM (SELECT object_id, userset_namespace, userset_object_id, userset_relation FROM relation_tuple WHERE pg_visible_in_snapshot(created_xid,  $1 ) =  $2  AND pg_visible_in_snapshot(deleted_xid,  $3 ) =  $4  AND namespace =  $5  AND relation =  $6  AND object_id IN ( $7 , $8 , $9 , $10 , $11 , $12 ) AND ((userset_relation <>  $13 ))) AS results WHERE pg_xact_status($14::text::xid8) != $15
should be multiple seconds unless it is (a) returning an insane amount of data or (b) its using the wrong index
so unless your graph is heavily sharing the same resources
it should be pretty fast
s
what do you mean by same resources?
j
one document -> 100K groups -> users
s
the 
resource
definition in our schema is kind of generic where we are granting r/w access to classes of things (resources). not object level access. So those are reused a lot. we use roles to groupl those together. and all of our users get 1 of like 4 default roles. so i think yes. roles are shared quite a lot
one of the roles shows up 
13489
times in the relation_tuple table
on our dev cluster. its probably a bit higher on production
Copy code
select namespace, object_id, count(*) as total from relation_tuple group by namespace, object_id order by total DESC;

    namespace    |                     object_id                     | total 
-----------------+---------------------------------------------------+-------
 role            | 68e69c3237365e926746ca74                          | 59136
 role            | 64e4e46429090ea954d7981a                          | 45564
 role            | 64e4e46429090ea954d7981d                          | 21242
 role            | 64e4e46429090ea954d79817                          |  2318
 resource        | owner                                             |  1452
 resource        | pipeline                                          |    52
 resource        | role                                              |    42
<SNIP>
j
but does role walk directly to user
or indirectly?
s
Copy code
definition user {
  relation inheritor: access_token
  permission member = inheritor
}


definition role {
  relation inheritor: user#member | access_token | service_account#member
  relation parent_role: role#member
  permission member = inheritor + parent_role
  permission parent = parent_role
}
j
and the check is for 
access_token
?
s
checks are typically going to be against an access token ya, or the thing that owns it ( a user / service account).
j
so you might end up walking 59,136 items
which means those items need to be sent over the wire
s
thats no good.
any suggestions?
we basically use role inheritance to allow for custom role. or having to maintain 100 permissions on ever role My Custom Role = Admin + write:user
where admin is system defined and re-used a lot for example. 59136 times to be exact
j
at minimum, change your dispatch chunk size from 100 to ~1000
better 59 subproblems instead of 590
s
ok simple enough.
I'm assuming this isn't a common use case / pattern 🥺
j
not usually, no
but it does happen
s
it doesn't sound sustainable. Its still a new feature that people aren't doing much with. Its only going to get worse from here.
what would be the better way to model some thing like this?
does changing dispatch chunk size effect how much data is returned from the datastore? that seems to be the bottle neck spicedb itself is peforming pretty fine.
j
no
it reduces the number of subdispatches
s
ok. is there an acceptable way to model this kind of inheritence in a sane way? do you guys have a more appropriate chanel for modeling help like this?
j
this is the channel for any OSS usage
in your case, the only real solution is to either not have so many shared roles
or to have the subject not be an indirect off of the role
if 
relation inheritor: user#member
was instead 
relation inheritor: user
and you're checking for a user directly
then there is no need to load all the roles - we walk and check directly
IF the roles themselves are not used at a higher level, of course
s
All users have at least 1 role
j
that's fine - its whether you need to walk through the user
s
Hmm. Ok. I don't remember why we landed with the indirection for #member
j
query planner does support inverting those very wide rels, but its still experimental
I would recommend running auto vacuum
s
I did
j
any changes?
s
Not that I can see yet
Clarification, User has at least one role that almost certainly inherits from another
Would something like this make more sense. I think I brake the role loop
Copy code
definition user {}

definition service_account {}

definition access_token {
  relation user_owner: user
  relation service_owner: service_account
}

definition role {
  // Roles allow users, service accounts, and other roles to be members
  relation member: user | service_account | access_token | role#member | access_token#user_owner | access_token#service_owner
  
  // Computes the inheritance down the tree
  permission assigned_member = member
}

definition resource {
  relation manager: user | service_account | access_token | role#member
  relation writer: user | service_account | access_token | role#member
  relation reader: user | service_account | access_token | role#member

  permission manage = manager
  permission write = writer + manage
  permission read = reader + write
}
from what Im understanding, 
member
being a comple permission (inheritor + parent_role) rather than a relation is kind of the death of me. But I could be way off. The relationship traversal bit is a bit black box to me
j
only if you have MANY roles on the same resource
s
no, if I understand. we have a small number of roles
large number of users that are members of the roles
and a large number of service accounts that are members of those same roles
here is a few records for a role. there are basically 3 roles that are reused. so those roles have 10K relations pointed at them a peice https://cdn.discordapp.com/attachments/1526671675435913298/1527033330095231028/message.txt?ex=6a59301c&is=6a57de9c&hm=581a1c6475df721f9179c904b9a9f955fb76e8938db70943ae3eee678663d8ad&
i guess what I'm asking, is what would be a better way to model this so our permission checks aren't returning the 1/2 of the table set
j
have more specific roles, with less users on them?
s
ok, wrong question. does removing the 
user#member
branch here seem like it'll remove the need to walk through all the users? or does having 
role#member
on the resources necessitate the same thing effectively?
I know I'm taking a lot of your time. I appreciate your help + input 🙏
Copy code
relation writer: user | service_account | access_token | role#member
this bit basically. does including role#member mean that it will have to walk through all roles, and through all users that are a member of the role. because role.member = 
member: user | service_account | access_token | role#member
is better? or just a different way of creating the same problem?
j
all roles that are on 
writer
s
So we reworked the schema to have dedicated relations for each permission path + type (read + user, read +access_token, etc) we migrated all the data and re-worked our access patterns to follow suit. I think we have elimiated all of the potential fan out / in situations.
Copy code
definition role {
  relation parent_role: role#member

  relation inheritor_user: user
  relation inheritor_token: access_token
  relation inheritor_service_account: service_account

  permission member_user = inheritor_user + parent_role->member_user
  permission member_token = inheritor_token + parent_role->member_token
  permission member_service_account = inheritor_service_account + parent_role->member_service_account
}
Copy code
definition resource {
  relation reader_user: user
  relation reader_token: access_token
  relation reader_role: role
  relation reader_service_account: service_account

  relation writer_user: user
  relation writer_token: access_token
  relation writer_role: role
  relation writer_service_account: service_account

  relation manager_user: user
  relation manager_token: access_token
  relation manager_role: role
  relation manager_service_account: service_account

  permission manage_user = manager_user + manager_role->member_user
  permission manage_token = manager_token + manager_role->member_token
  permission manage_service_account = manager_service_account + manager_role->member_service_account

  permission write_user = writer_user + writer_role->member_user + manage_user
  permission write_token = writer_token + writer_role->member_token + manage_token
  permission write_service_account = writer_service_account + writer_role->member_service_account + manage_service_account

  permission read_user = reader_user + reader_role->member_user + write_user
  permission read_token = reader_token + reader_role->member_token + write_token
  permission read_service_account = reader_service_account + reader_role->member_service_account + write_service_account
}
The schema is a bit more complex but our check logic is contained in 1 spot so it isn't terrible to manage
j
yeah
if it isn't having to do that walk, the fanout would be significantly less
5 Views
PreviousNext
Powered by