Can a non-owner of a table be allowed to disable the table's triggers?
I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?
postgresql permissions trigger
edited 11 hours ago
Andriy M
16.1k63373
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?
postgresql permissions trigger
edited 11 hours ago
Andriy M
16.1k63373
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?
postgresql permissions trigger
edited 11 hours ago
Andriy M
16.1k63373
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?
postgresql permissions trigger
postgresql permissions trigger
edited 11 hours ago
Andriy M
16.1k63373
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 11 hours ago
Andriy M
16.1k63373
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 11 hours ago
Andriy M
16.1k63373
edited 11 hours ago
Andriy M
16.1k63373
edited 11 hours ago
Andriy M
16.1k63373
16.1k63373
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
asked 13 hours ago
Szymon ChowaniecSzymon Chowaniec
212
212
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Szymon Chowaniec is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:
CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;
Then grant the necessary privilege to your user to execute the function:
GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;
Now alice will be able to disable that one trigger. You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.
The trick is in SECURITY DEFINER. It makes the function running with the privileges of the user that defines (creates) the function. As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.
answered 10 hours ago
dezsodezso
22.2k116096
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Szymon Chowaniec is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f230278%2fcan-a-non-owner-of-a-table-be-allowed-to-disable-the-tables-triggers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:
CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;
Then grant the necessary privilege to your user to execute the function:
GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;
Now alice will be able to disable that one trigger. You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.
The trick is in SECURITY DEFINER. It makes the function running with the privileges of the user that defines (creates) the function. As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.
answered 10 hours ago
dezsodezso
22.2k116096
add a comment |
As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:
CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;
Then grant the necessary privilege to your user to execute the function:
GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;
Now alice will be able to disable that one trigger. You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.
The trick is in SECURITY DEFINER. It makes the function running with the privileges of the user that defines (creates) the function. As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.
answered 10 hours ago
dezsodezso
22.2k116096
add a comment |
As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:
CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;
Then grant the necessary privilege to your user to execute the function:
GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;
Now alice will be able to disable that one trigger. You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.
The trick is in SECURITY DEFINER. It makes the function running with the privileges of the user that defines (creates) the function. As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.
answered 10 hours ago
dezsodezso
22.2k116096
As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:
CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;
Then grant the necessary privilege to your user to execute the function:
GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;
Now alice will be able to disable that one trigger. You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.
The trick is in SECURITY DEFINER. It makes the function running with the privileges of the user that defines (creates) the function. As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.
answered 10 hours ago
dezsodezso
22.2k116096
edited 10 hours ago
answered 10 hours ago
dezsodezso
22.2k116096
answered 10 hours ago
dezsodezso
22.2k116096
answered 10 hours ago
dezsodezso
22.2k116096
22.2k116096
add a comment |
add a comment |
Szymon Chowaniec is a new contributor. Be nice, and check out our Code of Conduct.
Szymon Chowaniec is a new contributor. Be nice, and check out our Code of Conduct.
Szymon Chowaniec is a new contributor. Be nice, and check out our Code of Conduct.
Szymon Chowaniec is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f230278%2fcan-a-non-owner-of-a-table-be-allowed-to-disable-the-tables-triggers%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
