From: Stephen Frost <sfrost@snowman.net>
To: Steven Pousty <steve.pousty@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Trusted versus untrusted Pl language
Date: Thu, 24 Dec 2020 16:48:49 -0500
Message-ID: <20201224214849.GT27507@tamriel.snowman.net> (raw)
In-Reply-To: <CAKmB1PEX3Wd2OiA4rFJUdMUY7L+xTvQVCEcfChVCUu2eSgUPXg@mail.gmail.com>
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>
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.
>
> The SQL I am talking about is this:
> UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plr';
>
> 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
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: sfrost@snowman.net, steve.pousty@gmail.com, bruce@momjian.us, tgl@sss.pgh.pa.us, pgsql-docs@lists.postgresql.org
Subject: Re: Trusted versus untrusted Pl language
In-Reply-To: <20201224214849.GT27507@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox