How to switch session role securely?












0















I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.



I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.



However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.



I tried using SET SESSION AUTHORIZATION, but the docs say




The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.




And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.



The PostreSQL GRANT docs say




[A role] may grant or revoke membership in itself from a database session where the session user matches the role.




I take that to suggest that I should be able to do something like



-- from a connection to the "service" role

SET SESSION ROLE tenant_1; -- switch to a new role
REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role

-- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges


But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.



Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?



Or do I just have to be prepared to manage a whole bunch of username + password pairs?



Or is there some other way to accomplish what I'm trying?









share







New contributor




Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

























    0















    I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.



    I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.



    However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.



    I tried using SET SESSION AUTHORIZATION, but the docs say




    The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.




    And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.



    The PostreSQL GRANT docs say




    [A role] may grant or revoke membership in itself from a database session where the session user matches the role.




    I take that to suggest that I should be able to do something like



    -- from a connection to the "service" role

    SET SESSION ROLE tenant_1; -- switch to a new role
    REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role

    -- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges


    But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.



    Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?



    Or do I just have to be prepared to manage a whole bunch of username + password pairs?



    Or is there some other way to accomplish what I'm trying?









    share







    New contributor




    Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.























      0












      0








      0








      I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.



      I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.



      However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.



      I tried using SET SESSION AUTHORIZATION, but the docs say




      The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.




      And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.



      The PostreSQL GRANT docs say




      [A role] may grant or revoke membership in itself from a database session where the session user matches the role.




      I take that to suggest that I should be able to do something like



      -- from a connection to the "service" role

      SET SESSION ROLE tenant_1; -- switch to a new role
      REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role

      -- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges


      But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.



      Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?



      Or do I just have to be prepared to manage a whole bunch of username + password pairs?



      Or is there some other way to accomplish what I'm trying?









      share







      New contributor




      Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.












      I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.



      I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.



      However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.



      I tried using SET SESSION AUTHORIZATION, but the docs say




      The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.




      And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.



      The PostreSQL GRANT docs say




      [A role] may grant or revoke membership in itself from a database session where the session user matches the role.




      I take that to suggest that I should be able to do something like



      -- from a connection to the "service" role

      SET SESSION ROLE tenant_1; -- switch to a new role
      REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role

      -- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges


      But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.



      Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?



      Or do I just have to be prepared to manage a whole bunch of username + password pairs?



      Or is there some other way to accomplish what I'm trying?







      postgresql security role





      share







      New contributor




      Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.










      share







      New contributor




      Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.








      share



      share






      New contributor




      Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 3 mins ago









      DathanDathan

      1011




      1011




      New contributor




      Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Dathan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          0






          active

          oldest

          votes











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


          }
          });






          Dathan is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229331%2fhow-to-switch-session-role-securely%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








          Dathan is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          Dathan is a new contributor. Be nice, and check out our Code of Conduct.













          Dathan is a new contributor. Be nice, and check out our Code of Conduct.












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




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229331%2fhow-to-switch-session-role-securely%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

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh