-
Notifications
You must be signed in to change notification settings - Fork 0
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Construct policies more tabularly? #4
Comments
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)
) |
(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) |
@seankross see also #3 - just pushed branch so you can see what I was working on BEFORE we talked today |
@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 |
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)
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 |
I completely agree that we should not use dplyr verbs. Today we discussed creating functions like |
curious can you give your reasoning? |
Don't know right now, but will investigate.
This relates to
rls_construct_policy
and perhaps torls_create_policy
as wellThe text was updated successfully, but these errors were encountered: