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.
library(tibble)
library(DBI)
library(RPostgres)
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
library(dbplyr)
#>
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#>
#> ident, sql
library(rls)Create a table
Create the table (with no data)
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
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
rows_append(
tbl(con, "passwd"),
copy_inline(con, sample_data),
in_place = TRUE
)Check that the data is in the table
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/zshEnable row level security
Enable row level security with rls_enable
rls_enable(con, "passwd")Then check that it worked with rls_check_status
rls_check_status(con, "passwd")
#> # A tibble: 1 × 3
#> relname relrowsecurity relforcerowsecurity
#> <chr> <lgl> <lgl>
#> 1 passwd TRUE FALSECreate row level security policies
admin_all = Administrator can see all rows and add any
rows
(policy1 <- rls_construct_policy(
name = "admin_all",
table = "passwd",
role = "admin",
using = "(true)",
check = "(true)"
))
#> <rls_policy>
#> policy name: admin_all
#> table: passwd
#> role: admin
#> using: (true)
#> check: (true)
rls_create_policy(con, policy1)
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 trueall_view = Normal users can view all rows
(policy2 <- rls_construct_policy(
name = "all_view",
table = "passwd",
command = "SELECT",
using = "(true)"
))
#> <rls_policy>
#> policy name: all_view
#> table: passwd
#> command: SELECT
#> using: (true)
rls_create_policy(con, policy2)
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
(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')
)"
))
#> <rls_policy>
#> 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')
#> )
rls_create_policy(con, policy3)
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 permissions
Allow admin all normal rights
dbExecute(con, "GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin")Users only get select access on public columns
dbExecute(con, "GRANT SELECT
(user_name, uid, gid, real_name, home_phone, home_dir, shell)
ON passwd TO public"
)Allow users to update certain columns
dbExecute(con, "GRANT UPDATE
(pwhash, real_name, home_phone, shell)
ON passwd TO public"
)Ensure the system behaves as expected
Admin can access all columns
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/zshAlice can NOT access all columns
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 passwdAlice can access all columns except for pwhash
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/zshAlice can not do UPDATE operations on certain columns (in this case
user_name)
dbExecute(con, "UPDATE passwd SET user_name = 'joe'")
#> Error: Failed to fetch row : ERROR: permission denied for table passwdAlice can however update real_name
dbExecute(con, "UPDATE passwd SET real_name = 'Alice Doe'")
#> [1] 1She can update real_name, but the update doesn’t alter
any rows when it has a WHERE clause on user_name
dbExecute(con, "UPDATE passwd SET real_name = 'John Doe' WHERE user_name = 'admin'")
#> [1] 0Alice can not update the shell column to an invalid
value as defined by our row-level security policy above
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
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 passwdAlice can change her own password; RLS silently prevents updating other rows
dbExecute(con, "UPDATE passwd SET pwhash = 'abc'")
#> [1] 1