PART 3 · VIEWS IN ADMINISTRATION
Views (VIEW) as a Showcase and Security Boundary
How to provide an intern or an external service with player statistics without revealing their email addresses or requiring them to write complex JOIN statements
-- Создаём представление от имени владельца схемы (game_owner):
CREATE VIEW game.v_player_summary AS
SELECT
p.player_id,
p.nickname, -- email сюда НЕ включён!
c.clan_tag,
p.level,
COUNT(pu.purchase_id) AS purchases_cnt,
COALESCE(SUM(pu.price_paid), 0) AS total_spent
FROM game.players p
LEFT JOIN game.clans c ON c.clan_id = p.clan_id
LEFT JOIN game.purchases pu ON pu.player_id = p.player_id
GROUP BY p.player_id, p.nickname, c.clan_tag, p.level;
GRANT SELECT ON game.v_player_summary TO game_readonly;
Two VIEW Superpowers for Administrators
1. Protection of Personal Information (PII):
• You can deny roles direct read access to the game.players table (where email addresses are stored), but allow them to read game.v_player_summary.
• By default, a VIEW is executed with the rights of its owner (game_owner) on the underlying tables!
2. Stable contract during refactoring:
• If tables are renamed within the database, reports will continue to execute SELECT * FROM game.v_player_summary.
A Regular VIEW vs. a MATERIALIZED VIEW in PostgreSQL
• A regular VIEW does not store data on disk—it generates its SQL query on the fly with every read (always 100% up-to-date data).
• A MATERIALIZED VIEW physically stores the result of the calculation on disk and is updated using the REFRESH MATERIALIZED VIEW CONCURRENTLY command—which is useful for resource-intensive dashboards.
In Appendix P6 (at the end of the presentation), you can click "Run" right in your browser to send a request to game.v_player_summary.