Construct policies more tabularly?

[sckott]

• can we make policy management more tidy?
• any way to construct policies in a tabular way?
• do policy elements map to a tabular fashion?

Don't know right now, but will investigate.

This relates to rls_construct_policy and perhaps to rls_create_policy as well

[sckott]

brainstorming - NOT sure if doing all of this is possible/easy enough or not yet

# CREATE POLICY a_policy ON passwd
#  FOR ALL
#  USING (true);
policy(a_policy) %>%
  on_table(passwd)

# CREATE POLICY my_policy ON passwd
#  FOR SELECT
#  USING (true);
policy(my_policy) %>%
  on_table(passwd) %>%
  command(select)

# CREATE POLICY user_mod_policy ON users
#. USING (user_name = current_user);
policy(user_mod_policy) %>%
  on_table(users) %>%
  command(select) %>%
  using(user_name = current_user)

# CREATE RLS POLICY policy_events
#  WITH (eventid INTEGER) AS ev
#  USING (
#    ev.eventid IN (SELECT eventid FROM tickit_sales_redshift WHERE qtysold <3)
#. );
policy(policy_events) %>%
  on_table(users) %>%
  command(select) %>%
  check(
    mutate(ev = as.integer(eventid))
  ) %>%
  using(
    ev %in%
      tbl(tickit_sales_redshift) %>%
      select(eventid) %>%
      filter(qtysold < 3)
  )

[seankross]

(policy3 <- rls_construct_policy(
  name = "user_mod",
  table = "passwd",
  command = "UPDATE",
  using = "(current_user = user_name)",
  check = "(
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  )"
))

using: select => view
check: delete, update, insert => edit

view, edit, delete

policy(name = "You can only view rows where your username matches user_name",
       table = "passwd",
       filter = current_user() == "user_name")

policy(name = "You can only view rows where your username is sean or scott",
       table = "passwd",
       filter = current_user() %in% c("sean", "scott"))

policy(name = "You can only view rows where your age is greater than 10",
       table = "passwd",
       filter = Age > 10)

policy(name = "You can only update the column 'Shell' to include certain shells",
       table = "passwd",
       check =  Shell %in% c("bash", "zsh"))

policy3 <- policy(name = "You can only view rows where your username matches user_name",
       table = "passwd",
       filter = current_user() == "user_name") %>%
  policy(name = "You can only update the column 'Shell' to include certain shells",
         table = "passwd",
         check =  Shell %in% c("bash", "zsh"))

row_policy
col_policy

dbExecute(con, "GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin")

col_policy(table = "passwd", 
           role = "admin",
           permissions = c(view(), edit(), delete()),
           which = all_columns())

dbExecute(con, "GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, home_dir, shell)
  ON passwd TO public"
)

col_policy(table = "passwd", 
           role = "public",
           permissions = "v",
           which = c("user_name", "uid", "gid", "real_name", "home_phone", "home_dir", "shell"))

# do we make out own S3 method for select?
tbl(con, "passwd") %>%
  col_policy(role = "public", permissions = "v") %>%
  select(user_name, uid, gid, real_name, home_phone, home_dir, shell)

[sckott]

@seankross see also #3 - just pushed branch so you can see what I was working on BEFORE we talked today

[sckott]

@seankross Found a repo that may prove useful https://github.com/cfeenstra67/sqlauthz as a model for what we're trying to do, e.g.,

# Grant a user or group read permissions on a limited set of rows and columns in a table
allow("bob", "select", resource)
    if resource == "api.mytable"
    and (resource.row.mycol == "abc" or resource.row.mycol2 < 12)
    and resource.row.col in ["mycol", "mycol3"];

not saying completely go that way - it's completely declarative, so you'd set out roles and users in a json file, and write rules like above in another file and then read those in. but at least take some inspiration from their approach

[sckott]

I've been brainstorming most of the morning today and I don't think we should use dplyr verbs as it's too confusing and doesn't map to what dplyr verbs actually do. .e.g., this block has a number of issues

tbl(con, "passwd") %>%
  col_policy(role = "public", permissions = "view") %>%
  select(user_name, uid, gid, real_name, home_phone, home_dir, shell)

1. we can make a new replacingtbl, that's fine, but we don't actually want to do work on the passwd table, we just want to set permissions/privileges for it
2. select, filter, etc. do specific things that most R users are familiar with, and I think to use them we'd have to modify their behavior, which seems confusing and not a good solution

I think it's better to use our own functions, e.g.,

rls_tbl(con, "passwd") %>% # i've made this fxn locally already
  # probably modify attributes on the output of rls_tbl
  col_policy(role = "public", permissions = "view") %>%
  # the columns we're giving access to for this policy
  rls_select(user_name, uid, gid, real_name, home_phone, home_dir, shell)  %>%
  # current_user is a special postgres fxn, and so doesn't work in a real dplyr::filter context
  rls_filter(current_user == user_name)

Or name them completely separate from dplyr verbs if we're not using the actual dplyr verbs