Re: Trusted versus untrusted Pl language

Greetings,

* Steven Pousty (steve(dot)pousty(at)gmail(dot)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