Migrating to Flexible Server
Known Issues
ERROR: permission denied for table
Teams may encounter this error when they try to query the database (admin user) after migrating to flexible server. A new feature was added to the migration tool which now migrates roles/owners along with the schema. This results in the above error when teams are using a different admin user on flexible server than on single server.
To fix this, teams can raise a PR that triggers a script to fix ownership. Here are some useful links for this solution:
This issue can also be fixed manually by signing into the flexible server and running the below SQL. You will need to sign in using the single server credentials from the associated key vault.
Note: This should be used as a last resort. Preference is to use the automated solution.
GRANT ${flex_server_user} TO ${single_server_user};
REASSIGN OWNED BY ${single_server_user} TO ${flexible_server_user};
REVOKE ${flexible_server_user} FROM ${single_server_user};
GRANT ${single_server_user} TO ${flexible_server_user};
Note: If the single server user is in the format am@am-org-role-mapping-service-postgres-db-v11-aat use am.
Unable to login with DTS JIT xx DB Reader SC
After migrating the data from Single Server to Flexible Server, the team may find that they are unable to sign into the new Flexible Server database using the JIT role. In this case, it’s likely that the user did not get created during the migration.
Teams can raise a PR to trigger the script that will create the role and assign appropriate permissions. Here are some useful links:
If after the pipeline runs, teams are still unable to log in with the JIT role, they can view the output from their tf apply stage which should give them some idea of what went wrong. An example of this, would be if the team has set an incorrect value for product in their infrastructure code. This value is used to determine the name of the JIT role to be created, so if this is wrong it won’t be able to create the role.
- Example Error -
ERROR: Could not validate AAD user DTS JIT Access rd-judicial-ref-api DB Reader SC because its name is not found in the tenant. - Example Fix
DTS JIT xx DB Reader SC does not have correct permissions
Once able to login with the JIT role, teams should confirm they have appropriate permissions by running a simple select query. If they get a permission denied error, you can run the following commands to grant permissions:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "DTS JIT Access rd DB Reader SC";
GRANT USAGE ON SCHEMA public TO "DTS JIT Access rd DB Reader SC";
NOTE: The above commands will need to be run against any additional schemas that are in use.