Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzHtb-00FIFV-Qg for pgsql-general@arkaria.postgresql.org; Sat, 04 Nov 2023 14:45:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qzHta-006KUv-Gx for pgsql-general@arkaria.postgresql.org; Sat, 04 Nov 2023 14:45:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzHtZ-006KUn-W5 for pgsql-general@lists.postgresql.org; Sat, 04 Nov 2023 14:45:06 +0000 Received: from mail-yw1-x1134.google.com ([2607:f8b0:4864:20::1134]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qzHtX-003w7r-6H for pgsql-general@lists.postgresql.org; Sat, 04 Nov 2023 14:45:04 +0000 Received: by mail-yw1-x1134.google.com with SMTP id 00721157ae682-5a7c011e113so36552317b3.1 for ; Sat, 04 Nov 2023 07:45:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1699109102; x=1699713902; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=kDkXmEXXPcofLnph1vBvn5Fb3izu4D3IPowHuGzcqvk=; b=i6zOP7Qb17tO/3RHLzMm6CSIBB159Wni7AWjEde6QhAxpd25CrcBQJ6ljfRaSm0zFA Ao9Oi1TlWSN5FDDvsWUGMkxk15L6xplR+ZFlcbtvlE7+Y9FQouiPS+g7wUJmNvi/fU8r 76pqNYtJP75O1YLeUuHhpLHXG0343o++Gy6wj4kMaQmYuR47EGcRZpiCBuhoUjLaMVq7 JXi27b9hwD0gCoNET9z5CR4Moe9HoYW1G7SJM7b8lueMU1ff1BJUue4n7dMof0jEnClr WcGbaaZ2EZV74IJ1TGI9vArOD3b8eDPKRpPWIusQL6jilnKRoNBY4DSZsYk++hmsxLqE YMUg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1699109102; x=1699713902; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=kDkXmEXXPcofLnph1vBvn5Fb3izu4D3IPowHuGzcqvk=; b=HTwbqOStm7S7kuEzJXeWGPcnNfrpr0AsS/xapp8LWVT5o41nIRMRNVhzeu5/q34Xiu JUmd1YpZnoVv/YT2sRjYEKwu0c44GIUE0nruhQ0xAjsiksmfY210rG9bgYrluD/pK14w AQ/ozyoQMgI2FUqLLQfmAL/trgHgPAC6Ao/Vjf8WTeTJQv/oyNkZ4JIuHi3ucG45kL8T GTiJxZvYeNXD9A59C8aRqZYrTPLzykUZZbEbsWSkyfFmzBujUcizoQ6Uq2i6qxJEVa1K utc9tXcA3WM58j9mXzv9AfqkyJREsxo+VoHimnflISPCNOFdx4C0sRX+m11Bpzu3pp3x KdFw== X-Gm-Message-State: AOJu0Yzegt1m8GEp/AIoXnxg4VoMyYPzI1WLYsLPyHlIKuAsFvdW2HqO T/aZEwrmlzbY8kq2Gq7vy88fFRM6wLU= X-Google-Smtp-Source: AGHT+IG+xAo/NKPuMSfYTm3ApJfjAP9/+tFvBkgFrWsD6AsK4vjA+PNSCxOjxvX4sx5mZJHoYiALzA== X-Received: by 2002:a25:ce8c:0:b0:da0:37c8:9f00 with SMTP id x134-20020a25ce8c000000b00da037c89f00mr23569742ybe.36.1699109102097; Sat, 04 Nov 2023 07:45:02 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810::44? ([2600:1700:bd2c:b810::44]) by smtp.googlemail.com with ESMTPSA id m6-20020a056902004600b00d72176bdc5csm1841205ybh.40.2023.11.04.07.45.01 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 04 Nov 2023 07:45:01 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------jTL0OO0qvVg1qlGjgpm9ZSVW" Message-ID: <1cda5011-4de0-4a85-bebb-79d0cbe7a12f@gmail.com> Date: Sat, 4 Nov 2023 09:45:01 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Postgres limitation in user management Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------jTL0OO0qvVg1qlGjgpm9ZSVW Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 – > > 1. *App*– who owns the application schemas (and tables) > 2. *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. --------------jTL0OO0qvVg1qlGjgpm9ZSVW Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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 –

 

  1. App – who owns the application schemas (and tables)
  2. 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.
--------------jTL0OO0qvVg1qlGjgpm9ZSVW--