Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Interactions with Azure PostgreSQL-Databases #89

Open
cymed opened this issue Aug 24, 2023 · 2 comments
Open

Interactions with Azure PostgreSQL-Databases #89

cymed opened this issue Aug 24, 2023 · 2 comments
Assignees
Labels
bug Something isn't working

Comments

@cymed
Copy link

cymed commented Aug 24, 2023

Some hosted PostgreSQL - servers (i.e. Azure PaaS) do not give out superuser rights to their clients. Also, Azure Database for Postgres does not have a user named "postgres" This complicates things when loading non-plain database backups into these systems. I therefore suggest that all TEKSI backups (i.e. the release assets of the datamodel) are stored as plain text sql files instead of .backup

Workaround for other service providers encountering this problem:

  • Load .backup on a different postgres server
  • backup as plain sql
  • import on Azure PostgreSQL using psql.
@cymed cymed added the bug Something isn't working label Aug 24, 2023
@ponceta
Copy link
Member

ponceta commented Aug 25, 2023

That's a great observation, but the problem is actually the opposite when you deal with small entities relying only on pgAdmin interface for management. Of course it includes a psql client but the usage is much more complicated for the end user.

As it is now almost fully automated, we should provide both .backup and .sql files.

Since the demo_data_backup can't be restore in a sql structure, I'm wondering the utility of it. I would propose to have :

  • project_x.x.x_structure_with_value_lists.sql/.backup
  • project_x.x.x_demo.sql/.backup (being the full demo with value lists and structure)

Technical assets can be generated only in the required format.

  • (project_x.x.x_structure.sql) and (demo_data.backup)

@cymed we should also add the import to Azure PostgreSQL as a supported and documented framework for TEKSI modules.

@cymed cymed changed the title Handling of PostgreSQL-Databases with no superuser access Interactions with Azure PostgreSQL-Databases Aug 25, 2023
@cymed
Copy link
Author

cymed commented Aug 25, 2023

The problem arises solely when a potential TEKSI user wishes to import the structure_and_demo_data.backup on an Azure-based DB. In my opinion it is ok to rely on a service provider for this very specific task.
For Clients on a managed service we cannot automatically assume that there is a user named postgres. we should take that into consideration when creating deltas - grant all schemas/functions etc to qgep_sysadmin instead of postgres.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants