pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedTrusted versus untrusted Pl language
9+ messages / 6 participants
[nested] [flat]
* Trusted versus untrusted Pl language
@ 2020-12-23 20:24 PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 9+ messages in thread
From: PG Doc comments form @ 2020-12-23 20:24 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: steve.pousty@gmail.com
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
@ 2020-12-23 22:41 ` Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2020-12-23 22:41 UTC (permalink / raw)
To: steve.pousty@gmail.com; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
@ 2020-12-23 23:18 ` Steven Pousty <steve.pousty@gmail.com>
2020-12-24 00:38 ` Re: Trusted versus untrusted Pl language Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Steven Pousty @ 2020-12-23 23:18 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
@ 2020-12-24 00:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-24 00:49 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-12-24 00:38 UTC (permalink / raw)
To: Steven Pousty <steve.pousty@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
2020-12-24 00:38 ` Re: Trusted versus untrusted Pl language Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-12-24 00:49 ` Bruce Momjian <bruce@momjian.us>
2020-12-24 20:01 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2020-12-24 00:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Steven Pousty <steve.pousty@gmail.com>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
2020-12-24 00:38 ` Re: Trusted versus untrusted Pl language Tom Lane <tgl@sss.pgh.pa.us>
2020-12-24 00:49 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
@ 2020-12-24 20:01 ` Steven Pousty <steve.pousty@gmail.com>
2020-12-24 21:26 ` Re: Trusted versus untrusted Pl language David G. Johnston <david.g.johnston@gmail.com>
2020-12-24 21:48 ` Re: Trusted versus untrusted Pl language Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 9+ messages in thread
From: Steven Pousty @ 2020-12-24 20:01 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
2020-12-24 00:38 ` Re: Trusted versus untrusted Pl language Tom Lane <tgl@sss.pgh.pa.us>
2020-12-24 00:49 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-24 20:01 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
@ 2020-12-24 21:26 ` David G. Johnston <david.g.johnston@gmail.com>
2020-12-24 21:35 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2020-12-24 21:26 UTC (permalink / raw)
To: Steven Pousty <steve.pousty@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Tom Lane <tgl@sss.pgh.pa.us>; Pg Docs <pgsql-docs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
2020-12-24 00:38 ` Re: Trusted versus untrusted Pl language Tom Lane <tgl@sss.pgh.pa.us>
2020-12-24 00:49 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-24 20:01 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
2020-12-24 21:26 ` Re: Trusted versus untrusted Pl language David G. Johnston <david.g.johnston@gmail.com>
@ 2020-12-24 21:35 ` Steven Pousty <steve.pousty@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Steven Pousty @ 2020-12-24 21:35 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Tom Lane <tgl@sss.pgh.pa.us>; Pg Docs <pgsql-docs@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Trusted versus untrusted Pl language
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
2020-12-24 00:38 ` Re: Trusted versus untrusted Pl language Tom Lane <tgl@sss.pgh.pa.us>
2020-12-24 00:49 ` Re: Trusted versus untrusted Pl language Bruce Momjian <bruce@momjian.us>
2020-12-24 20:01 ` Re: Trusted versus untrusted Pl language Steven Pousty <steve.pousty@gmail.com>
@ 2020-12-24 21:48 ` Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 9+ messages in thread
From: Stephen Frost @ 2020-12-24 21:48 UTC (permalink / raw)
To: Steven Pousty <steve.pousty@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2020-12-24 21:48 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-23 20:24 Trusted versus untrusted Pl language PG Doc comments form <noreply@postgresql.org>
2020-12-23 22:41 ` Bruce Momjian <bruce@momjian.us>
2020-12-23 23:18 ` Steven Pousty <steve.pousty@gmail.com>
2020-12-24 00:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-24 00:49 ` Bruce Momjian <bruce@momjian.us>
2020-12-24 20:01 ` Steven Pousty <steve.pousty@gmail.com>
2020-12-24 21:26 ` David G. Johnston <david.g.johnston@gmail.com>
2020-12-24 21:35 ` Steven Pousty <steve.pousty@gmail.com>
2020-12-24 21:48 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox