Postgres “session variables” kludge on AWS RDS
I am trying to migrate a PostgreSQL database to AWS RDS.
The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the customized options syntax for storing runtime session data. (In this case, the DB has a slew of triggers, which seem to be basically reinventing the concept of roles. Rewriting the software isn't really an option at this point, so please bear with me here.)
The main problem with migrating that DB to the AWS cloud is that I couldn't find a way to set a sensible default for the custom param. An RDS database doesn't give you Superuser access required for ALTER SYSTEM SET kludge.param TO 'foo', nor do you get to edit postgresql.conf. (It's possible to edit existing DB parameters via RDS's DB parameter group mechanism, but adding new ones won't work. I tried.)
In addition, trying to get the current_setting(kludge.param) doesn't return anything nice if the option isn't set; instead it causes the statement to fail with an SQL error. Here's what I get when I try to update a table without setting the param in the session:
ERROR: unrecognized configuration parameter "kludge.param"
CONTEXT: PL/pgSQL function update_modifier() line 1 at assignment
Furthermore, I couldn't find a way to check for the existence of the parameter without producing the same error.
My current idea is to edit all the trigger functions (although there are many triggers, there aren't ridiculously many trigger functions) to trap error 42P02 undefined_parameter with a WHEN clause, and set a default value for the custom parameter in the error handler.
Given that my only goal seems very simple (inserts and updates work sensibly, with or without setting any custom parameters) and my solution seems to fall in the suspicious category of "clever hacks for enabling other clever hacks", I wanted to check with you guys first:
- Does anything I'm saying make any sense at all?
- Is there an easier way?
- If there isn't, is this approach likely to solve my problem?
Thanks for your time in advance.
(We're running PostgreSQL server version 9.5.10.)
postgresql aws parameter
asked May 2 '18 at 9:50
BassBass
1113
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am trying to migrate a PostgreSQL database to AWS RDS.
The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the customized options syntax for storing runtime session data. (In this case, the DB has a slew of triggers, which seem to be basically reinventing the concept of roles. Rewriting the software isn't really an option at this point, so please bear with me here.)
The main problem with migrating that DB to the AWS cloud is that I couldn't find a way to set a sensible default for the custom param. An RDS database doesn't give you Superuser access required for ALTER SYSTEM SET kludge.param TO 'foo', nor do you get to edit postgresql.conf. (It's possible to edit existing DB parameters via RDS's DB parameter group mechanism, but adding new ones won't work. I tried.)
In addition, trying to get the current_setting(kludge.param) doesn't return anything nice if the option isn't set; instead it causes the statement to fail with an SQL error. Here's what I get when I try to update a table without setting the param in the session:
ERROR: unrecognized configuration parameter "kludge.param"
CONTEXT: PL/pgSQL function update_modifier() line 1 at assignment
Furthermore, I couldn't find a way to check for the existence of the parameter without producing the same error.
My current idea is to edit all the trigger functions (although there are many triggers, there aren't ridiculously many trigger functions) to trap error 42P02 undefined_parameter with a WHEN clause, and set a default value for the custom parameter in the error handler.
Given that my only goal seems very simple (inserts and updates work sensibly, with or without setting any custom parameters) and my solution seems to fall in the suspicious category of "clever hacks for enabling other clever hacks", I wanted to check with you guys first:
- Does anything I'm saying make any sense at all?
- Is there an easier way?
- If there isn't, is this approach likely to solve my problem?
Thanks for your time in advance.
(We're running PostgreSQL server version 9.5.10.)
postgresql aws parameter
asked May 2 '18 at 9:50
BassBass
1113
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Can you runalter user bass set kludge.param TO 'foo';?
– a_horse_with_no_name
May 2 '18 at 10:30
@a_horse_with_no_name, unfortunately, no. ERROR: permission denied to set parameter "kludge.param". This is a particularly good guess though, since the account we get does indeed have the "create role" attribute, which should be the one that gets checked when trying toalter user. (As a particularly interesting behaviour, trying toalter user allnot only fails, it also causes the connection to drop with "SSL SYSCALL error: EOF detected".)
– Bass
May 2 '18 at 12:08
(After further investigation, it seems like that command crashes the entire DB: all pids in pg_stat_activity change at once.)
– Bass
May 2 '18 at 12:22
add a comment |
I am trying to migrate a PostgreSQL database to AWS RDS.
The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the customized options syntax for storing runtime session data. (In this case, the DB has a slew of triggers, which seem to be basically reinventing the concept of roles. Rewriting the software isn't really an option at this point, so please bear with me here.)
The main problem with migrating that DB to the AWS cloud is that I couldn't find a way to set a sensible default for the custom param. An RDS database doesn't give you Superuser access required for ALTER SYSTEM SET kludge.param TO 'foo', nor do you get to edit postgresql.conf. (It's possible to edit existing DB parameters via RDS's DB parameter group mechanism, but adding new ones won't work. I tried.)
In addition, trying to get the current_setting(kludge.param) doesn't return anything nice if the option isn't set; instead it causes the statement to fail with an SQL error. Here's what I get when I try to update a table without setting the param in the session:
ERROR: unrecognized configuration parameter "kludge.param"
CONTEXT: PL/pgSQL function update_modifier() line 1 at assignment
Furthermore, I couldn't find a way to check for the existence of the parameter without producing the same error.
My current idea is to edit all the trigger functions (although there are many triggers, there aren't ridiculously many trigger functions) to trap error 42P02 undefined_parameter with a WHEN clause, and set a default value for the custom parameter in the error handler.
Given that my only goal seems very simple (inserts and updates work sensibly, with or without setting any custom parameters) and my solution seems to fall in the suspicious category of "clever hacks for enabling other clever hacks", I wanted to check with you guys first:
- Does anything I'm saying make any sense at all?
- Is there an easier way?
- If there isn't, is this approach likely to solve my problem?
Thanks for your time in advance.
(We're running PostgreSQL server version 9.5.10.)
postgresql aws parameter
asked May 2 '18 at 9:50
BassBass
1113
I am trying to migrate a PostgreSQL database to AWS RDS.
The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the customized options syntax for storing runtime session data. (In this case, the DB has a slew of triggers, which seem to be basically reinventing the concept of roles. Rewriting the software isn't really an option at this point, so please bear with me here.)
The main problem with migrating that DB to the AWS cloud is that I couldn't find a way to set a sensible default for the custom param. An RDS database doesn't give you Superuser access required for ALTER SYSTEM SET kludge.param TO 'foo', nor do you get to edit postgresql.conf. (It's possible to edit existing DB parameters via RDS's DB parameter group mechanism, but adding new ones won't work. I tried.)
In addition, trying to get the current_setting(kludge.param) doesn't return anything nice if the option isn't set; instead it causes the statement to fail with an SQL error. Here's what I get when I try to update a table without setting the param in the session:
ERROR: unrecognized configuration parameter "kludge.param"
CONTEXT: PL/pgSQL function update_modifier() line 1 at assignment
Furthermore, I couldn't find a way to check for the existence of the parameter without producing the same error.
My current idea is to edit all the trigger functions (although there are many triggers, there aren't ridiculously many trigger functions) to trap error 42P02 undefined_parameter with a WHEN clause, and set a default value for the custom parameter in the error handler.
Given that my only goal seems very simple (inserts and updates work sensibly, with or without setting any custom parameters) and my solution seems to fall in the suspicious category of "clever hacks for enabling other clever hacks", I wanted to check with you guys first:
- Does anything I'm saying make any sense at all?
- Is there an easier way?
- If there isn't, is this approach likely to solve my problem?
Thanks for your time in advance.
(We're running PostgreSQL server version 9.5.10.)
postgresql aws parameter
postgresql aws parameter
asked May 2 '18 at 9:50
BassBass
1113
asked May 2 '18 at 9:50
BassBass
1113
edited May 2 '18 at 10:07
Bass
asked May 2 '18 at 9:50
BassBass
1113
asked May 2 '18 at 9:50
BassBass
1113
asked May 2 '18 at 9:50
BassBass
1113
1113
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Can you runalter user bass set kludge.param TO 'foo';?
– a_horse_with_no_name
May 2 '18 at 10:30
@a_horse_with_no_name, unfortunately, no. ERROR: permission denied to set parameter "kludge.param". This is a particularly good guess though, since the account we get does indeed have the "create role" attribute, which should be the one that gets checked when trying toalter user. (As a particularly interesting behaviour, trying toalter user allnot only fails, it also causes the connection to drop with "SSL SYSCALL error: EOF detected".)
– Bass
May 2 '18 at 12:08
(After further investigation, it seems like that command crashes the entire DB: all pids in pg_stat_activity change at once.)
– Bass
May 2 '18 at 12:22
add a comment |
Can you runalter user bass set kludge.param TO 'foo';?
– a_horse_with_no_name
May 2 '18 at 10:30
@a_horse_with_no_name, unfortunately, no. ERROR: permission denied to set parameter "kludge.param". This is a particularly good guess though, since the account we get does indeed have the "create role" attribute, which should be the one that gets checked when trying toalter user. (As a particularly interesting behaviour, trying toalter user allnot only fails, it also causes the connection to drop with "SSL SYSCALL error: EOF detected".)
– Bass
May 2 '18 at 12:08
(After further investigation, it seems like that command crashes the entire DB: all pids in pg_stat_activity change at once.)
– Bass
May 2 '18 at 12:22
Can you run
alter user bass set kludge.param TO 'foo';?– a_horse_with_no_name
May 2 '18 at 10:30
Can you run
alter user bass set kludge.param TO 'foo';?– a_horse_with_no_name
May 2 '18 at 10:30
@a_horse_with_no_name, unfortunately, no. ERROR: permission denied to set parameter "kludge.param". This is a particularly good guess though, since the account we get does indeed have the "create role" attribute, which should be the one that gets checked when trying to
alter user. (As a particularly interesting behaviour, trying to alter user all not only fails, it also causes the connection to drop with "SSL SYSCALL error: EOF detected".)– Bass
May 2 '18 at 12:08
@a_horse_with_no_name, unfortunately, no. ERROR: permission denied to set parameter "kludge.param". This is a particularly good guess though, since the account we get does indeed have the "create role" attribute, which should be the one that gets checked when trying to
alter user. (As a particularly interesting behaviour, trying to alter user all not only fails, it also causes the connection to drop with "SSL SYSCALL error: EOF detected".)– Bass
May 2 '18 at 12:08
(After further investigation, it seems like that command crashes the entire DB: all pids in pg_stat_activity change at once.)
– Bass
May 2 '18 at 12:22
(After further investigation, it seems like that command crashes the entire DB: all pids in pg_stat_activity change at once.)
– Bass
May 2 '18 at 12:22
add a comment |
1 Answer
1
active
oldest
votes
AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.
The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with
EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';
followed by replicated trigger code, so that the trigger gets properly executed even in the error case.
answered May 4 '18 at 9:41
BassBass
1113
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
});
}
});
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%2f205541%2fpostgres-session-variables-kludge-on-aws-rds%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
AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.
The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with
EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';
followed by replicated trigger code, so that the trigger gets properly executed even in the error case.
answered May 4 '18 at 9:41
BassBass
1113
add a comment |
AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.
The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with
EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';
followed by replicated trigger code, so that the trigger gets properly executed even in the error case.
answered May 4 '18 at 9:41
BassBass
1113
add a comment |
AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.
The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with
EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';
followed by replicated trigger code, so that the trigger gets properly executed even in the error case.
answered May 4 '18 at 9:41
BassBass
1113
AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.
The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with
EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';
followed by replicated trigger code, so that the trigger gets properly executed even in the error case.
answered May 4 '18 at 9:41
BassBass
1113
answered May 4 '18 at 9:41
BassBass
1113
answered May 4 '18 at 9:41
BassBass
1113
answered May 4 '18 at 9:41
BassBass
1113
1113
add a comment |
add a comment |
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%2f205541%2fpostgres-session-variables-kludge-on-aws-rds%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
Can you run
alter user bass set kludge.param TO 'foo';?– a_horse_with_no_name
May 2 '18 at 10:30
@a_horse_with_no_name, unfortunately, no. ERROR: permission denied to set parameter "kludge.param". This is a particularly good guess though, since the account we get does indeed have the "create role" attribute, which should be the one that gets checked when trying to
alter user. (As a particularly interesting behaviour, trying toalter user allnot only fails, it also causes the connection to drop with "SSL SYSCALL error: EOF detected".)– Bass
May 2 '18 at 12:08
(After further investigation, it seems like that command crashes the entire DB: all pids in pg_stat_activity change at once.)
– Bass
May 2 '18 at 12:22