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...

[davenquinn]

@amyfromandi just making you aware of this issue, as we need to get better at managing roles in Macrostrat in order to use PostgreSQL "row-level-security" in conjunction with PostgREST.

We could use a pre-existing library for this... sqlauthz seems like it could work. A few points

• The library has a silly name
• It is written in Typescript, which would be slightly harder to integrate with our command-line application (though not impossible – we could use Docker!)
• It uses the "polar" domain-specific language, which could be good or bad (pros: less yaml; cons: another language).

Overall, this might be worth taking a deeper look at as we start on the v3 API design.

[davenquinn]

We've created a start to GRANT functionality in the Macrostrat CLI in #97. We'll see if this is adequate. Currently, it doesn't check the current state or remove grants in excess of requirements. But these capabilities could be added.