Trusted versus untrusted Pl language

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Trusted versus untrusted Pl language

apt.postgresql.org Repository Update
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
Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

Bruce Momjian
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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee



Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

Steven Pousty


On Wed, Dec 23, 2020 at 2:41 PM Bruce Momjian <[hidden email]> 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
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
 
Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

Tom Lane-2
Steven Pousty <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

Bruce Momjian
On Wed, Dec 23, 2020 at 07:38:16PM -0500, Tom Lane wrote:
> Steven Pousty <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee



Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

Steven Pousty


On Wed, Dec 23, 2020 at 4:49 PM Bruce Momjian <[hidden email]> wrote:
On Wed, Dec 23, 2020 at 07:38:16PM -0500, Tom Lane wrote:
> Steven Pousty <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

David G Johnston
On Thu, Dec 24, 2020 at 1:01 PM Steven Pousty <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

Steven Pousty
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 <[hidden email]> wrote:
On Thu, Dec 24, 2020 at 1:01 PM Steven Pousty <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Trusted versus untrusted Pl language

Stephen Frost
In reply to this post by Steven Pousty
Greetings,

* Steven Pousty ([hidden email]) 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 (836 bytes) Download Attachment