Detections
Analytics

4.5 Ensure Row Level Security (RLS) is configured correctly

Warning! Audit Deprecated

This audit has been deprecated and will be removed in a future update.

View Next Audit Version

Information

In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which individual rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row Level Security (RLS).

By default, tables do not have any policies, so if a user has access privileges to a table according to the SQL privilege system, all rows within it are equally available for querying or updating. Row security policies can be specific to commands, to roles, or to both. A policy can be specified to apply to ALL commands, or to any combination of SELECT, INSERT, UPDATE, or DELETE. Multiple roles can be assigned to a given policy, and normal role membership and inheritance rules apply.

If you use RLS and apply restrictive policies to certain users, it is important that the Bypass RLS privilege not be granted to any unauthorized users. This privilege overrides RLS-enabled tables and associated policies. Generally, only superusers and elevated users should possess this privilege.

Rationale:

If RLS policies and privileges are not configured correctly, users could perform actions on tables that they are not authorized to perform, such as inserting, updating, or deleting rows.

NOTE: Nessus has not performed this check. Please review the benchmark to ensure target compliance.

Solution

Again, we are using the example from the PostgreSQL documentation using the example passwd table. We will create three database roles to illustrate the workings of RLS:

postgres=# CREATE USER admin;
CREATE ROLE
postgres=# CREATE USER bob;
CREATE ROLE
postgres=# CREATE USER alice;
CREATE ROLE

Now, we will insert known data into the passwd table:

postgres=# INSERT INTO passwd VALUES
 ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT 0 1
postgres=# INSERT INTO passwd VALUES
 ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT 0 1
postgres=# INSERT INTO passwd VALUES
 ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');
INSERT 0 1

And we will enable RLS on the table:

postgres=# ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
ALTER TABLE
postgres=# SELECT oid, relname, relrowsecurity FROM pg_class WHERE relname = 'passwd';
 oid | relname | relrowsecurity
-------+---------+----------------
 24679 | passwd | t
(1 row)

Now that RLS is enabled, we need to define one or more policies. Create the administrator policy and allow it access to all rows:

postgres=# CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
CREATE POLICY

Create a policy for normal users to view all rows:

postgres=# CREATE POLICY all_view ON passwd FOR SELECT USING (true);
CREATE POLICY

Create a policy for normal users that allows them to update only their own rows and to limit what values can be set for their login shell:

postgres=# CREATE POLICY user_mod ON passwd FOR UPDATE
 USING (current_user = user_name)
 WITH CHECK (
 current_user = user_name AND
 shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
 );
CREATE POLICY

Grant all the normal rights on the table to the admin user:

postgres=# GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
GRANT

Grant only select access on non-sensitive columns to everyone:

postgres=# GRANT SELECT
 (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
 ON passwd TO public;
GRANT

Grant update to only the sensitive columns:

postgres=# GRANT UPDATE
 (pwhash, real_name, home_phone, extra_info, shell)
 ON passwd TO public;
GRANT

Ensure that no one has been granted Bypass RLS inadvertently, by running the psql display command \du+. If unauthorized users do have Bypass RLS granted then resolve this using the ALTER ROLE <user> NOBYPASSRLS; command.

You can now verify that 'admin', 'bob', and 'alice' are properly restricted by querying the passwd table as each of these roles.

postgres=# set role admin;
SET
postgres=# table passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | 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
(3 rows)
postgres=# set role alice;
SET
postgres=# table passwd;
ERROR: permission denied for table passwd
postgres=# select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name | home_phone | extra_info | home_dir | shell
-----------+-----------+--------------+------------+-------------+-----------
 admin | Admin | 111-222-3333 | | /root | /bin/dash
 bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh
 alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
postgres=# update passwd set user_name = 'joe';
ERROR: permission denied for table passwd
-- Alice is allowed to change her own real_name, but no others
postgres=# update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=# update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=# select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name | home_phone | extra_info | home_dir | shell
-----------+-----------+--------------+------------+-------------+-----------
 admin | Admin | 111-222-3333 | | /root | /bin/dash
 bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh
 alice | Alice Doe | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
postgres=# update passwd set shell = '/bin/xx';
ERROR: new row violates WITH CHECK OPTION for 'passwd'
postgres=# delete from passwd;
ERROR: permission denied for table passwd
postgres=# insert into passwd (user_name) values ('xxx');
ERROR: permission denied for table passwd
-- Alice can change her own password; RLS silently prevents updating other rows
postgres=# update passwd set pwhash = 'abc';
UPDATE 1

See Also

https://workbench.cisecurity.org/benchmarks/14977

Item Details

References: CSCv7|14.6

Plugin: PostgreSQLDB

Control ID: ce94e0bc83c9fdad205ce2aec5cdc991606104bb33f3350bd742e734ffc7d215