Message-ID: <c6681e45fb41e7397b3a7c353c607d2bfbcc9d87.camel@j-davis.com>
Subject: Re: Non-superuser subscription owners
From: Jeff Davis <pgsql@j-davis.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan
 <andrew@dunslane.net>, PostgreSQL-development
 <pgsql-hackers@postgresql.org>,  Robert Haas <robertmhaas@gmail.com>
Date: Tue, 30 Nov 2021 12:42:13 -0800
In-Reply-To: 
 <CAA4eK1JJsBzJsStA_LYrJu0WmOG+tWXcS9G4mY1G4CsQVBcaQw@mail.gmail.com>
References: <9DFC88D3-1300-4DE8-ACBC-4CEF84399A53@enterprisedb.com>
	 <c1c334a2-5d85-687f-f0dc-0ac8c422ca1a@dunslane.net>
	 <CD02D159-7260-4247-888F-2CFA7AD3798D@enterprisedb.com>
	 <6BB4451E-7B1B-474C-BD1F-DB7531E720C6@enterprisedb.com>
	 <aa28945f9f348307c8ac833a937b5fca732668b8.camel@j-davis.com>
	 <6A2B0FF6-CC86-48CE-B0D3-5401AA5CFEA9@enterprisedb.com>
	 <b6f6ed921b6e219875801857204b14bbc8782e5e.camel@j-davis.com>
	 <CF56AC0D-7495-4E8D-A48F-FF38BD8074EB@enterprisedb.com>
	 <CAA4eK1KHE_xmxk==CqTcTzceC_n00tRWBLm3DGUWhaaPTguz3A@mail.gmail.com>
	 <1BB0A553-3FE9-4A91-A975-6F9D8C157FBD@enterprisedb.com>
	 <CAA4eK1LBjtqHH2h4TO7FHG=FJRfygfkeEb7AFj-dh6rqg3xqUA@mail.gmail.com>
	 <CC5DAC9C-F767-4256-BFEF-32D5A566D254@enterprisedb.com>
	 <7C62876F-5D19-4B5C-96AC-5590B10A49B2@enterprisedb.com>
	 <66f55a6b9ff7bdd6bfd0d05e8fd8351690e69e23.camel@j-davis.com>
	 <CAA4eK1KnEptxtJTzo9LNb2VgRBYcBWxY+NSx2hJR8iUM8JXktw@mail.gmail.com>
	 <18b267939a9d1ae27e9b3643d53395f4e29ea6ce.camel@j-davis.com>
	 <CAA4eK1K1=DbXCNfA32urxZga6YxeiCvcTHvJu-+BtLCtVgqJ2w@mail.gmail.com>
	 <785e8b8ab06f123cf754862557bd3171aeba4d65.camel@j-davis.com>
	 <CAA4eK1JJsBzJsStA_LYrJu0WmOG+tWXcS9G4mY1G4CsQVBcaQw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/c6681e45fb41e7397b3a7c353c607d2bfbcc9d87.camel%40j-davis.com>
Precedence: bulk

On Tue, 2021-11-30 at 17:25 +0530, Amit Kapila wrote:
> I think it would be better to do it before we allow subscription
> owners to be non-superusers.

There are a couple other things to consider before allowing non-
superusers to create subscriptions anyway. For instance, a non-
superuser shouldn't be able to use a connection string that reads the
certificate file from the server unless they also have
pg_read_server_files privs.

> Yeah, it is possible that is why I suggested in one of the emails
> above to allow changing the owners only for disabled subscriptions.

The current patch detects the following cases at the transaction
boundary:

 * ALTER SUBSCRIPTION ... OWNER TO ...
 * ALTER ROLE ... NOSUPERUSER
 * privileges revoked one way or another (aside from the RLS/WCO
problems, which will be fixed)

If we want to detect at row boundaries we need to capture all of those
cases too, or else we're being inconsistent. The latter two cannot be
tied to whether the subscription is disabled or not, so I don't think
that's a complete solution.

How about (as a separate patch) we just do maybe_reread_subscription()
every K operations within a transaction? That would speed up
permissions errors if a revoke happens.

Regards,
	Jeff Davis