allowing privileges on untrusted languages

Started by Peter Eisentrautabout 13 years ago16 messages

peter_e@gmx.net

about 13 years ago

1 attachment(s)

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.

Attachments:

pg-lanpltrusted.patchtext/x-patch; charset=UTF-8; name=pg-lanpltrusted.patchDownload

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 9144eec..3988de3 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3838,8 +3838,8 @@ <title><structname>pg_language</> Columns</title>
       <entry>
        True if this is a trusted language, which means that it is believed
        not to grant access to anything outside the normal SQL execution
-       environment.  Only superusers can create functions in untrusted
-       languages.
+       environment.  Untrusted languages don't have default privileges
+       assigned.
       </entry>
      </row>
 
diff --git a/doc/src/sgml/ref/create_language.sgml b/doc/src/sgml/ref/create_language.sgml
index 0995b13..891f472 100644
--- a/doc/src/sgml/ref/create_language.sgml
+++ b/doc/src/sgml/ref/create_language.sgml
@@ -121,9 +121,7 @@ <title>Parameters</title>
       <para><literal>TRUSTED</literal> specifies that the language does
        not grant access to data that the user would not otherwise
        have.  If this key word is omitted
-       when registering the language, only users with the
-       <productname>PostgreSQL</productname> superuser privilege can
-       use this language to create new functions.
+       when registering the language, no default privileges are assigned.
       </para>
      </listitem>
     </varlistentry>
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 0bf5356..5c90c68 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -2560,13 +2560,6 @@ static AclMode pg_aclmask(AclObjectKind objkind, Oid table_oid, AttrNumber attnu
 
 		pg_language_tuple = (Form_pg_language) GETSTRUCT(tuple);
 
-		if (!pg_language_tuple->lanpltrusted)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("language \"%s\" is not trusted",
-							NameStr(pg_language_tuple->lanname)),
-				   errhint("Only superusers can use untrusted languages.")));
-
 		/*
 		 * Get owner ID and working copy of existing ACL. If there's no ACL,
 		 * substitute the proper default.
@@ -2576,7 +2569,10 @@ static AclMode pg_aclmask(AclObjectKind objkind, Oid table_oid, AttrNumber attnu
 								   &isNull);
 		if (isNull)
 		{
-			old_acl = acldefault(ACL_OBJECT_LANGUAGE, ownerId);
+			if (pg_language_tuple->lanpltrusted)
+				old_acl = acldefault(ACL_OBJECT_LANGUAGE, ownerId);
+			else
+				old_acl = make_empty_acl();
 			/* There are no old member roles according to the catalogs */
 			noldmembers = 0;
 			oldmembers = NULL;
@@ -3823,7 +3819,10 @@ static AclMode pg_aclmask(AclObjectKind objkind, Oid table_oid, AttrNumber attnu
 	if (isNull)
 	{
 		/* No ACL, so build default ACL */
-		acl = acldefault(ACL_OBJECT_LANGUAGE, ownerId);
+		if (((Form_pg_language) GETSTRUCT(tuple))->lanpltrusted)
+			acl = acldefault(ACL_OBJECT_LANGUAGE, ownerId);
+		else
+			acl = make_empty_acl();
 		aclDatum = (Datum) 0;
 	}
 	else
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index c858511..897e170 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -854,23 +854,11 @@
 	languageOid = HeapTupleGetOid(languageTuple);
 	languageStruct = (Form_pg_language) GETSTRUCT(languageTuple);
 
-	if (languageStruct->lanpltrusted)
-	{
-		/* if trusted language, need USAGE privilege */
-		AclResult	aclresult;
-
-		aclresult = pg_language_aclcheck(languageOid, GetUserId(), ACL_USAGE);
-		if (aclresult != ACLCHECK_OK)
-			aclcheck_error(aclresult, ACL_KIND_LANGUAGE,
-						   NameStr(languageStruct->lanname));
-	}
-	else
-	{
-		/* if untrusted language, must be superuser */
-		if (!superuser())
-			aclcheck_error(ACLCHECK_NO_PRIV, ACL_KIND_LANGUAGE,
-						   NameStr(languageStruct->lanname));
-	}
+	/* need USAGE privilege on language */
+	aclresult = pg_language_aclcheck(languageOid, GetUserId(), ACL_USAGE);
+	if (aclresult != ACLCHECK_OK)
+		aclcheck_error(aclresult, ACL_KIND_LANGUAGE,
+					   NameStr(languageStruct->lanname));
 
 	languageValidator = languageStruct->lanvalidator;
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9b71c75..dfb8305 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8964,11 +8964,10 @@ static void binary_upgrade_extension_member(PQExpBuffer upgrade_buffer,
 				 NULL, "",
 				 plang->dobj.catId, 0, plang->dobj.dumpId);
 
-	if (plang->lanpltrusted)
-		dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
-				qlanname, NULL, plang->dobj.name,
-				lanschema,
-				plang->lanowner, plang->lanacl);
+	dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
+			qlanname, NULL, plang->dobj.name,
+			lanschema,
+			plang->lanowner, plang->lanacl);
 
 	free(qlanname);
 
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index e8930cb..f22dc79 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -457,9 +457,13 @@ SELECT oid FROM atestp2; -- ok
 \c -
 REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
 GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
-GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
-ERROR:  language "c" is not trusted
-HINT:  Only superusers can use untrusted languages.
+GRANT USAGE ON LANGUAGE c TO regressuser1; -- ok
+SELECT has_language_privilege('c', 'USAGE'), has_language_privilege('public', 'c', 'USAGE');
+ has_language_privilege | has_language_privilege 
+------------------------+------------------------
+ t                      | f
+(1 row)
+
 SET SESSION AUTHORIZATION regressuser1;
 GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
 WARNING:  no privileges were granted for "sql"
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index d4d328e..f23cc11 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -300,7 +300,8 @@ CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2);
 
 REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
 GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
-GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
+GRANT USAGE ON LANGUAGE c TO regressuser1; -- ok
+SELECT has_language_privilege('c', 'USAGE'), has_language_privilege('public', 'c', 'USAGE');
 
 SET SESSION AUTHORIZATION regressuser1;
 GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail

Tom Lane

tgl@sss.pgh.pa.us

about 13 years ago

In reply to: Peter Eisentraut (#1)

Re: allowing privileges on untrusted languages

Peter Eisentraut <peter_e@gmx.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Kohei KaiGai

kaigai@kaigai.gr.jp

almost 13 years ago

In reply to: Peter Eisentraut (#1)

Re: allowing privileges on untrusted languages

2013/1/11 Peter Eisentraut <peter_e@gmx.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@kaigai.gr.jp>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Simon Riggs

simon@2ndQuadrant.com

almost 13 years ago

In reply to: Kohei KaiGai (#3)

Re: allowing privileges on untrusted languages

On 19 January 2013 13:45, Kohei KaiGai <kaigai@kaigai.gr.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas

robertmhaas@gmail.com

almost 13 years ago

In reply to: Simon Riggs (#4)

Re: allowing privileges on untrusted languages

On Sat, Jan 19, 2013 at 8:54 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 19 January 2013 13:45, Kohei KaiGai <kaigai@kaigai.gr.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane

tgl@sss.pgh.pa.us

almost 13 years ago

In reply to: Robert Haas (#5)

Re: allowing privileges on untrusted languages

Robert Haas <robertmhaas@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas

robertmhaas@gmail.com

almost 13 years ago

In reply to: Tom Lane (#6)

Re: allowing privileges on untrusted languages

On Sun, Jan 20, 2013 at 10:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dimitri Fontaine

dimitri@2ndQuadrant.fr

almost 13 years ago

In reply to: Tom Lane (#6)

Re: allowing privileges on untrusted languages

Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Kohei KaiGai

kaigai@kaigai.gr.jp

almost 13 years ago

In reply to: Tom Lane (#6)

Re: allowing privileges on untrusted languages

2013/1/20 Tom Lane <tgl@sss.pgh.pa.us>:

Robert Haas <robertmhaas@gmail.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@iwashi ~]$ ls -l /bin/ping
-rwxr-xr-x. 1 root root 40880 Dec 7 15:19 /bin/ping
[kaigai@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@kaigai.gr.jp>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#10

Robert Haas

robertmhaas@gmail.com

almost 13 years ago

In reply to: Kohei KaiGai (#9)

Re: allowing privileges on untrusted languages

On Fri, Jan 25, 2013 at 2:59 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:

2013/1/20 Tom Lane <tgl@sss.pgh.pa.us>:

Robert Haas <robertmhaas@gmail.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@iwashi ~]$ ls -l /bin/ping
-rwxr-xr-x. 1 root root 40880 Dec 7 15:19 /bin/ping
[kaigai@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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#11

Tom Lane

tgl@sss.pgh.pa.us

almost 13 years ago

In reply to: Robert Haas (#10)

Re: allowing privileges on untrusted languages

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Jan 25, 2013 at 2:59 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:

2013/1/20 Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#12

Robert Haas

robertmhaas@gmail.com

almost 13 years ago

In reply to: Tom Lane (#11)

Re: allowing privileges on untrusted languages

On Sun, Jan 27, 2013 at 1:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Jan 25, 2013 at 2:59 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:

2013/1/20 Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#13

Craig Ringer

craig@2ndQuadrant.com

almost 13 years ago

In reply to: Robert Haas (#12)

Re: allowing privileges on untrusted languages

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

#14

Robert Haas

robertmhaas@gmail.com

almost 13 years ago

In reply to: Craig Ringer (#13)

Re: allowing privileges on untrusted languages

On Sun, Jan 27, 2013 at 11:15 PM, Craig Ringer <craig@2ndquadrant.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#15

Peter Eisentraut

peter_e@gmx.net

almost 13 years ago

In reply to: Tom Lane (#2)

Re: allowing privileges on untrusted languages

On 1/11/13 10:25 AM, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#16

Peter Eisentraut

peter_e@gmx.net

almost 13 years ago

In reply to: Kohei KaiGai (#3)

Re: allowing privileges on untrusted languages

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers