I want to install an application (Odoo) that uses PostgreSQL, but it needs you create a superuser Role to allow the aplication process instalation create its own database. I just created an Azure PostgreSQL database (PaaS), but the user it creats is not a Superuser, and can't create a superuser role. Is there a way to get my admin user a Superuser?
Add a comment
|
2 Answers
To explain the users and roles a little better for Azure Database for PostgreSQL:
By default, when a server is created we have the following 3 roles defined, which you can also see when you run SELECT rolname FROM pg_roles; –
- azure_pg_admin
- azure_superuser
- server admin login – the admin login the user created the server with – which by default is a member of azure_pg_admin.
Ours is a managed PaaS service and Microsoft is the azure_superuser. We don’t grant superuser privileges to the user.
With that as baseline, there is at least one role (user) at any given time that is part of azure_pg_admin group i.e. server admin login. This user can create databases, create custom roles and customize privileges, and create additional users that are member of azure_pg_admin. A user is either a part of this group or not. Any user outside this group will not have those privileges.
Hope that helps answer your question.
Saloni
4 Comments
koehn
What if I need to
alter system to tune the settings for my application? Can I set e.g., effective_cache_size? I need superuser to do that, or the ability to update it in the server parameters. Otherwise I need to find another provider; this is a deal-breaker.
Hari
Does
azure_superuser role can access client database tables data/objects ? do we have restrict azure_superuser from accessing our tables data
Rafs
It is not the best that I cannot be a superuser on any databases. There is a stuck query that I need to kill, and I need superuser access to do that. That really degrades the quality of this product offering.
Rafs
New issue: I need to
--disable-triggers when dumping/restoring a database. Foreign keys are implemented as system triggers from what I see. I cannot temporarily disable these to allow the restore to go ahead because I cannot have access to any superusers on my own database. This is indeed limiting.When we postgres as service then we cannot be superuser but can enable extensions allowed
Azure console => Azure Database for PostgreSQL flexible server => => Server parameters => azure.extensions => => Save
