PART 3 · IMPLEMENTING RBAC IN SQL
Configuring Permissions in SQL and Protecting Purchase Logs (Append-Only)
Complete privilege configuration template from the Lesson 5/dba/live_admin_pg.sql database
-- 1. Закрываем схему public и даём доступ к схеме game
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA game TO game_app, game_readonly;
-- 2. Операционные таблицы: полный DML для сервиса
GRANT SELECT, INSERT, UPDATE, DELETE ON
game.clans, game.players, game.items,
game.matches, game.match_participants
TO game_app;
-- 3. Финансовый и боевой журналы: ТОЛЬКО дозапись (Append-Only)!
GRANT SELECT, INSERT ON game.purchases, game.kill_log TO game_app;
-- 4. Важно: доступ к последовательностям для генерации IDENTITY ID
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA game TO game_app;
-- 5. Чтение для аналитиков и права по умолчанию на будущие таблицы
GRANT SELECT ON ALL TABLES IN SCHEMA game TO game_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE game_owner IN SCHEMA game
GRANT SELECT ON TABLES TO game_readonly;
Why are there permissions for SEQUENCES?
• Behind the scenes, every column defined as `GENERATED ALWAYS AS IDENTITY` increments a system counter (sequence).
• If you execute an INSERT statement on a table but forget to run GRANT USAGE, SELECT ON ALL SEQUENCES, the service will receive a “permission denied for sequence” error on the first insert.
Checking a Role Using SET ROLE
• Within a psql session, the command `SET ROLE game_app;` instantly switches the effective privileges to the service role.
• The `RESET ROLE;` command restores the original privileges. This is how we test failures right in the script!
Note the difference with `ALTER DEFAULT PRIVILEGES`: a standard `GRANT ... ON ALL TABLES` applies only to tables that have already been created, whereas `DEFAULT PRIVILEGES` also apply to future tables.