The role manager lambda creates the migrator and app db roles using the postgres master db user. However, updating default table privileges for a user can only be updated by the user itself. So, after creating the migrator and app roles, the role manager lambda needs to connect to the db as the migrator role, then run the alter default table privileges command using the connection that is logged in as the migrator role.
Testing notes
To test this properly, we can't use the existing platform-test's app-dev database since the default privileges already exist there. Instead we should create a database in a new workspace.
platform-test$ terraform -chdir=infra/app/database init -reconfigure -backend-config=dev.s3.tfbackend
platform-test$ terraform -chdir=infra/app/database workspace new t-db
platform-test$ make infra-update-app-database APP_NAME=app ENVIRONMENT=dev
We should update the checker to not manually grant privileges to the app user (i.e. remove this line), then see if it still works. If the default privileges have been updated then the app user should still be able to access the temporary table.
Currently, for project teams to use the database they need to add an initial migration that updates default privileges so that all tables created by the
migratordb user are accessible by theappdb user. It would be nice if themake infra-update-app-database-rolesstep that creates the migrator and app users also goes ahead and modifies the default table permissions.Implementation notes
The role manager lambda creates the
migratorandappdb roles using thepostgresmaster db user. However, updating default table privileges for a user can only be updated by the user itself. So, after creating themigratorandapproles, the role manager lambda needs to connect to the db as themigratorrole, then run the alter default table privileges command using the connection that is logged in as themigratorrole.Testing notes
To test this properly, we can't use the existing platform-test's app-dev database since the default privileges already exist there. Instead we should create a database in a new workspace.
Now, we have an existing script that checks database roles make infra-check-database-roles, and it runs these lines of code in the role manager that creates a table with the migrator user, grants privileges to the app user to write to the table, then tries to write to it with the app user.
We should update the checker to not manually grant privileges to the
appuser (i.e. remove this line), then see if it still works. If the default privileges have been updated then theappuser should still be able to access the temporary table.