Postgres “session variables” kludge on AWS RDS












2















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:




  1. Does anything I'm saying make any sense at all?

  2. Is there an easier way?

  3. 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.)










share|improve this question
















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 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













  • (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
















2















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:




  1. Does anything I'm saying make any sense at all?

  2. Is there an easier way?

  3. 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.)










share|improve this question
















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 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













  • (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














2












2








2








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:




  1. Does anything I'm saying make any sense at all?

  2. Is there an easier way?

  3. 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.)










share|improve this question
















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:




  1. Does anything I'm saying make any sense at all?

  2. Is there an easier way?

  3. 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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 2 '18 at 10:07







Bass

















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 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













  • (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











  • @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

















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










1 Answer
1






active

oldest

votes


















0














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.






share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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.






    share|improve this answer




























      0














      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.






      share|improve this answer


























        0












        0








        0







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered May 4 '18 at 9:41









        BassBass

        1113




        1113






























            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            الفوسفات في المغرب

            Four equal circles intersect: What is the area of the small shaded portion and its height

            بطل الاتحاد السوفيتي