Re: allowing privileges on untrusted languages

Here is a proposed patch for the issue discussed in
<http://archives.postgresql.org/pgsql-hackers/2012-07/msg00569.php>:

I'd propose getting rid of lanplistrusted, at least for access
checking. Instead, just don't install USAGE privileges by
default for those languages.

The reason is that there is value in having a role that can
deploy
schemas, possibly containing functions in untrusted languages,
without having to be a full superuser. Just like you can have a
user that can create roles without being a superuser.

It turned out that actually getting rid of lanpltrusted would be too
invasive, especially because some language handlers use it to determine
their own behavior.

So instead the lanpltrusted attribute now just determined what the
default privileges of the language are, and all the checks the require
superuserness to do anything with untrusted languages are removed.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> It turned out that actually getting rid of lanpltrusted would be too
> invasive, especially because some language handlers use it to determine
> their own behavior.

> So instead the lanpltrusted attribute now just determined what the
> default privileges of the language are, and all the checks the require
> superuserness to do anything with untrusted languages are removed.

Hmm ... that worries me a bit. It seems like system security will now
require being sure that the permissions on the language match the
lanpltrusted setting. Even if the code is right today, there's a lot
of scope for future oversights with security implications. Don't know
what we could do to mitigate that.

In particular, have you thought carefully about upgrade scenarios?
Will a dump-and-restore of a pre-9.3 installation end up with safe
language privileges?

In the same vein, I'm worried that the proposed change in pg_dump will
do the wrong thing when looking at a pre-9.3 server. Is any
server-version-dependent behavior needed there?

regards, tom lane

2013/1/11 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> Here is a proposed patch for the issue discussed in
> <http://archives.postgresql.org/pgsql-hackers/2012-07/msg00569.php>:
>
> I'd propose getting rid of lanplistrusted, at least for access
> checking. Instead, just don't install USAGE privileges by
> default for those languages.
>
> The reason is that there is value in having a role that can
> deploy
> schemas, possibly containing functions in untrusted languages,
> without having to be a full superuser. Just like you can have a
> user that can create roles without being a superuser.
>
> It turned out that actually getting rid of lanpltrusted would be too
> invasive, especially because some language handlers use it to determine
> their own behavior.
>
> So instead the lanpltrusted attribute now just determined what the
> default privileges of the language are, and all the checks the require
> superuserness to do anything with untrusted languages are removed.
>
Even though I could understand your motivation, I'm worrying about this
change makes system security uncontrollable. For example, it allows
an unprivileged users to grant usage permission of untrusted language
for another one or public, without any controls by superuser.

I think, it is a time to investigate separation of database superuser privileges
into several fine-grained capabilities, like as operating system doing.
https://github.com/torvalds/linux/blob/master/include/uapi/linux/capability.h

In case of Linux, the latest kernel has 36 kinds of capabilities that reflects
a part of root privileges, such as privilege to open listen port less than 1024,
privilege to override DAC permission and so on. Traditional root performs
as a user who has all the capability in default.

Does it make sense for your efforts? Probably, these three may match with
what you focus on, as a first step.
* privilege to read/write local filesystem
* privilege to use untrusted language
* other privileges; that does not match above ones.

How about your opinion?

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

On 19 January 2013 13:45, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:

> I think, it is a time to investigate separation of database superuser privileges
> into several fine-grained capabilities, like as operating system doing.
> https://github.com/torvalds/linux/blob/master/include/uapi/linux/capability.h
>
> In case of Linux, the latest kernel has 36 kinds of capabilities that reflects
> a part of root privileges, such as privilege to open listen port less than 1024,
> privilege to override DAC permission and so on. Traditional root performs
> as a user who has all the capability in default.

Sounds like the best way to go. The reasoning that led to that change
works for us as well.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sat, Jan 19, 2013 at 8:54 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 19 January 2013 13:45, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>> I think, it is a time to investigate separation of database superuser privileges
>> into several fine-grained capabilities, like as operating system doing.
>> https://github.com/torvalds/linux/blob/master/include/uapi/linux/capability.h
>>
>> In case of Linux, the latest kernel has 36 kinds of capabilities that reflects
>> a part of root privileges, such as privilege to open listen port less than 1024,
>> privilege to override DAC permission and so on. Traditional root performs
>> as a user who has all the capability in default.
>
> Sounds like the best way to go. The reasoning that led to that change
> works for us as well.

Yeah. We'd need to think a little bit about how to make this work,
since I think that adding a gajillion booleans to pg_authid will not
make anyone very happy. But I like the idea. GRANT
kill_sessions_of_other_users TO bob? GRANT install_untrusted_pls TO
any_database_owner? GRANT install_an_extension_called(hstore) TO
any_database_owner? I know there are other ways of doing all of these
things, so don't take the specific proposals too seriously, but we
clearly have a need to parcel out controlled bits of the superuser
mojo to individual users in a nice, clean, convenient way. Getting
agreement on the details is likely to be difficult, but it seems like
a good concept from 10,000 feet.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Yeah. We'd need to think a little bit about how to make this work,
> since I think that adding a gajillion booleans to pg_authid will not
> make anyone very happy. But I like the idea. GRANT
> kill_sessions_of_other_users TO bob? GRANT install_untrusted_pls TO
> any_database_owner? GRANT install_an_extension_called(hstore) TO
> any_database_owner? I know there are other ways of doing all of these
> things, so don't take the specific proposals too seriously, but we
> clearly have a need to parcel out controlled bits of the superuser
> mojo to individual users in a nice, clean, convenient way. Getting
> agreement on the details is likely to be difficult, but it seems like
> a good concept from 10,000 feet.

The traditional answer to that, which not only can be done already in
all existing releases but is infinitely more flexible than any
hard-wired scheme we could implement, is that you create superuser-owned
security-definer functions that can execute any specific operation you
want to allow, and then GRANT EXECUTE on those functions to just the
people who should have it.

I'm really entirely un-thrilled with a proposal to clutter the privilege
system like this. Admittedly, it might be a hair more secure than
user-written plpgsql functions, which could perhaps be subverted if the
author is careless. But there are a hundred other places where we could
more usefully spend our implementation and future-maintenance efforts
than here.

regards, tom lane

On Sun, Jan 20, 2013 at 10:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Yeah. We'd need to think a little bit about how to make this work,
>> since I think that adding a gajillion booleans to pg_authid will not
>> make anyone very happy. But I like the idea. GRANT
>> kill_sessions_of_other_users TO bob? GRANT install_untrusted_pls TO
>> any_database_owner? GRANT install_an_extension_called(hstore) TO
>> any_database_owner? I know there are other ways of doing all of these
>> things, so don't take the specific proposals too seriously, but we
>> clearly have a need to parcel out controlled bits of the superuser
>> mojo to individual users in a nice, clean, convenient way. Getting
>> agreement on the details is likely to be difficult, but it seems like
>> a good concept from 10,000 feet.
>
> The traditional answer to that, which not only can be done already in
> all existing releases but is infinitely more flexible than any
> hard-wired scheme we could implement, is that you create superuser-owned
> security-definer functions that can execute any specific operation you
> want to allow, and then GRANT EXECUTE on those functions to just the
> people who should have it.
>
> I'm really entirely un-thrilled with a proposal to clutter the privilege
> system like this. Admittedly, it might be a hair more secure than
> user-written plpgsql functions, which could perhaps be subverted if the
> author is careless. But there are a hundred other places where we could
> more usefully spend our implementation and future-maintenance efforts
> than here.

It's not terribly personally important to me, either ... but it's
important enough to other people here that I'm pretty sure we will see
future patches aiming at this target. Extensions to event triggers,
inter alia. Even had I the power, I'm not prepared to reject all of
those things out of hand, so I think it would behoove us to think
about by what means we want to enable these sorts of things rather
than whether we want to enable them.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> The traditional answer to that, which not only can be done already in
> all existing releases but is infinitely more flexible than any
> hard-wired scheme we could implement, is that you create superuser-owned
> security-definer functions that can execute any specific operation you
> want to allow, and then GRANT EXECUTE on those functions to just the
> people who should have it.

The next step after that might well be INSTEAD OF event triggers,
allowing users to reimplement some DDL in terms of themselves in PL
code, with the ALTER EVENT TRIGGER … DISABLE; trick to avoid recursion.

