TIL — Postgres RLS on VIEWs — part 1

Views are useful for encapsulating commonly executed queries into a virtual table, but they can also be useful for preventing access to certain columns.

But there’s a catch! Views are called with the privileges of the role that created them (the owner). So if you did CREATE VIEW with an admin/superuser role, it would bypass Row-level security (RLS). (since view owner to check the row-level security policies on these tables.

-- Create an apiuser & grant base privileges
CREATE USER "apiuser";
GRANT CONNECT ON DATABASE "postgres" TO "apiuser";
GRANT USAGE ON SCHEMA "public" TO "apiuser";

-- apiuser can read tables
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO "apiuser";

ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT
  SELECT ON TABLES TO "apiuser";

-- Example table
CREATE TABLE "users" (
  "id" serial primary key,
  "username" text,
  "email" text,
  "data" text
);

-- "users" table view with limited data
CREATE VIEW "users_view" AS 
  SELECT "id", "username", "data" 
  FROM users;

-- Enable RLS on the table
ALTER TABLE "users" ENABLE ROW LEVEL SECURITY;

-- Policy to select rows 
CREATE POLICY "read_users" ON "users"
  FOR SELECT 
  TO "apiuser"
  USING ("username" = current_user);

Let’s insert some data and observe SELECTs with these roles —postgres and apiuser

INSERT INTO "users" (username, email, data) VALUES 
  ('phoenix', 'phoenix@example.com', 'dummy-phoenix'),
  ('postgres', 'postgres@example.com', 'dummy-postgres'),
  ('apiuser', 'apiuser@example.com', 'dummy-apiuser');

-- Change role to postgres
SET role = 'postgres';
SELECT current_user; -- postgres (superadmin)

-- Execute SELECTs
SELECT * FROM "users";
SELECT * FROM "users_view";

-- Change role to apiuser
SET role = 'apiuser';
SELECT current_user; -- apiuser

-- Execute SELECTs
SELECT * FROM "users";
SELECT * FROM "users_view";

role is `postgres`

role is `apiuser`