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 1qzOiv-00G15V-Ge for pgsql-general@arkaria.postgresql.org; Sat, 04 Nov 2023 22:02:33 +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 1qzOiu-007jap-3Y for pgsql-general@arkaria.postgresql.org; Sat, 04 Nov 2023 22:02:32 +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 1qzOit-007jaM-M7 for pgsql-general@lists.postgresql.org; Sat, 04 Nov 2023 22:02:31 +0000 Received: from mail-yw1-x1135.google.com ([2607:f8b0:4864:20::1135]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qzOir-003zDX-2U for pgsql-general@lists.postgresql.org; Sat, 04 Nov 2023 22:02:30 +0000 Received: by mail-yw1-x1135.google.com with SMTP id 00721157ae682-5a7c08b7744so37637027b3.3 for ; Sat, 04 Nov 2023 15:02:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1699135347; x=1699740147; darn=lists.postgresql.org; h=content-transfer-encoding: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=D6hoCMRCUiTmg4fn9uTb3i8qE01HGN0M1WlCwXP5EV4=; b=aOc61hvK7TqjqokSXSClcsMKPA3iBT4txbawpB5wpI4Y7RUvm5Y/JX95uNM26Odht7 +SzycnA86NB27/RQUdOezeJN2yvLw5DyD1QcawnMiYAxFswxIrru/DFYBSmUdUTp38Hq D5F2uLgpooBZIOXPBsqr3ga/wKWx52FejGf+TDZAaySigiufq7yTqVgCoNrwkAd7cDSR K+LW1sH6xp9b9sViBEk5Ba7CV/ekIY8f7PwamT1B9dOtPRrflWQRHG07N5TnDltJ7BRB 1UGubVEI0qKUhffrO2x7xdxLQ/EVPBSgTX1hgeCgoKyzIdZ2y/LxaXoVZThN8bw30a7M qbDQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1699135347; x=1699740147; h=content-transfer-encoding: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=D6hoCMRCUiTmg4fn9uTb3i8qE01HGN0M1WlCwXP5EV4=; b=qj1KDdsFWqpBKcw2QgAdXbx1RDAZ5uYHxdXf0yTAi89NItXPn18uuo+PkZaZXf1iXk r0n+DYnNl6w+VKPjA7b4fxdw4+d064EWGetW/xDlxn6Kq2B87D/OYwH/5QT6x397u8b5 tvDyLRrhDJyNWv6GcYZ5el+sLS0WKuu8J+mEnXB+deXR7E7NApNqUV5rMa68MgckZX+Q hqRHWIqJ4il/cuaEpSaAnUjJcKcbAXI1/3wRZqI7NduxVG+0QfLSPuEVZv1p3+q363VY 6/pTFhVgfvp8H+7K0y8nMVXIFqs0hkaKu+vDnwN6rW6ZEVQLJ8AFGUndoyIa0d5HnpuU v8RA== X-Gm-Message-State: AOJu0YwDARRLBoWCZ+XLMsAnnxNfi4nX+ORgLyu21QteaJFuhZVLYmWC rxkmI7ejFHRj6/Yo/vlrnmyeYVtWF5U= X-Google-Smtp-Source: AGHT+IHSZKJZCgnkz2ScpNDYu5XvXOL7mvYjI+SYlCJOjrIqFqicfuNvQYQH5IxLipN6w77g+PCd+Q== X-Received: by 2002:a05:690c:fd4:b0:5a7:ab45:539d with SMTP id dg20-20020a05690c0fd400b005a7ab45539dmr7891245ywb.2.1699135347630; Sat, 04 Nov 2023 15:02:27 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810::44? ([2600:1700:bd2c:b810::44]) by smtp.googlemail.com with ESMTPSA id z145-20020a0dd797000000b005a0f9718a5fsm2454806ywd.78.2023.11.04.15.02.27 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 04 Nov 2023 15:02:27 -0700 (PDT) Message-ID: <9faf2c18-5029-4d77-a937-ece6d25f9678@gmail.com> Date: Sat, 4 Nov 2023 17:02:26 -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: <20231104213303.4mtgamfyo4v5i33e@hjp.at> <20231104215321.ojqhqo346bbsistg@hjp.at> From: Ron In-Reply-To: <20231104215321.ojqhqo346bbsistg@hjp.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/4/23 16:53, Peter J. Holzer wrote: > On 2023-11-04 21:42:34 +0000, Brent Wood wrote: >>>> 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. >>> This seems strange to me. What kind of troubleshooting requires to >>> ability to ALTER TABLE but not to do DML? >> Where your db admin & data admin are separated. Data security issues can >> require minimal access to data, which a dba does not necessarily require. >> Especially when the DBA role is contracted out. >> >> Sort of along this line, we have offloaded user management to AD, so our DB >> user management is now carried out via in-house IT, who are not DBA's and have >> no access to data. > This doesn't answer the question why ALTER TABLE privilege would be > required. I bet the Good Idea Fairy whispered something into the CISO's ear. -- Born in Arizona, moved to Babylonia.