Re: Feature request(?): Proxy User

czw., 28 lip 2022 o 14:18 Mateusz Henicz <mateuszhenicz(at)gmail(dot)com>
napisał(a):

> Hi,
> Did you check "SET SESSION AUTHORIZATION" or "SET ROLE" commands?
> I think that is what you are looking for.
>
> Cheers,
> Mateusz
>
> czw., 28 lip 2022 o 11:29 Wiwwo Staff <wiwwo(at)wiwwo(dot)com> napisał(a):
>
>> Sorry to post solutions and links about alternative DBMSs, but I miss
>> this sort of "sudo" ad database user level:
>> https://oracle-base.com/articles/misc/proxy-users-and-connect-through
>>
>> Is there any approach to achieve the same result in Postgresql?
>> Anyone sharing the need of this functionality?
>>
>> Tnx!
>>
>>
Can you elaborate what exact problem are you trying to solve here?

looking at the article you sent:
"Some DBA tasks, like creating private database links or setting up jobs
using the DBMS_JOB package, require the administrator to log in as a
specific user. This can present a problem if the administrator doesn't know
the password."

probably as Mateusz mentioned SET ROLE is the solution, if you are looking
for something more sophisticated you might want to look what INHERIT
attribute means:
https://www.postgresql.org/docs/current/role-membership.html or look at
functions with security definer:
https://www.postgresql.org/docs/current/sql-createfunction.html

"You have multiple developers working in a shared schema. Letting multiple
people share the same credentials represents a security risk. Instead you
create a separate proxy user for each individual, allowing them to connect
to the schema owner with their own credentials. If a user leaves a project,
you simply lock or drop their user, and they no longer have access to the
shared schema."

It's totally not needed in Postgres as the architecture and the concept of
schema and users is different and you don't have any credentials to the
schema, which is just a namespace in postgres