Thread: Trusted versus untrusted Pl language
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/plpython.html Description: Hey all: This page & the PL/PERL page are the closest I have seen in the docs about trusted versus untrusted languages. It would be great if we could add a subtopic and 1 or 2 paragraphs on this page https://www.postgresql.org/docs/current/xplang.html Possibly outline: A) Explain to users what trusted versus untrusted in terms of language extensions. 1) Differentiate that from non-risky versus risky 2) Explain why, by default, functions written in untrusted languages need to be added by superuser. B) It would be great to give an example workflow of working with untrusted languages 1) Developer uses superuser on their own machine or makes the language trusted 2) Send function to the DBA 3) Function goes through security review and testing 4) If it passes then the DBA installs in a production DB C) An example on how to make a language trusted in a db. Thanks Steve
On Wed, Dec 23, 2020 at 08:24:13PM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/13/plpython.html > Description: > > Hey all: > This page & the PL/PERL page are the closest I have seen in the docs about > trusted versus untrusted languages. > > It would be great if we could add a subtopic and 1 or 2 paragraphs on this > page https://www.postgresql.org/docs/current/xplang.html Uh, what about this? https://www.postgresql.org/docs/13/xplang-install.html > Possibly outline: > A) Explain to users what trusted versus untrusted in terms of language > extensions. > 1) Differentiate that from non-risky versus risky > 2) Explain why, by default, functions written in untrusted languages > need to be added by superuser. > B) It would be great to give an example workflow of working with untrusted > languages > 1) Developer uses superuser on their own machine or makes the language > trusted > 2) Send function to the DBA > 3) Function goes through security review and testing > 4) If it passes then the DBA installs in a production DB > C) An example on how to make a language trusted in a db. Does that URL need more detail? -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
On Wed, Dec 23, 2020 at 2:41 PM Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Dec 23, 2020 at 08:24:13PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/13/plpython.html
> Description:
>
> Hey all:
> This page & the PL/PERL page are the closest I have seen in the docs about
> trusted versus untrusted languages.
>
> It would be great if we could add a subtopic and 1 or 2 paragraphs on this
> page https://www.postgresql.org/docs/current/xplang.html
Uh, what about this?
https://www.postgresql.org/docs/13/xplang-install.html
> Possibly outline:
> A) Explain to users what trusted versus untrusted in terms of language
> extensions.
> 1) Differentiate that from non-risky versus risky
> 2) Explain why, by default, functions written in untrusted languages
> need to be added by superuser.
> B) It would be great to give an example workflow of working with untrusted
> languages
> 1) Developer uses superuser on their own machine or makes the language
> trusted
> 2) Send function to the DBA
> 3) Function goes through security review and testing
> 4) If it passes then the DBA installs in a production DB
> C) An example on how to make a language trusted in a db.
Does that URL need more detail?
-----------
Thanks for pointing that out Bruce. It is really helpful and I must have missed it as I was reading through the doc.
I would say the only thing it needs is:
1. A Trusted vs. Untrusted bold header so it catches the eye
2. One or two sentences explaining that trusted and untrusted is not the same thing as risky
1. A Trusted vs. Untrusted bold header so it catches the eye
2. One or two sentences explaining that trusted and untrusted is not the same thing as risky
3. An example of how to make a pre-installed untrusted langue into a trusted language
What do you think?
That would have helped me A LOT when I was learning this stuff. I would also love to point this to people when they say PL/Python is untrusted therefore you should never use it.
Thanks again
Steve
Steven Pousty <steve.pousty@gmail.com> 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.
regards, tom lane
On Wed, Dec 23, 2020 at 07:38:16PM -0500, Tom Lane wrote: > Steven Pousty <steve.pousty@gmail.com> 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. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
On Wed, Dec 23, 2020 at 4:49 PM Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Dec 23, 2020 at 07:38:16PM -0500, Tom Lane wrote:
> Steven Pousty <steve.pousty@gmail.com> 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
On Thu, Dec 24, 2020 at 1:01 PM Steven Pousty <steve.pousty@gmail.com> 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.
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.johnston@gmail.com> wrote:
On Thu, Dec 24, 2020 at 1:01 PM Steven Pousty <steve.pousty@gmail.com> 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.