APPENDIX · POSTGRESQL LIVE DEMO · SLIDE 5
P5. RBAC Permission Matrix and Purchase Log Protection (Append-Only)
Click Run—we'll check the permissions matrix for the game_app and game_readonly roles across all 7 tables
-- Проверяем матрицу привилегий ролей game_app и game_readonly:
SELECT
t.table_name,
game.has_role_privilege('game_app', t.table_name, 'SELECT') AS app_select,
game.has_role_privilege('game_app', t.table_name, 'INSERT') AS app_insert,
game.has_role_privilege('game_app', t.table_name, 'DELETE') AS app_delete,
game.has_role_privilege('game_readonly', t.table_name, 'SELECT') AS ro_select,
game.has_role_privilege('game_readonly', t.table_name, 'INSERT') AS ro_insert
FROM information_schema.tables t
WHERE t.table_schema = 'game'
AND t.table_type = 'BASE TABLE'
ORDER BY t.table_name;
Let's take a look at the app_delete column!
• For the operational tables (clans, items, matches, players), the game_app role has app_delete set to true.
• For the protected logs `kill_log` and `purchases`, `app_insert = true`, but `app_delete = false`!
• For the game_readonly analytical role, only ro_select = true is allowed everywhere, while ro_insert = false.
Checking the Access Matrix
Append-Only Log
Test Using SET ROLE
A single SQL query clearly shows the auditor the full picture of how permissions are configured for each table in your schema.
Even if an attacker or a bug in a microservice attempts to execute `DELETE FROM game.purchases`, the database will return a "permission denied" error.
In the local container, run `SET ROLE game_app; DELETE FROM game.purchases;` and see the failure for yourself.