> I'm really entirely un-thrilled with a proposal to clutter the privilege
> system like this. Admittedly, it might be a hair more secure than
> user-written plpgsql functions, which could perhaps be subverted if the
> author is careless. But there are a hundred other places where we could
> more usefully spend our implementation and future-maintenance efforts
> than here.

My understanding is that the security minds are shifting towards
capabilities as the way to reduce maintenance efforts and security bug
fixes in the long run:

http://www.erights.org/

Again, as I'm not into security that much, so I will refrain from
commenting any further.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2013/1/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Yeah. We'd need to think a little bit about how to make this work,
>> since I think that adding a gajillion booleans to pg_authid will not
>> make anyone very happy. But I like the idea. GRANT
>> kill_sessions_of_other_users TO bob? GRANT install_untrusted_pls TO
>> any_database_owner? GRANT install_an_extension_called(hstore) TO
>> any_database_owner? I know there are other ways of doing all of these
>> things, so don't take the specific proposals too seriously, but we
>> clearly have a need to parcel out controlled bits of the superuser
>> mojo to individual users in a nice, clean, convenient way. Getting
>> agreement on the details is likely to be difficult, but it seems like
>> a good concept from 10,000 feet.
>
> The traditional answer to that, which not only can be done already in
> all existing releases but is infinitely more flexible than any
> hard-wired scheme we could implement, is that you create superuser-owned
> security-definer functions that can execute any specific operation you
> want to allow, and then GRANT EXECUTE on those functions to just the
> people who should have it.
>
I also agree it is a right solution to provide unprivileged users a trusted
way to handle privileged operations, as set-uid programs are widely
utilized for same purpose on operating system, however, it needs to
satisfy an assumption these trusted procedures have to be free from
bugs and vulnerabilities. In general, it is not an easy assumption.
Thus, it is the reason why fine-grained capability is preferred than
root as single-point-of-failure on security. For example, now /bin/ping
has no set-uid bit on recent Fedora system, to avoid a risk when this
program has any vulnerability.

[kaigai(at)iwashi ~]$ ls -l /bin/ping
-rwxr-xr-x. 1 root root 40880 Dec 7 15:19 /bin/ping
[kaigai(at)iwashi ~]$ getcap /bin/ping
/bin/ping = cap_net_admin,cap_net_raw+ep

I think, our situation is similar. If DBA is not 100% certain on safeness
of self-defined functions, I believe he want unprivileged users to call
security-definer functions owned by limited capable user, instead of
fully capable superuser.

I'm not positive to over-interpret this concept, like hundred of capabilities
being nightmare of maintenance. However, it is worthwhile as basis
of self-defined security-definer functions.
I'd like people to imagine a case when trusted procedures were buggy.

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

On Fri, Jan 25, 2013 at 2:59 PM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
> 2013/1/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> Yeah. We'd need to think a little bit about how to make this work,
>>> since I think that adding a gajillion booleans to pg_authid will not
>>> make anyone very happy. But I like the idea. GRANT
>>> kill_sessions_of_other_users TO bob? GRANT install_untrusted_pls TO
>>> any_database_owner? GRANT install_an_extension_called(hstore) TO
>>> any_database_owner? I know there are other ways of doing all of these
>>> things, so don't take the specific proposals too seriously, but we
>>> clearly have a need to parcel out controlled bits of the superuser
>>> mojo to individual users in a nice, clean, convenient way. Getting
>>> agreement on the details is likely to be difficult, but it seems like
>>> a good concept from 10,000 feet.
>>
>> The traditional answer to that, which not only can be done already in
>> all existing releases but is infinitely more flexible than any
>> hard-wired scheme we could implement, is that you create superuser-owned
>> security-definer functions that can execute any specific operation you
>> want to allow, and then GRANT EXECUTE on those functions to just the
>> people who should have it.
>>
> I also agree it is a right solution to provide unprivileged users a trusted
> way to handle privileged operations, as set-uid programs are widely
> utilized for same purpose on operating system, however, it needs to
> satisfy an assumption these trusted procedures have to be free from
> bugs and vulnerabilities. In general, it is not an easy assumption.
> Thus, it is the reason why fine-grained capability is preferred than
> root as single-point-of-failure on security. For example, now /bin/ping
> has no set-uid bit on recent Fedora system, to avoid a risk when this
> program has any vulnerability.
>
> [kaigai(at)iwashi ~]$ ls -l /bin/ping
> -rwxr-xr-x. 1 root root 40880 Dec 7 15:19 /bin/ping
> [kaigai(at)iwashi ~]$ getcap /bin/ping
> /bin/ping = cap_net_admin,cap_net_raw+ep
>
> I think, our situation is similar. If DBA is not 100% certain on safeness
> of self-defined functions, I believe he want unprivileged users to call
> security-definer functions owned by limited capable user, instead of
> fully capable superuser.
>
> I'm not positive to over-interpret this concept, like hundred of capabilities
> being nightmare of maintenance. However, it is worthwhile as basis
> of self-defined security-definer functions.
> I'd like people to imagine a case when trusted procedures were buggy.

