Re: Trusted versus untrusted Pl language
On Wed, Dec 23, 2020 at 4:49 PM Bruce Momjian wrote: > On Wed, Dec 23, 2020 at 07:38:16PM -0500, Tom Lane wrote: > > Steven Pousty writes: > > > 3. An example of how to make a pre-installed untrusted langue into a > > > trusted language > > > > Under what circumstances would that be a good idea? > > > > I can't imagine that we'd really want to recommend end users doing > > that, but an example would surely be taken as a recommendation > > that it's okay to do it. > > Right. The language has to provide some sandbox environment for us to > consider it safe, e.g. Perl, but not Python. PL/pgSQL is safe since it > doesn't have any interface to external resources. > > --- 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. 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. Thanks Steve
Re: Trusted versus untrusted Pl language
On Thu, Dec 24, 2020 at 1:01 PM Steven Pousty wrote: > The SQL I am talking about is this: > UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plr'; > You seem to be missing the point. The language is either trusted, or it's not. Modifying the catalogs is not part of a "good flow", ever. In short, "don't use trusted languages ever". If a specific requirement can only be implemented using a trusted language maybe there is a reason to use it - in development and production (if your DBA will let you) - but more likely you are better off writing an out-of-database client application and doing the "trusted" stuff there. David J.
Re: Trusted versus untrusted Pl language
Ok David but that is not what I have heard from a lot of other people in the PostgreSQL community. On Thu, Dec 24, 2020 at 1:26 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Dec 24, 2020 at 1:01 PM Steven Pousty > wrote: > >> The SQL I am talking about is this: >> UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plr'; >> > > You seem to be missing the point. The language is either trusted, or it's > not. Modifying the catalogs is not part of a "good flow", ever. In short, > "don't use trusted languages ever". If a specific requirement can only be > implemented using a trusted language maybe there is a reason to use it - in > development and production (if your DBA will let you) - but more likely you > are better off writing an out-of-database client application and doing the > "trusted" stuff there. > > David J. > >
Re: Trusted versus untrusted Pl language
Greetings, * Steven Pousty (steve.pou...@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 signature.asc Description: PGP signature
