Microsoft Azure PostgreSQL Event Trigger vs. SuperUser
I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as well.
I've created a role pgpublish and all users are members of the role. For a new table, which I created, I altered the table owner to the role pgpublish. Now everyone with the role pgpublish is able to alter the table:
ALTER TABLE "MYcoolSchema"."CoolNewTable" OWNER TO pgpublish;
To make this more automatic/generic, I created a trigger function and tried to create an event trigger as explained here.
Unfortunately I can't create the event trigger (The trigger function works fine), as it is stated:
ERROR: permission denied to create event trigger
"trg_create_set_owner" HINT: Must be superuser to create an event trigger.
SQL state: 42501
Is there a workaround for creating event triggers on Microsoft Azure Database for PostgreSQL? How can this look like?
I could run a cron job on another system to scan for new tables and alter the owner of these new tables to pgpublish but this is not cool at all.
postgresql
azure events triggers asked Jan 2 at 18:10
RiccardoRiccardo
1066
add a comment |
I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as well.
I've created a role pgpublish and all users are members of the role. For a new table, which I created, I altered the table owner to the role pgpublish. Now everyone with the role pgpublish is able to alter the table:
ALTER TABLE "MYcoolSchema"."CoolNewTable" OWNER TO pgpublish;
To make this more automatic/generic, I created a trigger function and tried to create an event trigger as explained here.
Unfortunately I can't create the event trigger (The trigger function works fine), as it is stated:
ERROR: permission denied to create event trigger
"trg_create_set_owner" HINT: Must be superuser to create an event trigger.
SQL state: 42501
Is there a workaround for creating event triggers on Microsoft Azure Database for PostgreSQL? How can this look like?
I could run a cron job on another system to scan for new tables and alter the owner of these new tables to pgpublish but this is not cool at all.
postgresql
azure events triggers asked Jan 2 at 18:10
RiccardoRiccardo
1066
1
Hi Riccardo - Please make an entry to request the desired functionality on this forum: feedback.azure.com/forums/597976-azure-database-for-PostgreSQL I am working with the product group to get you an interim solution.
– Mike Ubezzi MSFT
Jan 2 at 21:50
@MikeUbezziMSFT: been there, done that: feedback.azure.com/forums/597976-azure-database-for-postgresql/…
– Riccardo
Jan 3 at 7:43
Perfect, thank you! I have an email into the product group requesting work around or solution to your issue. Super User access will eventually arrive but not without building use cases. Thanks again.
– Mike Ubezzi MSFT
Jan 3 at 19:17
So, Azure Database for PostgreSQL is offered as PaaS, and there is currently a permissions limitation with this offering. There is no short-term solution and the product team has viewed your User Voice request. That being said, you could deploy PostgreSQL on an Ubuntu VM (available through Azure Marketplace as PostgreSQL 9.5 on Ubuntu). This will give you complete control of your instance but there is more involved to set-up and at (most likely) additional costs but, you get to what you are looking for.
– Mike Ubezzi MSFT
Jan 10 at 18:00
add a comment |
I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as well.
I've created a role pgpublish and all users are members of the role. For a new table, which I created, I altered the table owner to the role pgpublish. Now everyone with the role pgpublish is able to alter the table:
ALTER TABLE "MYcoolSchema"."CoolNewTable" OWNER TO pgpublish;
To make this more automatic/generic, I created a trigger function and tried to create an event trigger as explained here.
Unfortunately I can't create the event trigger (The trigger function works fine), as it is stated:
ERROR: permission denied to create event trigger
"trg_create_set_owner" HINT: Must be superuser to create an event trigger.
SQL state: 42501
Is there a workaround for creating event triggers on Microsoft Azure Database for PostgreSQL? How can this look like?
I could run a cron job on another system to scan for new tables and alter the owner of these new tables to pgpublish but this is not cool at all.
postgresql
azure events triggers asked Jan 2 at 18:10
RiccardoRiccardo
1066
I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as well.
I've created a role pgpublish and all users are members of the role. For a new table, which I created, I altered the table owner to the role pgpublish. Now everyone with the role pgpublish is able to alter the table:
ALTER TABLE "MYcoolSchema"."CoolNewTable" OWNER TO pgpublish;
To make this more automatic/generic, I created a trigger function and tried to create an event trigger as explained here.
Unfortunately I can't create the event trigger (The trigger function works fine), as it is stated:
ERROR: permission denied to create event trigger
"trg_create_set_owner" HINT: Must be superuser to create an event trigger.
SQL state: 42501
Is there a workaround for creating event triggers on Microsoft Azure Database for PostgreSQL? How can this look like?
I could run a cron job on another system to scan for new tables and alter the owner of these new tables to pgpublish but this is not cool at all.
postgresql
azure events triggers postgresql
azure events triggers asked Jan 2 at 18:10
RiccardoRiccardo
1066
asked Jan 2 at 18:10
RiccardoRiccardo
1066
edited Jan 2 at 18:48
Riccardo
asked Jan 2 at 18:10
RiccardoRiccardo
1066
asked Jan 2 at 18:10
RiccardoRiccardo
1066
asked Jan 2 at 18:10
RiccardoRiccardo
1066
1066
1
Hi Riccardo - Please make an entry to request the desired functionality on this forum: feedback.azure.com/forums/597976-azure-database-for-PostgreSQL I am working with the product group to get you an interim solution.
– Mike Ubezzi MSFT
Jan 2 at 21:50
@MikeUbezziMSFT: been there, done that: feedback.azure.com/forums/597976-azure-database-for-postgresql/…
– Riccardo
Jan 3 at 7:43
Perfect, thank you! I have an email into the product group requesting work around or solution to your issue. Super User access will eventually arrive but not without building use cases. Thanks again.
– Mike Ubezzi MSFT
Jan 3 at 19:17
So, Azure Database for PostgreSQL is offered as PaaS, and there is currently a permissions limitation with this offering. There is no short-term solution and the product team has viewed your User Voice request. That being said, you could deploy PostgreSQL on an Ubuntu VM (available through Azure Marketplace as PostgreSQL 9.5 on Ubuntu). This will give you complete control of your instance but there is more involved to set-up and at (most likely) additional costs but, you get to what you are looking for.
– Mike Ubezzi MSFT
Jan 10 at 18:00
add a comment |
1
Hi Riccardo - Please make an entry to request the desired functionality on this forum: feedback.azure.com/forums/597976-azure-database-for-PostgreSQL I am working with the product group to get you an interim solution.
– Mike Ubezzi MSFT
Jan 2 at 21:50
@MikeUbezziMSFT: been there, done that: feedback.azure.com/forums/597976-azure-database-for-postgresql/…
– Riccardo
Jan 3 at 7:43
Perfect, thank you! I have an email into the product group requesting work around or solution to your issue. Super User access will eventually arrive but not without building use cases. Thanks again.
– Mike Ubezzi MSFT
Jan 3 at 19:17
So, Azure Database for PostgreSQL is offered as PaaS, and there is currently a permissions limitation with this offering. There is no short-term solution and the product team has viewed your User Voice request. That being said, you could deploy PostgreSQL on an Ubuntu VM (available through Azure Marketplace as PostgreSQL 9.5 on Ubuntu). This will give you complete control of your instance but there is more involved to set-up and at (most likely) additional costs but, you get to what you are looking for.
– Mike Ubezzi MSFT
Jan 10 at 18:00
1
1
Hi Riccardo - Please make an entry to request the desired functionality on this forum: feedback.azure.com/forums/597976-azure-database-for-PostgreSQL I am working with the product group to get you an interim solution.
– Mike Ubezzi MSFT
Jan 2 at 21:50
Hi Riccardo - Please make an entry to request the desired functionality on this forum: feedback.azure.com/forums/597976-azure-database-for-PostgreSQL I am working with the product group to get you an interim solution.
– Mike Ubezzi MSFT
Jan 2 at 21:50
@MikeUbezziMSFT: been there, done that: feedback.azure.com/forums/597976-azure-database-for-postgresql/…
– Riccardo
Jan 3 at 7:43
@MikeUbezziMSFT: been there, done that: feedback.azure.com/forums/597976-azure-database-for-postgresql/…
– Riccardo
Jan 3 at 7:43
Perfect, thank you! I have an email into the product group requesting work around or solution to your issue. Super User access will eventually arrive but not without building use cases. Thanks again.
– Mike Ubezzi MSFT
Jan 3 at 19:17
Perfect, thank you! I have an email into the product group requesting work around or solution to your issue. Super User access will eventually arrive but not without building use cases. Thanks again.
– Mike Ubezzi MSFT
Jan 3 at 19:17
So, Azure Database for PostgreSQL is offered as PaaS, and there is currently a permissions limitation with this offering. There is no short-term solution and the product team has viewed your User Voice request. That being said, you could deploy PostgreSQL on an Ubuntu VM (available through Azure Marketplace as PostgreSQL 9.5 on Ubuntu). This will give you complete control of your instance but there is more involved to set-up and at (most likely) additional costs but, you get to what you are looking for.
– Mike Ubezzi MSFT
Jan 10 at 18:00
So, Azure Database for PostgreSQL is offered as PaaS, and there is currently a permissions limitation with this offering. There is no short-term solution and the product team has viewed your User Voice request. That being said, you could deploy PostgreSQL on an Ubuntu VM (available through Azure Marketplace as PostgreSQL 9.5 on Ubuntu). This will give you complete control of your instance but there is more involved to set-up and at (most likely) additional costs but, you get to what you are looking for.
– Mike Ubezzi MSFT
Jan 10 at 18:00
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54011161%2fmicrosoft-azure-postgresql-event-trigger-vs-superuser%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54011161%2fmicrosoft-azure-postgresql-event-trigger-vs-superuser%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Hi Riccardo - Please make an entry to request the desired functionality on this forum: feedback.azure.com/forums/597976-azure-database-for-PostgreSQL I am working with the product group to get you an interim solution.
– Mike Ubezzi MSFT
Jan 2 at 21:50
@MikeUbezziMSFT: been there, done that: feedback.azure.com/forums/597976-azure-database-for-postgresql/…
– Riccardo
Jan 3 at 7:43
Perfect, thank you! I have an email into the product group requesting work around or solution to your issue. Super User access will eventually arrive but not without building use cases. Thanks again.
– Mike Ubezzi MSFT
Jan 3 at 19:17
So, Azure Database for PostgreSQL is offered as PaaS, and there is currently a permissions limitation with this offering. There is no short-term solution and the product team has viewed your User Voice request. That being said, you could deploy PostgreSQL on an Ubuntu VM (available through Azure Marketplace as PostgreSQL 9.5 on Ubuntu). This will give you complete control of your instance but there is more involved to set-up and at (most likely) additional costs but, you get to what you are looking for.
– Mike Ubezzi MSFT
Jan 10 at 18:00