Trusted versus untrusted Pl language

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
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:

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