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
