rls.Rmd

---
title: "Getting Started"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Getting Started}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---



Row Level Security for PostgreSQL and AWS Redshift.

The following is a walk-through of using row level security in PostgreSQL, including checks to make sure policies behave as expected.


``` r
library(tibble)
library(DBI)
library(RPostgres)
library(dplyr)
library(dbplyr)
library(rls)
```

## Start PostgreSQL locally

Start Postgres however you do that.

## Create a connection


``` r
con <- dbConnect(Postgres())
```



## Create roles


``` r
# Administrator
dbExecute(con, "CREATE ROLE admin")
# Normal user
dbExecute(con, "CREATE ROLE bob")
# Another normal user
dbExecute(con, "CREATE ROLE alice")
```

## Create a table

Create the table (with no data)


``` r
invisible(dbExecute(con, "
  CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);
"))
```

Some sample data


``` r
sample_data <- tribble(
  ~user_name, ~pwhash, ~uid, ~gid, ~real_name, ~home_phone, ~home_dir, ~shell,
  'admin','xxx',0,0,'Admin','111-222-3333','/root','/bin/dash',
  'bob','xxx',1,1,'Bob','123-456-7890','/home/bob','/bin/zsh',
  'alice','xxx',2,1,'Alice','098-765-4321','/home/alice','/bin/zsh'
)
```

Append rows to the `passwd` table


``` r
rows_append(
  tbl(con, "passwd"),
  copy_inline(con, sample_data),
  in_place = TRUE
)
```

Check that the data is in the table


``` r
tbl(con, "passwd")
#> # Source:   table<"passwd"> [3 x 8]
#> # Database: postgres  [schambe3@/tmp:5432/schambe3]
#>   user_name pwhash   uid   gid real_name home_phone   home_dir    shell    
#>   <chr>     <chr>  <int> <int> <chr>     <chr>        <chr>       <chr>    
#> 1 admin     xxx        0     0 Admin     111-222-3333 /root       /bin/dash
#> 2 bob       xxx        1     1 Bob       123-456-7890 /home/bob   /bin/zsh 
#> 3 alice     xxx        2     1 Alice     098-765-4321 /home/alice /bin/zsh
```

## Enable row level security

Enable row level security with `rls_enable`


``` r
rls_enable(con, "passwd")
```

Then check that it worked with `rls_check_status`


``` r
rls_check_status(con, "passwd")
#> # A tibble: 1 × 3
#>   relname relrowsecurity relforcerowsecurity
#>   <chr>   <lgl>          <lgl>              
#> 1 passwd  TRUE           FALSE
```

## Create row level security policies

`admin_all` = Administrator can see all rows and add any rows


``` r
policy1 <- rls_tbl(con, "passwd") %>%
  row_policy("admin_all") %>%
  rows_existing(TRUE) %>%
  rows_new(TRUE) %>%
  to(admin)
policy1
#> <row_policy> admin_all
#>   as: PERMISSIVE
#>   user: admin
#>   existing rows: true
#>   new rows: true
#> # Source:   SQL [3 x 8]
#> # Database: postgres  [schambe3@/tmp:5432/schambe3]
#>     gid home_dir    home_phone   pwhash real_name shell       uid user_name
#>   <int> <chr>       <chr>        <chr>  <chr>     <chr>     <int> <chr>    
#> 1     0 /root       111-222-3333 xxx    Admin     /bin/dash     0 admin    
#> 2     1 /home/bob   123-456-7890 xxx    Bob       /bin/zsh      1 bob      
#> 3     1 /home/alice 098-765-4321 xxx    Alice     /bin/zsh      2 alice
rls_perform(policy1)
#> # A tibble: 1 × 1
#>   rows_affected
#>           <int>
#> 1             0
rls_policies(con)
#> # A tibble: 1 × 8
#>   schemaname tablename policyname permissive roles      cmd   qual  with_check
#>   <chr>      <chr>     <chr>      <chr>      <pq__name> <chr> <chr> <chr>     
#> 1 public     passwd    admin_all  PERMISSIVE {admin}    ALL   true  true
```

