Basic Row-Level Security with Views and PostgreSQL Session Variables

-- Assume a 'users' table and a 'posts' table
CREATE TABLE app_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES app_users(id),
    title VARCHAR(255) NOT NULL,
    content TEXT
);

INSERT INTO app_users (username) VALUES ('admin'), ('editor1'), ('editor2');
INSERT INTO posts (user_id, title, content) VALUES
(1, 'Admin Post 1', 'Content visible to admin.'),
(2, 'Editor1 Post 1', 'Content for editor1.'),
(1, 'Admin Post 2', 'Another admin post.'),
(3, 'Editor2 Post 1', 'Content for editor2.');

-- Function to set the current user in a session variable
CREATE OR REPLACE FUNCTION set_current_user(p_user_id INTEGER) RETURNS VOID AS $$
BEGIN
    PERFORM set_config('app.current_user_id', p_user_id::text, false);
END;
$$ LANGUAGE plpgsql;

-- View to implement RLS
-- Only shows posts belonging to the current_user_id
CREATE OR REPLACE VIEW user_posts AS
SELECT id, user_id, title, content
FROM posts
WHERE user_id = current_setting('app.current_user_id')::integer;

-- Simulate user login and data access

-- As admin (user_id = 1)
SELECT set_current_user(1);
SELECT * FROM user_posts;
-- Expected output: Admin Post 1, Admin Post 2

-- As editor1 (user_id = 2)
SELECT set_current_user(2);
SELECT * FROM user_posts;
-- Expected output: Editor1 Post 1

-- As editor2 (user_id = 3)
SELECT set_current_user(3);
SELECT * FROM user_posts;
-- Expected output: Editor2 Post 1

-- Cleanup
-- DROP VIEW user_posts;
-- DROP FUNCTION set_current_user;
-- DROP TABLE posts;
-- DROP TABLE app_users;