Row Level Security (RLS) in PostgreSQL on AWS RDS not working

Question

I am currently trying to configure RLS on my PostgreSQL DB running on AWS RDS. For some reason I cannot get it to work on the Postgres DB in AWS, but locally it works and everything seems to be identical.

Here my setup:

PostgreSQL version: 10.6

Steps:

SELECT * FROM pg_user;
|usename|usesysid|usecreatedb|usesuper|userepl|usebypassrls|passwd|valuntil|
|rdsadmin|10 |t| t|t| t| ******** | infinity|
|app_user|16393|t|f|f|f|********|infinity|

SELECT current_user;
> current_user
> ---------------
> app_user

CREATE TABLE rls_test (id int, name varchar(255));
INSERT INTO rls_test (1, 'test');
SELECT * FROM rls_test;
>  id | name
> ----+------
>  1  | test

SELECT * FROM pg_tables where tablename='rls_test';
>  schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
> ------------+-----------+------------+------------+------------+----------+-------------+-------------
>  public     | rls_test  | app_user   |            | f          | f        | f           | f

ALTER TABLE rls_test ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_test FORCE ROW LEVEL SECURITY;

SELECT * FROM pg_tables where tablename='rls_test';
>  schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
> ------------+-----------+------------+------------+------------+----------+-------------+-------------
>  public     | rls_test  | app_user   |            | f          | f        | f           | t

SELECT * FROM rls_test;
>  id | name
> ----+------
>  1  | test

The same steps on a locally running Postgres DB do work. After enabling and forcing RLS on the new table the user does not see the entries in the table anymore. Only on the Postgres running on AWS RDS it's not working.

Any ideas what I could be missing here?

c16n c16n · Accepted Answer · 2020-04-09T15:32:52

So, in the meantime I found out, that AWS creates a 'master' user, which I was actually using here (app_user). The privileges given to this user can be found here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html Creating a new user with all the needed privileges for the DB solved the problem. I am still a little bit puzzled, though, which of the privileges prevented that this master user was bypassing the row level security, as the user table was showing that my app_user is not a superuser. If someone still has an answer to that, I'd appreciate it :-).

Row Level Security (RLS) in PostgreSQL on AWS RDS not working

3 Answers