Postgres Pro
Downloads
Home   >   mailing lists

Thread: Feature request(?): Proxy User

Feature request(?): Proxy User

From
Wiwwo Staff
Date:
Sorry to post solutions and links about alternative DBMSs, but I miss this sort of "sudo" ad database user level:

Is there any approach to achieve the same result in Postgresql?
Anyone sharing the need of this functionality?

Tnx!

Re: Feature request(?): Proxy User

From
Mateusz Henicz
Date:
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@wiwwo.com> napisał(a):
Sorry to post solutions and links about alternative DBMSs, but I miss this sort of "sudo" ad database user level:

Is there any approach to achieve the same result in Postgresql?
Anyone sharing the need of this functionality?

Tnx!

Re: Feature request(?): Proxy User

From
Alicja Kucharczyk
Date:
czw., 28 lip 2022 o 14:18 Mateusz Henicz <mateuszhenicz@gmail.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@wiwwo.com> napisał(a):
Sorry to post solutions and links about alternative DBMSs, but I miss this sort of "sudo" ad database user level:

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