Re: SYSTEM_USER reserved word implementation

On 6/22/22 10:51, Tom Lane wrote:
> My immediate guess would be that the SQL committee only intends
> to deal in SQL role names and therefore SYSTEM_USER is defined
> to return one of those, but I've not gone looking in the spec
> to be sure.

I only have a draft copy, but in SQL 2016 I find relatively thin 
documentation for what SYSTEM_USER is supposed to represent:

   The value specified by SYSTEM_USER is equal to an
   implementation-defined string that represents the
   operating system user who executed the SQL-client
   module that contains the externally-invoked procedure
   whose execution caused the SYSTEM_USER <general value
   specification> to be evaluated.

> I'm also not that clear on what we expect authn_id to be, but
> a quick troll in the code makes it look like it's not necessarily
> a SQL role name, but might be some external identifier such as a
> Kerberos principal.  If that's the case I think it's going to be
> inappropriate to use SQL-spec syntax to return it.  I don't object
> to inventing some PG-specific function for the purpose, though.

To me the Kerberos principal makes perfect sense given the definition above.

> BTW, are there any security concerns about exposing such identifiers?

On the contrary, I would argue that not having the identifier for the 
external "user" available is a security concern. Ideally you want to be 
able to trace actions inside Postgres to the actual user that invoked them.

-- 
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com