This is valid, but I think that the people who want this functionality
are less interest in avoiding bugs in trusted procedures than they are
in avoiding the necessity for the user to have to learn the local
admin-installed collection of trusted procedures. In other words, the
value in being able to run CREATE EXTENSION on_the_whitelist rather
than SELECT my_secdef_create_extension('on_the_whitelist') is that the
user is presumed to be already familiar with the former. I can see
the logic in that, though it does pose some implementation challenges.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jan 25, 2013 at 2:59 PM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>> 2013/1/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> The traditional answer to that, which not only can be done already in
>>> all existing releases but is infinitely more flexible than any
>>> hard-wired scheme we could implement, is that you create superuser-owned
>>> security-definer functions that can execute any specific operation you
>>> want to allow, and then GRANT EXECUTE on those functions to just the
>>> people who should have it.

> This is valid, but I think that the people who want this functionality
> are less interest in avoiding bugs in trusted procedures than they are
> in avoiding the necessity for the user to have to learn the local
> admin-installed collection of trusted procedures.

Sure, but given that we are working on event triggers, surely the
correct solution is to make sure that user-provided event triggers can
cover permissions-checking requirements, rather than to invent a whole
new infrastructure that's guaranteed to never really satisfy anybody.

regards, tom lane

On Sun, Jan 27, 2013 at 1:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Jan 25, 2013 at 2:59 PM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>>> 2013/1/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>>> The traditional answer to that, which not only can be done already in
>>>> all existing releases but is infinitely more flexible than any
>>>> hard-wired scheme we could implement, is that you create superuser-owned
>>>> security-definer functions that can execute any specific operation you
>>>> want to allow, and then GRANT EXECUTE on those functions to just the
>>>> people who should have it.
>
>> This is valid, but I think that the people who want this functionality
>> are less interest in avoiding bugs in trusted procedures than they are
>> in avoiding the necessity for the user to have to learn the local
>> admin-installed collection of trusted procedures.
>
> Sure, but given that we are working on event triggers, surely the
> correct solution is to make sure that user-provided event triggers can
> cover permissions-checking requirements, rather than to invent a whole
> new infrastructure that's guaranteed to never really satisfy anybody.

I am not sure whether it's really true that a capability mechanism
could "never really satisfy" anyone. It worked for Linux.

But, I think event triggers are a credible answer, too, and they
certainly are more flexible.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/28/2013 02:15 AM, Robert Haas wrote:
>
> I am not sure whether it's really true that a capability mechanism
> could "never really satisfy" anyone. It worked for Linux.
I have no concern about using a capabilities approach for this, but I
don't think Linux is a great example here. Linux's capabilities have
been defined in a somewhat ad-hoc fashion and a huge amount of stuff is
bundled into CAP_SYS_ADMIN. Several capabilities provide escalation
routes to root / CAP_SYS_ADMIN. See:

https://lwn.net/Articles/486306/
http://dl.packetstormsecurity.net/papers/attack/exploiting_capabilities_the_dark_side.pdf

There's nothing wrong with capability systems, it's just clear that they
need to be designed, documented and maintained carefully. Adding ad-hoc
capbilities is exactly the wrong route to take, and will lead us into
the same mess Linux is in now.
> But, I think event triggers are a credible answer, too, and they
> certainly are more flexible.
Yes, but with the caveat that leaving security design to user triggers
will provide users with more opportunities for error - failure to think
about schemas and search_path, testing role membership via some
hacked-together queries instead of the built-in system information
functions, failure to consider SECURITY DEFINER and the effect of
session_user vs current_user, etc. Some docs on writing security
triggers and some standard triggers in an extension module would go a
long way to mitigating that, though. The appeal of the trigger based
approach is that it means core doesn't land up needing
CAP_CAN_EXECUTE_PLPERLU_ON_TUESDAYS_AFTER_MIDDAY_ON_A_FULL_MOON_IN_A_LEAPYEAR.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sun, Jan 27, 2013 at 11:15 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 01/28/2013 02:15 AM, Robert Haas wrote:
>
> I am not sure whether it's really true that a capability mechanism
> could "never really satisfy" anyone. It worked for Linux.
>
> I have no concern about using a capabilities approach for this, but I don't
> think Linux is a great example here. Linux's capabilities have been defined
> in a somewhat ad-hoc fashion and a huge amount of stuff is bundled into
> CAP_SYS_ADMIN. Several capabilities provide escalation routes to root /
> CAP_SYS_ADMIN. See:
>
> https://lwn.net/Articles/486306/
> http://dl.packetstormsecurity.net/papers/attack/exploiting_capabilities_the_dark_side.pdf
>
> There's nothing wrong with capability systems, it's just clear that they
> need to be designed, documented and maintained carefully. Adding ad-hoc
> capbilities is exactly the wrong route to take, and will lead us into the
> same mess Linux is in now.
>
> But, I think event triggers are a credible answer, too, and they
> certainly are more flexible.
>
> Yes, but with the caveat that leaving security design to user triggers will
> provide users with more opportunities for error - failure to think about
> schemas and search_path, testing role membership via some hacked-together
> queries instead of the built-in system information functions, failure to
> consider SECURITY DEFINER and the effect of session_user vs current_user,
> etc. Some docs on writing security triggers and some standard triggers in an
> extension module would go a long way to mitigating that, though. The appeal
> of the trigger based approach is that it means core doesn't land up needing
> CAP_CAN_EXECUTE_PLPERLU_ON_TUESDAYS_AFTER_MIDDAY_ON_A_FULL_MOON_IN_A_LEAPYEAR.

+1 to the entire email, and well said.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 1/11/13 10:25 AM, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> It turned out that actually getting rid of lanpltrusted would be too
>> invasive, especially because some language handlers use it to determine
>> their own behavior.
>
>> So instead the lanpltrusted attribute now just determined what the
>> default privileges of the language are, and all the checks the require
>> superuserness to do anything with untrusted languages are removed.
>
> Hmm ... that worries me a bit. It seems like system security will now
> require being sure that the permissions on the language match the
> lanpltrusted setting. Even if the code is right today, there's a lot
> of scope for future oversights with security implications. Don't know
> what we could do to mitigate that.

I think altogether this patch does not introduce any more reasons "to be
careful" then any other security-related patch. The ACL stuff is
already spread out over too many places, and you could argue that this
patch reduces some of that surface area.

> In particular, have you thought carefully about upgrade scenarios?
> Will a dump-and-restore of a pre-9.3 installation end up with safe
> language privileges?

Untrusted languages in pre-9.3 installations cannot have any privileges,
because GRANT denies that. If you grant some anyway (e.g., set the
trusted bit, grant, re-remove trusted bit), then, well, you get what you
asked for, expect now it actually works.

> In the same vein, I'm worried that the proposed change in pg_dump will
> do the wrong thing when looking at a pre-9.3 server. Is any
> server-version-dependent behavior needed there?

That shouldn't be a problem for the same reasons.

What might actually be a problem in this area is that, AFAICT, pg_dump
does not save privileges granted to objects in extensions.

On 1/19/13 8:45 AM, Kohei KaiGai wrote:
> I think, it is a time to investigate separation of database superuser privileges
> into several fine-grained capabilities, like as operating system doing.
> https://github.com/torvalds/linux/blob/master/include/uapi/linux/capability.h

The Linux capabilities system exists because there is no normal file
system object to attach the privileges to. If there were
/dev/somethings for all of these things, there would not no need for the
capabilities thing.

In this case, the privileges system already exists. We just need to use it.