`all_view` = Normal users can view all rows


``` r
policy2 <- rls_tbl(con, "passwd") %>%
  row_policy("all_view") %>%
  commands(select) %>%
  rows_existing(TRUE)
policy2
#> <row_policy> all_view
#>   as: PERMISSIVE
#>   commands: select
#>   existing rows: true
#> # Source:   SQL [3 x 8]
#> # Database: postgres  [schambe3@/tmp:5432/schambe3]
#>     gid home_dir    home_phone   pwhash real_name shell       uid user_name
#>   <int> <chr>       <chr>        <chr>  <chr>     <chr>     <int> <chr>    
#> 1     0 /root       111-222-3333 xxx    Admin     /bin/dash     0 admin    
#> 2     1 /home/bob   123-456-7890 xxx    Bob       /bin/zsh      1 bob      
#> 3     1 /home/alice 098-765-4321 xxx    Alice     /bin/zsh      2 alice
rls_perform(policy2)
#> # A tibble: 1 × 1
#>   rows_affected
#>           <int>
#> 1             0
rls_policies(con)
#> # A tibble: 2 × 8
#>   schemaname tablename policyname permissive roles      cmd    qual  with_check
#>   <chr>      <chr>     <chr>      <chr>      <pq__name> <chr>  <chr> <chr>     
#> 1 public     passwd    admin_all  PERMISSIVE {admin}    ALL    true  true      
#> 2 public     passwd    all_view   PERMISSIVE {public}   SELECT true  <NA>
```

