Declarative management for PostgreSQL roles

[davenquinn]

We're increasingly encoding access control in PostgreSQL roles. This is great, but means we need to have good ways to manage/apply these roles on database startup and changes (e.g., migrations).

Here's an example of some role configuration for PostgREST: https://github.com/UW-Macrostrat/macrostrat/blob/main/cli/macrostrat/cli/subsystems/knowledge_graph/fixtures/roles.sql

We need to figure out how to centralize and generalize this, in tandem with defining finer-grained roles and potentially row-level security for data access management.

Context:

• Design documentation for our potential PSQL auth system
• PostgREST documentation

[davenquinn]

@brianaydemir we can worry more about this later, but this is a lane in which I feel there must be some existing software solution? Keeping a set of roles consistent seems like a really general problem for postgres-based systems...