Date: Thu, 24 Dec 2020 16:48:49 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Steven Pousty <steve.pousty@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-docs@lists.postgresql.org
Subject: Re: Trusted versus untrusted Pl language
Message-ID: <20201224214849.GT27507@tamriel.snowman.net>
References: <160875505390.674.9084620641912624635@wrigleys.postgresql.org>
 <20201223224140.GA13055@momjian.us>
 <CAKmB1PFuxkvA5-wD7KkW0yLp_4-+ieeVz7a2C5udg+GQVy9mgA@mail.gmail.com>
 <748034.1608770296@sss.pgh.pa.us>
 <20201224004922.GB13055@momjian.us>
 <CAKmB1PEX3Wd2OiA4rFJUdMUY7L+xTvQVCEcfChVCUu2eSgUPXg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="0rpW5bEisVBStM29"
Content-Disposition: inline
In-Reply-To: 
 <CAKmB1PEX3Wd2OiA4rFJUdMUY7L+xTvQVCEcfChVCUu2eSgUPXg@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--0rpW5bEisVBStM29
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Steven Pousty (steve.pousty@gmail.com) wrote:
> If you consider the application developer or data scientist's perspective
> it makes total sense. I don't like the pattern of appdevs always working =
as
> the postgres user, it encourages bad patterns and can often blow up when
> you move the application to production.

> Instead I think a good flow for an appdev or a data scientists to follow
> when developing their function in Pl/Python or PL/R is:
> 1) Make the langauge trusted on the appdevs or data scientist's instance =
of
> Postgres. Most developers either work on a cluster on their laptop or in a
> container.

The way to give non-superusers access to things which are usually
superuser-only is to set up a way to have that ability GRANT'd to them,
either through privileges on a function, or through a new role to manage
that access.

In this case, it would seem likely that the right answer would be a new
role along the lines of "pg_use_untrusted_language", which would then
allow a user who has been GRANT'd that role to be able to create
functions in untrusted languages.  An interesting question might be if
we'd allow such a role to create C language functions or not.

Clearly, such a privilege could be used by someone to get superuser
access themselves, but that's nothing new when it comes to such roles
and I appreciate the angle you're taking here where you'd like the
developer to be able to operate as a non-superuser in general while
still being able to create such functions.

> 2) Send the finished product to the DBA and security teams for review.
> 3) If it passes review and testing then you can put it into production.
>=20
> The SQL I am talking about is this:
> UPDATE pg_language SET lanpltrusted =3D true WHERE lanname LIKE 'plr';
>=20
> There should also be a reminder to NOT do this in production.

I can't agree with this part, it's just not a good idea for anyone to be
issuing direct UPDATE calls against the catalogs.

Thanks,

Stephen

--0rpW5bEisVBStM29
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJf5QzBAAoJEO1sijiDR2RV/XkP/2u5RaLRPubeyHE4YBP4QMUL
mxYkmsaa+eBlQsBRJQxMzkkpnN5kyMCKr0e6p7vhEuqnMifpcXvPonhDJF5Isiu2
rqG+z7ON9azf2E5NUoXa/7ZE7OznX04HLDlTpkCbkRnfmb7iCNNbnk0eNxEcYAkh
zGYplEFcO8nXCqNIra/B+AVAEO0ISksX+XA8FwiU+Lz+160D6CIVVFt/VoFgmCd9
jusAo5LlNrj8rJwQcNrPB0hd7zhhgXrTmEchBMnaHgrPNd1DLWRZEwKw8GiFxp+y
GOY7AN4x5arvX8ExCdO+ffEQFn3dC1PkZah5wj/o1t5ij3BLvz4ExwYNUE4hxv4J
rA0gwZScgnYMNLidkVHAD5G7k0Go4LzUAF7bk5RSFRI25yEKVOi2D4jMghQC6Ppj
eTif85ytnz0JuLTDkPSk+TpHdd7LC/P4Cbqe/7v/YVm9fq+qn4JmeTzfGYhHnYob
jdK58EIFoI+6clLGHuKpKL/fT4+lS0U2cGTY+emqelPBuRCd5G7NDw8GIKf7S7wy
8BVTLOXOTgrhftekMUkDMVfsYs9hD4tYeNqcbfzT0SZVlfOtxDqDWF5ZNS5hmOF/
of2tn3QR6orH+B/YZWOHzfvwvdMtBWUMPM/VHD4hBeAL7eqlQniN0ptw8M9pN7P5
FlPO0TmaPOzXQUOa8yhS
=fEBS
-----END PGP SIGNATURE-----

--0rpW5bEisVBStM29--