Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[Nico Williams]

A bit more about why I want this.

Suppose you have an app like PostgREST (a RESTful, Haskell-coded, HTTP
front-end for PostgreSQL).  PostgREST basically a proxy for PG access.

Users authenticate to the proxy.  The proxy authenticates to PG with its
own credentials, then it does something like SET ROLE or SET SESSION
AUTHORIZATION to impersonate the user to PG.

Now suppose you want to support impersonation in such a proxy.  That is,
that user "Joe" can impersonate "Jane", for example.  So what you do is
you have the proxy do this:

  -- This is the role authenticated to the proxy:
  SET SESSION AUTHORIZATION ;

  -- This is the requested impersonation, and succeeds only if the first
  -- role has been GRANTed the second:
  SET SESSION ROLE ;

Convenient!

Now, if you want to... audit what Joe does, you may want to record both,
the session_user (Joe) and the current_user (Jane) as you write the
audit trail.  Naturally, that's desirable, and it works...

...unless the audit procedures are SECURITY DEFINER.  Then they don't
see the current_user.  They see the session_user, but can't see who the
session user was impersonating.

Hence I want to be able to look back through the [security definer]
function invocation stack to find what current_user was at the
top-level, or even just the calling function's current_user.

Now, since this is a proxy, a workaround is to store the impersonated
role name in an application defined GUC.  But if ever you wanted to give
users direct PG access (one might! it should be possible), then that's
not enough because they can set that GUC.  So it really has to be that
the audit procedures can look up the stack.