`user_mod` = Normal users can update their own records, but limit
which shells a normal user is allowed to set (Note: `current_user` is a special system function that's part of PostgreSQL and Redshift that returns the current user)


``` r
policy3 <- rls_tbl(con, "passwd") %>%
  row_policy("user_mod") %>%
  commands(update) %>%
  rows_existing(sql = 'current_user = user_name') %>%
  rows_new(sql = '
    current_user = user_name AND
    shell IN (\'/bin/bash\',\'/bin/sh\',\'/bin/zsh\')'
  )
policy3
#> <row_policy> user_mod
#>   as: PERMISSIVE
#>   commands: update
#>   existing rows: current_user = user_name
#>   new rows: 
#>     current_user = user_name AND
#>     shell IN ('/bin/bash','/bin/sh','/bin/zsh')
#> # Source:   SQL [3 x 8]
#> # Database: postgres  [schambe3@/tmp:5432/schambe3]
#>     gid home_dir    home_phone   pwhash real_name shell       uid user_name
#>   <int> <chr>       <chr>        <chr>  <chr>     <chr>     <int> <chr>    
#> 1     0 /root       111-222-3333 xxx    Admin     /bin/dash     0 admin    
#> 2     1 /home/bob   123-456-7890 xxx    Bob       /bin/zsh      1 bob      
#> 3     1 /home/alice 098-765-4321 xxx    Alice     /bin/zsh      2 alice
rls_perform(policy3)
#> # A tibble: 1 × 1
#>   rows_affected
#>           <int>
#> 1             0
rls_policies(con)
#> # A tibble: 3 × 8
#>   schemaname tablename policyname permissive roles      cmd    qual   with_check
#>   <chr>      <chr>     <chr>      <chr>      <pq__name> <chr>  <chr>  <chr>     
#> 1 public     passwd    admin_all  PERMISSIVE {admin}    ALL    true   true      
#> 2 public     passwd    all_view   PERMISSIVE {public}   SELECT true   <NA>      
#> 3 public     passwd    user_mod   PERMISSIVE {public}   UPDATE (CURR… ((CURRENT…
```

## Grant column level permissions

Allow admin all normal rights


``` r
rls_tbl(con, "passwd") %>%
  grant(select, insert, update, delete) %>%
  to(admin) %>%
  rls_perform()
```

Users only get select access on public columns


``` r
rls_tbl(con, "passwd") %>%
  grant(select,
    cols = c(
      "user_name", "uid", "gid", "real_name",
      "home_phone", "home_dir", "shell"
    )
  ) %>%
  to(public) %>%
  rls_perform()
```

Allow users to update certain columns


``` r
rls_tbl(con, "passwd") %>%
  grant(update,
    cols = c("pwhash", "real_name", "home_phone", "shell")
  ) %>%
  to(public) %>%
  rls_perform()
```

## Ensure the system behaves as expected

Admin can access all columns


``` r
dbExecute(con, "SET SESSION AUTHORIZATION admin")
#> [1] 0
tbl(con, "passwd")
#> # Source:   table<"passwd"> [3 x 8]
#> # Database: postgres  [schambe3@/tmp:5432/schambe3]
#>   user_name pwhash   uid   gid real_name home_phone   home_dir    shell    
#>   <chr>     <chr>  <int> <int> <chr>     <chr>        <chr>       <chr>    
#> 1 admin     xxx        0     0 Admin     111-222-3333 /root       /bin/dash
#> 2 bob       xxx        1     1 Bob       123-456-7890 /home/bob   /bin/zsh 
#> 3 alice     xxx        2     1 Alice     098-765-4321 /home/alice /bin/zsh
```

Alice can NOT access all columns


``` r
dbExecute(con, "SET SESSION AUTHORIZATION alice")
#> [1] 0
tbl(con, "passwd") # "passwd" here means "SELECT * from passwd"
#> Error in `db_query_fields.DBIConnection()`:
#> ! Can't query fields.
#> ℹ Using SQL: SELECT * FROM "passwd" AS "q01" WHERE (0 = 1)
#> Caused by error:
#> ! Failed to fetch row : ERROR:  permission denied for table passwd
```

Alice can access all columns except for `pwhash`


``` r
sql1 <- sql("SELECT user_name,real_name,home_phone,home_dir,shell FROM passwd")
tbl(con, sql1)
#> # Source:   SQL [3 x 5]
#> # Database: postgres  [schambe3@/tmp:5432/schambe3]
#>   user_name real_name home_phone   home_dir    shell    
#>   <chr>     <chr>     <chr>        <chr>       <chr>    
#> 1 admin     Admin     111-222-3333 /root       /bin/dash
#> 2 bob       Bob       123-456-7890 /home/bob   /bin/zsh 
#> 3 alice     Alice     098-765-4321 /home/alice /bin/zsh
```

Alice can not do UPDATE operations on certain columns
(in this case `user_name`)


``` r
dbExecute(con, "UPDATE passwd SET user_name = 'joe'")
#> Error: Failed to fetch row : ERROR:  permission denied for table passwd
```

Alice can however update `real_name`


``` r
dbExecute(con, "UPDATE passwd SET real_name = 'Alice Doe'")
#> [1] 1
```

She can update `real_name`, but the update doesn't alter any rows
when it has a WHERE clause on `user_name`


``` r
dbExecute(con, "UPDATE passwd SET real_name = 'John Doe' WHERE user_name = 'admin'")
#> [1] 0
```

Alice can not update the `shell` column to an invalid value as
defined by our row-level security policy above


``` r
dbExecute(con, "UPDATE passwd SET shell = '/bin/xx'")
#> Error: Failed to fetch row : ERROR:  new row violates row-level security policy for table "passwd"
```

Alice can not delete the `passwd` table and can not do any insert
operations


``` r
dbExecute(con, "DELETE from passwd")
#> Error: Failed to fetch row : ERROR:  permission denied for table passwd
dbExecute(con, "INSERT INTO passwd (user_name) VALUES ('xxx')")
#> Error: Failed to fetch row : ERROR:  permission denied for table passwd
```

Alice can change her own password; RLS silently prevents updating
other rows


``` r
dbExecute(con, "UPDATE passwd SET pwhash = 'abc'")
#> [1] 1
```