-- RLS Checker educational fixture. Run only in a disposable local PostgreSQL
-- database as its administrator. All objects and rows are rolled back.
-- This demonstrates SQL role/RLS behaviour, not HTTP or a complete app audit.
BEGIN;
CREATE ROLE rls_demo_visitor NOLOGIN NOBYPASSRLS;
CREATE SCHEMA rls_checker_example;
GRANT USAGE ON SCHEMA rls_checker_example TO rls_demo_visitor;

CREATE TABLE rls_checker_example.exposed (id integer);
CREATE TABLE rls_checker_example.public_catalog (id integer);
CREATE TABLE rls_checker_example.filtered (id integer);
CREATE TABLE rls_checker_example.empty_table (id integer);
CREATE TABLE rls_checker_example.denied (id integer);
INSERT INTO rls_checker_example.exposed VALUES (1);
INSERT INTO rls_checker_example.public_catalog VALUES (1);
INSERT INTO rls_checker_example.filtered VALUES (1);
INSERT INTO rls_checker_example.denied VALUES (1);

GRANT SELECT ON rls_checker_example.exposed,
  rls_checker_example.public_catalog,
  rls_checker_example.filtered,
  rls_checker_example.empty_table TO rls_demo_visitor;

ALTER TABLE rls_checker_example.public_catalog ENABLE ROW LEVEL SECURITY;
CREATE POLICY public_catalog_read ON rls_checker_example.public_catalog
  FOR SELECT TO rls_demo_visitor USING (true);
ALTER TABLE rls_checker_example.filtered ENABLE ROW LEVEL SECURITY;
-- filtered contains a row but has no policy allowing the visitor to see it.
-- empty_table has no rows and no RLS. Both return an empty result to visitors.

SET LOCAL ROLE rls_demo_visitor;
SELECT 'exposed' AS example, count(*)::integer AS visible_rows
  FROM rls_checker_example.exposed;
SELECT 'public_catalog' AS example, count(*)::integer AS visible_rows
  FROM rls_checker_example.public_catalog;
SELECT 'filtered' AS example, count(*)::integer AS visible_rows
  FROM rls_checker_example.filtered;
SELECT 'empty_table' AS example, count(*)::integer AS visible_rows
  FROM rls_checker_example.empty_table;

-- A missing SELECT grant rejects the query instead of returning zero rows.
DO $$
BEGIN
  PERFORM * FROM rls_checker_example.denied;
  RAISE EXCEPTION 'Expected insufficient_privilege';
EXCEPTION WHEN insufficient_privilege THEN
  RAISE NOTICE 'denied: insufficient_privilege, SQLSTATE 42501';
END
$$;
RESET ROLE;
ROLLBACK;