Построить иерархию ролей с минимальными привилегиями (GRANT + ALTER DEFAULT PRIVILEGES)

Команда

-- Group roles (NOLOGIN): object owner, read-only, read-write
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
GRANT app_readonly TO app_readwrite;
CREATE SCHEMA app AUTHORIZATION app_owner;

-- Restrict PUBLIC's default privileges
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Existing objects
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

-- Objects app_owner will create IN THE FUTURE
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- Login users get privileges only through group membership
CREATE ROLE web_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readwrite;
CREATE ROLE report_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readonly;

-- Configured default privileges (psql: \ddp)
SELECT pg_get_userbyid(d.defaclrole) AS owner_role,
       n.nspname AS schema_name,
       d.defaclobjtype AS object_type,
       d.defaclacl
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;

Что делает

Строит иерархию групповых ролей: владельца, только чтение и чтение-запись; выдаёт нужные привилегии на существующие и будущие таблицы/последовательности и авторизует пользователей приложения только через членство в группах.

Когда использовать

При настройке новой схемы приложения, исправлении приложений, подключающихся как суперпользователь или владелец таблиц, и упрощении привилегий перед аудитом.

Внимание

ALTER DEFAULT PRIVILEGES применяется только к объектам, создаваемым ПОЗЖЕ ролью, указанной в FOR ROLE; если инструмент миграций создаёт таблицы от другого пользователя, привилегии не применяются, поэтому выполняйте DDL с SET ROLE app_owner. GRANT ... ON ALL TABLES охватывает только объекты, существующие на этот момент. Базы данных, созданные в PG15+, уже не выдают PUBLIC право CREATE на схему public; базам, обновлённым со старых версий, нужен REVOKE. REVOKE ALL ON DATABASE ... FROM PUBLIC также снимает CONNECT и TEMP; выдайте их отдельно другим ролям, которым они нужны. Используйте psql \password вместо указания паролей в командной строке (они не попадут в журналы и историю).