Re: Postgres limitation in user management

Ron <ronljohnsonjr@xxxxxxxxx> · Sat, 4 Nov 2023 09:45:01 -0500



    How can you practically support a database without being
    able to look at a table?

    
    On 11/3/23 01:26, Kar, Swapnil (TR
      Technology) wrote:

    
        Hello Team,
         
        I am facing a limitation
            with Postgres user management and require your assistance or
            input around it. Let me brief you the scenario here –
            
         
        We have 2 sets of
            database user groups –
         
        
          App – who
              owns the application schemas (and tables)
          Support – who
              provides db support
              
        
        We want Support users to
            have no SELECT or DML privilege but only ALTER TABLE to
            perform any troubleshooting in the database.
         
        In Postgres, to have
            alter system privilege one should be the owner of the
            schema/table but App users are not keen to make them
            temporarily as owner of the schema during the investigation
            time. Because they loose the ownership and can’t perform
            ALTER table commands. 
         
        Now another option 2 is
            to – grant app_user to support_user;
            
        This way ownership is
            not transferred but support is able to perform select and
            DML.
            
         
        Option 3 is to grant
            rds_superuser privilege to support and in this case they
            will become more powerful superuser in the DB. This is also
            not a solution for our requirement.
            
         
        Do you think there is a
            way to deal with this situation ?
         
        Any help and guidance
            here is highly appreciated.
            
         
        Regards,
        Swapnil 
      
      This e-mail is for the sole use of the intended recipient and
      contains information that may be privileged and/or confidential.
      If you are not an intended recipient, please notify the sender by
      return e-mail and delete this e-mail and any attachments. Certain
      required legal entity disclosures can be accessed on our website:
      https://www.thomsonreuters.com/en/resources/disclosures.html
    
    
    -- 

      Born in Arizona, moved to Babylonia.