On 31/05/2016 17:23, Melvin Davidson wrote:
Actually, you do not need to SWITCH, you just need permission to change to path
and gain access to all user2 privs, which is exactly what SET ROLE user2 does.
There is no need for a password, since user1 is already connected to the DB.
Any superuser can give the GRANT ROLE to any other user.
Still, PgSQL logs report the original user everywhere. Not useful for auditing,
debugging, etc
That being said, IMHO, I believe having a separate schema for every user is
poor database design
I agree about this, there are much better ways to utilize schemata.
On Tue, May 31, 2016 at 10:18 AM, Achilleas Mantzios <ach...@matrix.gatewaynet.com
<mailto:ach...@matrix.gatewaynet.com>> wrote:
On 31/05/2016 10:45, CN wrote:
I have a feeling that slight enhancement to commands "SET ROLE" or "SET
SESSION AUTHORIZATION" can obsolete and outperform external connection
pooling tools in some use cases.
Assume we are in the following situation:
- There are a million schemas each owned by a distinct role.
- Every role is not allowed to access any other schema except its own.
If command "SET SESSION AUTHORIZATION" is enhanced to accept two
additional arguments
PASSWORD <password>
, then a client simply establishes only one connection to server and do
jobs for a million roles.
Say I want to gain full access to "schema2", I simply issue these two
commands
SET SESSION AUTHORIZATION user2 PASSWORD p2;
SET SEARCH_PATH TO schema2,pg_category;
, where "p2" is the password associated with role "user2".
If the current role is superuser "postgres" and it wants to downgrade
itself to role "user3", then it simply sends these commands:
SET SESSION AUTHORIZATION user3;
SET SEARCH_PATH TO schema3,pg_category;
Does my points make sense?
Is it eligible for feature request?
I believe your thoughts are on the same line with an idea some people had about using
connection pools on Java EE environments, in a manner that does not use a generic
"catch all" user, but uses
the individual users sharing the security context from the app server.
This way one could have the benefits of the connection pool, and the
benefits of fine-grained and rich PostgreSQL security framework, the ability to
log user's activity, debug the system easier,
see real users on pg_stat_activity, on ps(1), on top(1) etc etc.
The way we do it currently is by having personalized connection pools for
pgsql in jboss. It does the job from every aspect, except one : it sucks as far
as performance is concerned. Every user
is tied to his/her number of connections. It creates a sandbox around each user, so
that a "malicious" greedy user (with the help of a poorly designed app of
course) can only bring down his own
pool, while others run unaffected, but still performance suffers. The idea
would be to use a common pool of connections and assign users on demand as they
are taken from the common pool, and
later also return them to the common pool, once closed.
Whenever I talked to PG ppl about it, they told me that redesigning the SET
ROLE functionality so that it correctly applies all the security checks and
also so that it results in reflecting the
effective user in all references in logs, sys views, OS (ps, top, etc)
etc.. was hard to do, and the convo stopped right there.
With all the new and modern cloud-inspired paradigms out there, our
traditional architecture might not of much interest any more, still I would
love to make the above happen some time.
Best Regards,
CN
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
