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 <mark.dilger@enterprisedb.com>
In-Reply-To: 
 <CAA4eK1+V5YaPn1R+_PDUCLMXx-tw7y2DrhV8hS0k8KzF1Kh-xg@mail.gmail.com>
Date: Fri, 19 Nov 2021 08:12:27 -0800
Cc: Jeff Davis <pgsql@j-davis.com>,
 Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Robert Haas <robertmhaas@gmail.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <22738092-BF0E-4C70-977B-D51EE43BBCD8@enterprisedb.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>
 <0774acfb4e613b9959c4e4995ba163cdadc454ef.camel@j-davis.com>
 <2EA8302B-9805-4FAA-A95D-C4D5CEB82916@enterprisedb.com>
 <47f0d0e528451041c04d61c7dcf18ad01174d2db.camel@j-davis.com>
 <CAA4eK1+V5YaPn1R+_PDUCLMXx-tw7y2DrhV8hS0k8KzF1Kh-xg@mail.gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/22738092-BF0E-4C70-977B-D51EE43BBCD8%40enterprisedb.com>
Precedence: bulk


> On Nov 19, 2021, at 2:23 AM, Amit Kapila <amit.kapila16@gmail.com> =
wrote:
>=20
> I was thinking why not separate the ownership and "run as" privileges
> for the subscriptions? We can introduce a new syntax in addition to
> the current syntax for "Owner" for this as:
>=20
> Create Subscription sub RUNAS <role_name> ...;
> Alter Subscription sub RUNAS <role_name>
>=20
> Now, RUNAS role will be used to apply changes and perform the initial
> table sync. The owner will be tied to changing some of the other
> properties (enabling, disabling, etc.) of the subscription. Now, we
> still need a superuser to create subscription and change properties
> like CONNECTION for a subscription but we can solve that by having
> roles specific to it as being indicated by Mark in some of his
> previous emails.

I feel disquieted about the "runas" idea.  I can't really say why yet.  =
Maybe it is ok, but it feels like a larger design decision than just an =
implementation detail about how subscriptions work.  We should consider =
if we won't soon be doing the same thing for other parts of the system.  =
If so, we should choose a solution that makes sense when applied more =
broadly.

Security definer functions could benefit from splitting the owner from =
the runas role.

Event triggers might benefit from having a runas role.  Currently, event =
triggers are always owned by superusers, but we've discussed allowing =
non-superuser owners.  That proposal still has outstanding issues to be =
resolved, so I can't be sure if runas would be helpful, but it might.

Table triggers might benefit from having a runas role.  I don't have a =
proposal here, just an intuition that we should think about this before =
designing how "runas" works.

=E2=80=94
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company