Patterns for managing postgres roles and grants in a single instance, multi-database environment?

[jonseymour]

I've be trying to find a good pattern for managing postgres roles and grants in a single instance, multi-database environment.

There isn't an issue if there is only a single postgres database - just add the DDL for the roles to the and grants into the same migration stream as the table and views DDL (although, actually, I find it handy to use a separate stream to handle views for reasons not entirely unrelated to this problem).

However, once you have multiple databases, things start to get very messy with naive or default approaches. The naive approach is to simply choose on of the databases as the owner of the roles and put all DDL for the role objects into that stream. But this isn't good enough, because the DDL for the grants really needs to go into the migration streams for each database. Worse, if you want to rollback a migration that created a role you might not be able to until you have run rollbacks for migrations that have granted authorities to the roll in other databases. This is very messy, because it ends up coupling otherwise unrelated migration streams together.

This tends to suggest that roles should be managed in their own migration stream with references from the database migration streams to roles created by the roles migration stream. This is still a dependency, but at least it decouples database streams from each other, if not from the common dependency (the roles migration stream).

(In the paragraphs above, I am referring a set of migrations and the related goose_db_version table as a "migration stream" - each database can have multiple migrations streams - a pattern I have found useful is to have a tables migration stream and a views migration stream with a single migration that drops the view schema and recreates it from scratch)

Just wondering out aloud if other people have solved this problem a different way, and how?

[fsaintjacques]

I think going the "common" database for global resources is the sane route. Roles are often scoped to a single logical databases which might make it difficult to accept that you need to do this pattern. In our case the offenders where extensions, which are really singleton global resources.

My temptation is that such resources could even be given to IaC tools like terraform, they seem more related to infrastructure than application.