Why security-definer functions are executable by public by default?

On 04/05/2011 09:41 AM, hubert depesz lubaczewski wrote:

hi

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

depesz

That is exactly the point of security definer. It means that even though
you do not have rights to data, I have a special function that will
allow you the rights in a very specific way.

For example, I give my users no rights on any tables. The only way they
can access data is through views and security definer functions. The
functions are built in such a way that it only allows them access in the
manner that I want them to.

So while my user cannot insert into the table, he can pass the correct
parameters into the function and if everything checks out write it will
insert the row.

Sim

hubert depesz lubaczewski <depesz@depesz.com> writes:

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

I don't see that that follows, at all. The entire point of a security
definer function is to provide access to some restricted resource to
users who couldn't get at it with their own privileges. Having it start
with no privileges would be quite useless.

regards, tom lane

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

you can create function in schema accesible to dba only.

------------
pasman

On Tue, Apr 5, 2011 at 3:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

hubert depesz lubaczewski <depesz@depesz.com> writes:

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

I don't see that that follows, at all.  The entire point of a security
definer function is to provide access to some restricted resource to
users who couldn't get at it with their own privileges.  Having it start
with no privileges would be quite useless.

Agreed.

If somebody is creating a security definer function then they are
explicitly relaxing security. It's a little hard for people doing that
to say that they were not aware of security and forgot to issue GRANTs
to carefully define who got the new capability.

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

On Wed, Apr 06, 2011 at 09:06:50AM +0200, pasman pasmański wrote:

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

you can create function in schema accesible to dba only.

sure. and I can revoke the privileges and grant the ones I need.

I know I can *fix* it. But I just think that the default should be the
same as with untrusted languages.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

On 04/06/2011 07:41 AM, hubert depesz lubaczewski wrote:

On Wed, Apr 06, 2011 at 09:06:50AM +0200, pasman pasmański wrote:

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

you can create function in schema accesible to dba only.

sure. and I can revoke the privileges and grant the ones I need.

I know I can *fix* it. But I just think that the default should be the
same as with untrusted languages.

As was said earlier the point of SECURITY DEFINER is to allow
non-privileged users access to privileged content. When a trusted
function is created it is has the default of SECURITY INVOKER that
restricts it privileges to that of the calling user. Switching to
SECURITY DEFINER is a dba decision on grants already.

depesz

--
Adrian Klaver
adrian.klaver@gmail.com

On Tue, Apr 05, 2011 at 08:41:21AM +0200, hubert depesz lubaczewski wrote:

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

That default applies to untrusted-language functions as well, and I don't think
individual languages can override it.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

Agreed. The SECURITY DEFINER property would remain superfluous until you GRANT
the function to a suitable audience, but that seems preferable to presuming that
the universal audience is suitable. In other words, I'd rather have the user
who hasn't thought this through get permission failures until he does. Likewise
for functions implemented in untrusted languages.

At least, that's what I'd prefer for a greenfield.

nm

Tom Lane wrote:

hubert depesz lubaczewski <depesz@depesz.com> writes:

was pointed to the fact that security definer functions have the same
default privileges as normal functions in the same language - i.e. if
the language is trusted - public has the right to execute them.

maybe i'm missing something important, but given the fact that security
definer functions are used to get access to things that you usually
don't have access to - shouldn't the privilege be revoked by default,
and grants left for dba to decide?

I don't see that that follows, at all. The entire point of a security
definer function is to provide access to some restricted resource to
users who couldn't get at it with their own privileges. Having it start
with no privileges would be quite useless.

Sorry for the late reply, but isn't this exactly what we do when we
create schemas? We create them with owner-only permissions because it
closes a window of vunlerability if somone creates the schema and then
tries to lock it down later. Is the security-definer function a similar
case that should start as owner-only?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +