At present pgbedrock is intended to configure a single database per spec, but this is kind of a strange compromise: roles and role memberships are at the Postgres instance level, meaning that if one Postgres instance has multiple databases in it then there is a need to have multiple spec.yml files which all have to be kept in sync with regards to the roles and role memberships in them. This can be done (especially by just doing pgbedrock generate before making any changes to a database), but it is awkward.
To solve this, pgbedrock should manage all databases in a Postgres instance. This would mean that the CLI would take a database name, but then would look up all databases via Postgres's pg_database table. The attributes.py and memberships.py submodules would run on any one database (which one doesn't really matter), but the ownerships.py and privileges.py submodules would run once for each database that exists.
In addition, objects in the spec would need to database-qualified, e.g. mydatabase.myschema.myobject. Then when the ownerships.py and privileges.py submodules run they should traverse the existing spec and pull out the objects that are relevant to that database. Then those submodules wouldn't need to really be changed at all. To do that, we'd ideally capture this 'pull things out of the spec.yaml' process in a new submodule (e.g. spec_inspector.py or something) so that we can keep all functionality together that pulls desired state out of a spec, as there is already some stuff that walks through a spec and get desired state out of it.
Key actions
Add functionality to identify all databases in a Postgres instance
Run ownerships.py and privileges.py for each database in the Postgres instance; attributes.py and memberships.py should just run on any one database
Before running pgbedrock configure, ensure all objects in the spec.yml file are database-qualified
Add a submodule that, among existing spec-inspection functionalities, also takes a database name and returns the portions of the spec that relate to objects from that database
Modify pgbedrock generate to traverse through all databases and add database-qualified objects to its output spec
Likely the best way to internally represent objects in this world would be as a namedtuple of (db, schema, objname), then it is only when we are going to output a spec that we convert it into a quoted, database-qualified object. This would allow us to avoid worrying about making sure all object are properly-quoted as we work with them internally.
Summary
At present pgbedrock is intended to configure a single database per spec, but this is kind of a strange compromise: roles and role memberships are at the Postgres instance level, meaning that if one Postgres instance has multiple databases in it then there is a need to have multiple spec.yml files which all have to be kept in sync with regards to the roles and role memberships in them. This can be done (especially by just doing
pgbedrock generatebefore making any changes to a database), but it is awkward.To solve this, pgbedrock should manage all databases in a Postgres instance. This would mean that the CLI would take a database name, but then would look up all databases via Postgres's
pg_databasetable. Theattributes.pyandmemberships.pysubmodules would run on any one database (which one doesn't really matter), but theownerships.pyandprivileges.pysubmodules would run once for each database that exists.In addition, objects in the spec would need to database-qualified, e.g.
mydatabase.myschema.myobject. Then when theownerships.pyandprivileges.pysubmodules run they should traverse the existing spec and pull out the objects that are relevant to that database. Then those submodules wouldn't need to really be changed at all. To do that, we'd ideally capture this 'pull things out of the spec.yaml' process in a new submodule (e.g.spec_inspector.pyor something) so that we can keep all functionality together that pulls desired state out of a spec, as there is already some stuff that walks through a spec and get desired state out of it.Key actions
ownerships.pyandprivileges.pyfor each database in the Postgres instance;attributes.pyandmemberships.pyshould just run on any one databasepgbedrock configure, ensure all objects in the spec.yml file are database-qualifiedpgbedrock generateto traverse through all databases and add database-qualified objects to its output spec