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