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

[seankross]

I completely agree that we should not use dplyr verbs. Today we discussed creating functions like rls_grant_col() or rls_grant_rows(), although that needs some work, especially for the rows. Maybe we should call it rls_grant_rows_where() although that's pretty verbose. Also we need to discuss whether we should use NSE, right now I am leaning away from NSE.

[sckott]

right now I am leaning away from NSE

curious can you give your reasoning?

[sckott]

I've landed on

1. Construct column policies alone
2. Construct row policies alone
   3. Construct column and row policies together in one code block

(removing idea for doing column and row together - i think it's a bit too cumbersome)

@seankross After getting lastet code from branch priv can you run code for

Auto pipe on vs off and rls_run

There's 3 ways to use these functions:

• when auto piping is off (auto_pipe(FALSE), this is the default when the pkg is loaded) these functions give back s3 objects
• when auto piping is on (auto_pipe(TURE)) these functions then run rls_run as the last command, hitting postgres
• with auto pipe off the user could assign output of a column or row level thing to a variable then pass it to rls_run or use rls_run as the last fxn call in a pipe chain to run the query against postgres

Rows

library(DBI)
library(RPostgres)
con <- dbConnect(Postgres())
if (!dbExistsTable(con, "passwd")) {
   setup_example_table(con, "passwd")
}

rls_tbl(con, "passwd") %>%
  row_policy("a_policy") %>%
  commands(update) %>%
  rows_existing(TRUE) %>%
  rows_new(TRUE) %>%
  to(jane)

rls_tbl(con, "passwd") %>%
  row_policy("my_policy") %>%
  commands(update) %>%
  rows_existing(sql = 'current_user = "user_name"') %>%
  rows_new(home_phone == "098-765-4321") %>%
  to(jane)

Noites:

• note use of to function here too in addition to column use
• commands is a pretty thin layer - could add which sql command to use to row_policy or somewhere else?
• rows_existing is a new naming change from the postgres syntax using
• rows_new is a new naming change from the postgres syntax check
• rows_existing(TRUE) and rows_new(TRUE) are special cases that say "all rows"
• for both of rows_existing and rows_new there's two ways to pass in queries, with the 2nd parameter using NSE, or the 3rd parameter (sql) using sql syntax. e.g., see the 2nd query above where the user needs to do SQL specific syntax e.g,. = insetad of == to mean equality

Columns

library(rls)
library(RPostgres)
library(dplyr)

con <- dbConnect(Postgres())

# create role if not created already
if (!"jane" %in% pull(rls_list_roles(con), rolname)) {
  dbExecute(con, "CREATE ROLE jane")
}

# Turn on auto sql execution
auto_pipe(TRUE)

# GRANT UPDATE ON passwd TO jane
rls_tbl(con, "passwd") %>% 
  grant(update) %>% 
  to(jane)

# see the privileges for jane
rls_privileges(con, "passwd", "jane")

# REVOKE UPDATE ON passwd TO jane
rls_tbl(con, "passwd") %>% 
  revoke(update) %>% 
  from(jane)

# see the privileges for jane changed hopefully
rls_privileges(con, "passwd", "jane")

# Turn it off to see what the `privilege` class looks like
auto_pipe(FALSE)

#' # GRANT SELECT
#' #  (real_name, home_phone)
#' #  ON passwd
#' #  TO jane
rls_tbl(con, "passwd") %>%
  grant(
    select,
    cols = c("real_name", "home_phone")
  ) %>% 
  to(jane)

#' GRANT SELECT
#'  (real_name)
#'  UPDATE
#'  (home_phone)
#'  ON passwd
#'  TO jane
rls_tbl(con, "passwd") %>%
  grant(
    select,
    cols = "real_name"
  ) %>% 
  grant(
    update,
    cols = "home_phone"
  ) %>% 
  to(jane)

#' GRANT admins TO jane
rls_tbl(con, "passwd") %>% 
  grant(update) %>% 
  to(jane)

#' GRANT ALL PRIVILEGES ON passwd TO jane
#' # Note: (where all is short for "all privileges")
rls_tbl(con, "passwd") %>% 
  grant(all) %>% 
  to(jane)

#' GRANT SELECT ON passwd TO jane, bob, alice
rls_tbl(con, "passwd") %>% 
  grant(select) %>% 
  to(jane, bob, alice)

[sckott]

still to do:

• clean up translate_privilege docs and possibly code too
• Support special cases of permissions as a higher level set: view and edit

[sckott]

@seankross vignette is updated on priv branch now with the new fxns for colulmns and rows https://github.com/getwilds/rls/blob/priv/vignettes/rls.Rmd

[seankross]

Going through this now, have you actually tried using rls with redshift? May be premature at this point, but we should make sure to kick the tires eventually.

[sckott]

no not yet

[seankross]

Overall this is great and very exciting. I'm still a little muddy on how to think about rows_existing and rows_new. See my comments here: #13

[sckott]

cool thanks for looking and thanks for the great comments.