(When you give out direct PG access you really want to make those audit
procedures SECURITY DEFINER, so they can do DMLs on tables that the
session_user can't.)

This isn't urgent _for me_, but it is a real problem.

Nico
-- 


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

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[Nico Williams]

On Wed, Oct 18, 2017 at 02:45:47PM -0700, David G. Johnston wrote:
> On Wed, Oct 18, 2017 at 2:30 PM, Nico Williams 
> wrote:
> > On Wed, Oct 18, 2017 at 02:13:29PM -0700, David G. Johnston wrote:
> > > > More useful than this, for me, would be a way to get the top-most user.
> > >
> > > That would be "session_user"?
> >
> > It's not quite since there's a difference between SET SESSION
> > AUTHORIZATION and SET SESSION ROLE.
> >
> > But yes, it's what I'm using now.
> 
> True, though at that point the superuser who wants to cover their tracks
> could probably just edit your functions...

I don't worry about superusers.

However, I'd like for there to be a way to drop privileges permanently
for a session.  Something like SET SESSION AUTHORIZATION WITH NORESET
(ala MySQL) or SET SESSION AUTHENTICATION.

> > Hmmm, oh, I forgot about GET DIAGNOSTICS!  The stack is already exposed
> > to SQL.  Maybe we could add a CURRENT_USER item to GET STACKED
> > DIAGNOSTICS or to the PG_CONTEXT.
> 
> Ideally if implementing what you describe we'd want it accessible from any
> procedural language​, not just pl/pgsql.

Good point.  So a function.  Got it.

> I'd probably expose the stack as an array...

I agree, but that would be more expensive, since it means marshalling
all the information, even if the caller only wants one specific item.
Whereas accessing a specific frame by number is much simpler and
performant (no allocation).

It's also easier to not have to do something like.. parsing than the
PG_CONTEXT, instead accessing each of any number of attributes we might
expose from each frame.

Nico
-- 


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

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[David G. Johnston]

On Wed, Oct 18, 2017 at 2:30 PM, Nico Williams 
wrote:

> On Wed, Oct 18, 2017 at 02:13:29PM -0700, David G. Johnston wrote:
> > > More useful than this, for me, would be a way to get the top-most user.
> >
> > That would be "session_user"?
>
> It's not quite since there's a difference between SET SESSION
> AUTHORIZATION and SET SESSION ROLE.
>
> But yes, it's what I'm using now.
>

​True, though at that point the superuser who wants to cover their tracks
could probably just edit your functions...​


> Really?  Why?  I mean, there's an implicit function invocation stack
> already.  Reifying some bits of the function call stack is useful.  I
> can't think of how this particular reification would be dangerous or set
> a bad precedent.
>

​Nothing concrete...​
​

>
> Hmmm, oh, I forgot about GET DIAGNOSTICS!  The stack is already exposed
> to SQL.  Maybe we could add a CURRENT_USER item to GET STACKED
> DIAGNOSTICS or to the PG_CONTEXT.
>

Ideally if implementing what you describe we'd want it accessible from any
procedural language​, not just pl/pgsql.

Also, GET STACKED DIAGNOSTICS is documented as being exposed only within an
exception handler.


> > If I was in position to dive deeper I wouldn't foreclose on the stack
> idea
> > but I'd be inclined to see if something else could be made to work with
> > reasonable effort.
>
> I would think that the more general approach, if easy enough to
> implement, would be better.  I can (and will) live with using
> session_user instead of current_user, for now.  But I'm willing to
> contribute a patch


​I'd probably expose the stack as an array...

David J.
​

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[Nico Williams]

On Wed, Oct 18, 2017 at 02:13:29PM -0700, David G. Johnston wrote:
> > More useful than this, for me, would be a way to get the top-most user.
> 
> That would be "session_user"?

It's not quite since there's a difference between SET SESSION
AUTHORIZATION and SET SESSION ROLE.

But yes, it's what I'm using now.

> > Introducing the concept of a stack at the SQL level here seems, at
> > > first glance, to be over-complicating things.
> >
> > Because of the current implementation of invocation of SECURITY DEFINER
> > functions, a stack is trivial to build, since it's a list of nodes
> > allocated on the C stack in fmgr_security_definer().
> 
> Not saying its difficult (or not) to code in C; but exposing that to SQL
> seems like a big step.

Really?  Why?  I mean, there's an implicit function invocation stack
already.  Reifying some bits of the function call stack is useful.  I
can't think of how this particular reification would be dangerous or set
a bad precedent.

Hmmm, oh, I forgot about GET DIAGNOSTICS!  The stack is already exposed
to SQL.  Maybe we could add a CURRENT_USER item to GET STACKED
DIAGNOSTICS or to the PG_CONTEXT.

> If I was in position to dive deeper I wouldn't foreclose on the stack idea
> but I'd be inclined to see if something else could be made to work with
> reasonable effort.

I would think that the more general approach, if easy enough to
implement, would be better.  I can (and will) live with using
session_user instead of current_user, for now.  But I'm willing to
contribute a patch.

Nico
-- 


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

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[David G. Johnston]

On Wed, Oct 18, 2017 at 2:08 PM, Nico Williams 
wrote:

> On Wed, Oct 18, 2017 at 01:43:30PM -0700, David G. Johnston wrote:
>
> More useful than this, for me, would be a way to get the top-most user.
>
>
​That would be "session_user"?​

> Introducing the concept of a stack at the SQL level here seems, at
> > first glance, to be over-complicating things.
>
> Because of the current implementation of invocation of SECURITY DEFINER
> functions, a stack is trivial to build, since it's a list of nodes
> allocated on the C stack in fmgr_security_definer().
>

​Not saying its difficult (or not) to code in C; but exposing that to SQL
seems like a big step.

If I was in position to dive deeper I wouldn't foreclose on the stack idea
but I'd be inclined to see if something else could be made to work with
reasonable effort.

David J.​

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[Nico Williams]

On Wed, Oct 18, 2017 at 01:43:30PM -0700, David G. Johnston wrote:
> Regardless of the merits of the proposed feature, the function
> "session_user" is SQL-defined and should not be modified or enhanced.
> 
> I could see "calling_role()" being useful - it returns the same value
> as "current_role" normally and in security invoker functions while in
> a security definer function it would return whatever current_role
> would have returned if the function was a security invoker (i.e., the
> role that the system will put back into effect once the security
> definer function returns).

That... could be awkward where lots of SECURITY DEFINER functions may be
user-callable, but also called from each other.  But it would be
minimally useful.

More useful than this, for me, would be a way to get the top-most user.

> Introducing the concept of a stack at the SQL level here seems, at
> first glance, to be over-complicating things.

Because of the current implementation of invocation of SECURITY DEFINER
functions, a stack is trivial to build, since it's a list of nodes
allocated on the C stack in fmgr_security_definer().

Nico
-- 


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

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[David G. Johnston]

On Wed, Oct 18, 2017 at 1:26 PM, Nico Williams 
wrote:

> On Wed, Oct 18, 2017 at 10:15:01PM +0200, Pavel Stehule wrote:
> > there is a function session_user() already
>
> But it doesn't do this.  Are you saying that I should add a
> session_user(int)?
>
>
​Regardless of the merits of the proposed feature, the function
"session_user" is SQL-defined and should not be modified or enhanced.

I could see "calling_role()" being useful - it returns the same value as
"current_role" normally and in security invoker functions while in a
security definer function it would return whatever current_role would have
returned if the function was a security invoker (i.e., the role that the
system will put back into effect once the security definer function
returns).

Introducing the concept of a stack at the SQL level here seems, at first
glance, to be over-complicating things.

David J.

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[Nico Williams]

On Wed, Oct 18, 2017 at 10:15:01PM +0200, Pavel Stehule wrote:
> there is a function session_user() already

But it doesn't do this.  Are you saying that I should add a
session_user(int)?

Nico
-- 


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

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[Pavel Stehule]

2017-10-18 22:01 GMT+02:00 Nico Williams :

> It'd be nice if SECURITY DEFINER functions could see what user invoked
> them, but current_user is the DEFINER user, naturally, since that's how
> this is done in fmgr_security_definer().
>
> I was thinking that fmgr_security_definer() could keep a global pointer
> to a linked list (with automatic nodes) of the save_userid values.  Then
> we could have a SQL function for accessing these, something like
> pg_current_user(level int) returning text, where level 0 is
> current_user, level 1 is "the previous current_user in the stack", and
> so on, returning null when level is beyond the top-level.
>
> This seems like a simple, small, easy patch, and since I [think I] need
> it I suspect others probably do as well.
>
> Thoughts?
>

there is a function session_user() already

regards

Pavel


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

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

[Nico Williams]

Alternatively, a way to get at the OuterUserId?  Or the outer-most
current_user in the function stack?

I should explain why I need this: for audit functionality where I want
the triggers' procedures to be SECURITY DEFINER so only they can write
to audit tables and such, but I want them to see the current_user of the
*caller*, rather than current_user being the DEFINER's name.

Nico
-- 


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