Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mvNXb-0005gr-6l for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Dec 2021 17:49:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mvNXZ-0007TP-JF for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Dec 2021 17:49:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mvNXY-0007TG-W1 for pgsql-hackers@lists.postgresql.org; Thu, 09 Dec 2021 17:49:09 +0000 Received: from mail-pl1-x62a.google.com ([2607:f8b0:4864:20::62a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mvNXU-0002Bq-Fu for pgsql-hackers@postgresql.org; Thu, 09 Dec 2021 17:49:08 +0000 Received: by mail-pl1-x62a.google.com with SMTP id b11so4424155pld.12 for ; Thu, 09 Dec 2021 09:49:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=KwhzYSgkpJICj35trw6aJLMxQe/pjjwr0WRBjXW31QE=; b=UkM61soKrZQ7KS2jkoCmVUIbnJXepzg4PIYAIi7qdGTI+M43w6lustVKI5aM4BpmKy LP5uScNxwzfktKIqCOpIKcx8iT8Yw4dmqHR8y9UMHBRRT97wjyP0DsuiKyDul40/MpIi byuHEy4mXpXLT3vqsZ62UpRgIlg3oxroCei0INEr4zztIBamCPt+Q1V1bVIHHbx6HCw3 vFsMxhzmTsplPpQ4jMC2SZKeulhJwmlm+O0+abbj630GijZ0GUgNnoI9QQ/ESB/681Nk TEpWO5oFlxAMNlcLKxiP2zd0s7NwOqsSfvpNRrapo3rogopGgqYb1wMvSlBZr1/hdvdw F3Uw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=KwhzYSgkpJICj35trw6aJLMxQe/pjjwr0WRBjXW31QE=; b=WFro38ZOXqrqlhHRShWkA+1BB3JM0Z5nAFrBGydpRWKCy1q3RU6GvyVDNTOBT509JG etDDKhJU0yz1x5aMrg/plUBIEsDTiBLaIX2806qV6mnnm9DYSeD9K4Hs/NNQHgwc1o2b mMMcxpcZGOW+0htc+CDWqWsgLKsWLxY2Eiv3mYDstLJAnIgtJZV4JDTqYgYSuzQkroe6 63m82ylm8FbM+DvpgY3qZjKoCHXrlgLKMV109Uf/d5xEiW7ChzFr6Cn7La2zv5E0anDH DJwIzlXO8k7V3i+K8fncfcIJ7MWntBV6ghjzqIeVxB6j2LkC7odDSOA1Hzz9jydi/6t6 jxDg== X-Gm-Message-State: AOAM532Se00+wf2sGhPm5daCmyEePCU7OnyHCbqZRHtN9FPYoP19fw91 F3glcMa9yknT0D5SrgBs/VpBR/fU9NxqxG8vMvChk5fWtpKyBO9fF1T32YddOmo5GD1rqESUnzR RpH3x0HCImUj3pUQ0rBF8Yhk9mMEJtP5BNxCqUa2CrTWHgqyVdWhhqDgPfzVHJaOmt9GqKtAI0q P+4kTjDLH81tQnPH+zb32GQaYZYSOoeIZpQ9B/ X-Google-Smtp-Source: ABdhPJyVW73GmnTZFIf657Yi2qwt7mqH8ChSg+yW9gIaBmGMiH747Wqu3eak/QLey/E/AGfbH09RSw== X-Received: by 2002:a17:903:1105:b0:143:a593:dc6e with SMTP id n5-20020a170903110500b00143a593dc6emr69163489plh.6.1639072142053; Thu, 09 Dec 2021 09:49:02 -0800 (PST) Received: from laptop280-ma-us.hitronhub.home (24-113-193-150.wavecable.com. [24.113.193.150]) by smtp.gmail.com with ESMTPSA id y128sm294903pfb.181.2021.12.09.09.49.00 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 09 Dec 2021 09:49:01 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: Non-superuser subscription owners From: Mark Dilger In-Reply-To: Date: Thu, 9 Dec 2021 09:48:59 -0800 Cc: Amit Kapila , Jeff Davis , Andrew Dunstan , PostgreSQL-development Content-Transfer-Encoding: quoted-printable Message-Id: <2C7236B5-9BD3-4994-B9C3-F6F59C23C2E0@enterprisedb.com> References: <9DFC88D3-1300-4DE8-ACBC-4CEF84399A53@enterprisedb.com> <6BB4451E-7B1B-474C-BD1F-DB7531E720C6@enterprisedb.com> <6A2B0FF6-CC86-48CE-B0D3-5401AA5CFEA9@enterprisedb.com> <1BB0A553-3FE9-4A91-A975-6F9D8C157FBD@enterprisedb.com> <7C62876F-5D19-4B5C-96AC-5590B10A49B2@enterprisedb.com> <66f55a6b9ff7bdd6bfd0d05e8fd8351690e69e23.camel@j-davis.com> <18b267939a9d1ae27e9b3643d53395f4e29ea6ce.camel@j-davis.com> <785e8b8ab06f123cf754862557bd3171aeba4d65.camel@j-davis.com> <4E93F649-C11A-40F0-BE34-6AB45507B807@enterprisedb.com> <7A34E620-0D86-4720-91A3-624C0B8C1A6C@enterprisedb.com> <4901C87E-AB16-4552-88B8-2DB204F61D7E@enterprisedb.com> <8BADB085-239C-4572-847D-99AA1DB84E36@enterprisedb.com> To: Robert Haas X-Mailer: Apple Mail (2.3608.120.23.2.7) X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Dec 9, 2021, at 7:47 AM, Robert Haas wrote: >=20 > 1 and 2B seem to require changing the same code, or related code. 1A > seems to require a completely different set of changes. If I'm right > about that, it seems like a good reason for doing 1+2B first and > leaving 2A for a separate patch. There are unresolved problems with 2A and 3 which were discussed = upthread. I don't want to include fixes for them in this patch, as it = greatly expands the scope of this patch, and is a logically separate = effort. We can come back to those problems after this first patch is = committed. Specifically, a non-superuser owner can perform ALTER SUBSCRIPTION and = do things that are morally equivalent to creating a new subscription. = This is problematic where things like the connection string are = concerned, because it means the non-superuser owner can connect out to = entirely different servers, without any access control checks to make = sure the owner should be able to connect to these servers. This problem already exists, right now. I'm not fixing it in this first = patch, but I'm also not making it any worse. The solution Jeff Davis proposed seems right to me. We change = subscriptions to use a foreign server rather than a freeform connection = string. When creating or altering a subscription, the role performing = the action must have privileges on any foreign server they use. =E2=80=94 Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company