Re: Extending SET SESSION AUTHORIZATION

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Ezra Epstein wrote:

I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a
password.

Uh, a password? What purpose would that serve?

Indeed. SET SESSION AUTH is already allowed only to superusers --- a
superuser hardly needs any additional privileges to become whoever he
wants.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 26, 2004 7:56 PM
To: Bruce Momjian
Cc: eepstein@prajnait.com; PostgreSQL-development
Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Ezra Epstein wrote:

I'd like to extend SET SESSION AUTHORIZATION to support a form

which takes a

password.

Uh, a password? What purpose would that serve?

Indeed. SET SESSION AUTH is already allowed only to superusers --- a
superuser hardly needs any additional privileges to become whoever he
wants.

regards, tom lane

For exactly the opposite usage: allowing a non-privileged user to take on a
different authorization IFF a password is also supplied. This allows a user
to use an existing connection (so, for example, connection pooling works)
and not require a high priv'd account to then act as a specific (and
specifically priv'd) user of the system.

E.g., I could then have a user who has only connection privs for the DB and
then use a SET SESSION AUTH as a means of "logging in" as a specific user.
What this buys me:
Connection pooling (critical for volume web apps)
Postgres (DB) level enforcement of privileges via GRANT and REVOKE : so
that my priv scheme is consistent across db access methods and I don't have
to be too concerned about replicating the authorization logic out in the app
layer.

== Ezra Epstein.

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Ezra Epstein wrote:

I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a
password.

Uh, a password? What purpose would that serve?

Indeed. SET SESSION AUTH is already allowed only to superusers --- a
superuser hardly needs any additional privileges to become whoever he
wants.

It is very helpful for connection pooling/persistent connections. Say I have 10
connections opened as superuser. I can switch the connection authorization per
query and let database enforce the rules and access control.

For authentication, I can keep a dummy connection.

There could be multiple ways to improve this behaviour.

1. If a non super-user attempts set session authorization, let him do so with
proper password.

2. Add password to set session authorization as suggested above.

I would prefer this actually. In case the application is breached, with option
2, the database is left wide open. With option 1, that may not be the case if
initial connection is with a sufficiently unprivilaged user. But then I need to
cache the actual password, which is another can of worms..:-(

Additionally it would be great if libpq could just authenticate a user without
forking a backend. I think some kind of PAM voodoo can be substituted for that
but having a libpq frontend is great.

I did suggest this earlier as well. Just reiterating..

Shridhar

"Ezra Epstein" <eepstein@prajnait.com> writes:

I'd like to extend SET SESSION AUTHORIZATION to support a form
which takes a password.

Uh, a password? What purpose would that serve?

For exactly the opposite usage: allowing a non-privileged user to take on a
different authorization IFF a password is also supplied. This allows a user
to use an existing connection (so, for example, connection pooling works)
and not require a high priv'd account to then act as a specific (and
specifically priv'd) user of the system.

I do not think SET SESSION AUTH is a suitable replacement for logging
in. For one thing, it doesn't apply per-user GUC settings. For
another, using it this way in a pooling environment would be completely
insecure --- what if you forget to "log out", or your attempt to do so
is dropped because it was inside a failed transaction block?

Another objection to doing things this way is that it would just about
force people to embed passwords into their SQL scripts, creating another
serious source of insecurity.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, January 27, 2004 10:46 AM
To: eepstein@prajnait.com
Cc: Bruce Momjian; PostgreSQL-development
Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION

"Ezra Epstein" <eepstein@prajnait.com> writes:

I'd like to extend SET SESSION AUTHORIZATION to support a form
which takes a password.

Uh, a password? What purpose would that serve?

For exactly the opposite usage: allowing a non-privileged user

to take on a

different authorization IFF a password is also supplied. This

allows a user

to use an existing connection (so, for example, connection

pooling works)

and not require a high priv'd account to then act as a specific (and
specifically priv'd) user of the system.

I do not think SET SESSION AUTH is a suitable replacement for logging
in. For one thing, it doesn't apply per-user GUC settings. For

OK, what are GUC settings. Can SET SESSION AUTH be extended to do this as
needed?

another, using it this way in a pooling environment would be completely
insecure --- what if you forget to "log out", or your attempt to do so
is dropped because it was inside a failed transaction block?

Well, consider the alternative. A web user logs in to the web app, not to
the DB. The web app connects to the DB as a user which has the union of ALL
privs of each of the web users! This is the default mode of ALL production
web apps. In other words, the alternative is an even bigger security hole

Also, in web apps you get to do post-response clean-up. I'd put the RESET
SESSION AUTH code there -- all by itself, outside of any transaction. So,
on 2 counts I would say the approach I would like to take will result in a
more secure application overall.

Another objection to doing things this way is that it would just about
force people to embed passwords into their SQL scripts, creating another
serious source of insecurity.

Au contraire! Go do a security audit of most production web system. While
the password might not be in SQL it is usually in a config file. E.g., in
the server.xml file for a J2EE servlet container as part of the declaration
of the jdbc DataSource. And the user is highly priv'd (union of all privs
for every user of the application). So what I'd like is a default user that
has NO privs. The user logs in, but the credentials are not validated
against an internal application-specific (or LDAP/Identity-server provided)
authentication but against the database's authentication itself! (Then I'd
add password synchronization for an enterprise client to keep their
Directory servers and the DB aligned.) In other words: no password or user
login is stored at all. It is provided by the user during log in! A much
better and much *more* secure approach.

== Ezra Epstein

"Ezra Epstein" <eepstein@prajnait.com> writes:

I do not think SET SESSION AUTH is a suitable replacement for logging
in. For one thing, it doesn't apply per-user GUC settings. For

OK, what are GUC settings. Can SET SESSION AUTH be extended to do this as
needed?

Not very easily; it's not clear to me how you undo the previous settings
taken from the other user, nor how you go back at RESET SESSION AUTH.
(It's not so much that you don't know what settings are specified in
pg_shadow, as that you don't know what might have been adopted if they'd
not been there.) I am also concerned about whether layering such
semantics onto SET SESSION AUTH wouldn't break its existing uses.

Maybe you could declare by fiat that you don't care and users in this
sort of environment don't get to have per-user GUC settings. If they
are sharing a webapp front end then maybe they don't need 'em. I dunno
how important it really is, but we'd have to think about the implications.

another, using it this way in a pooling environment would be completely
insecure --- what if you forget to "log out", or your attempt to do so
is dropped because it was inside a failed transaction block?

Well, consider the alternative. A web user logs in to the web app, not to
the DB. The web app connects to the DB as a user which has the union of ALL
privs of each of the web users! This is the default mode of ALL production
web apps. In other words, the alternative is an even bigger security hole

No, the alternative is that the web app is responsible for managing
security, which I think is the only reasonable place to put the
responsibility if you intend to use shared connections. I find it
simply illusory to think that a shared-connection setup is going to be
secure if you don't have complete confidence in the front end.
Basically what you're saying is that you're willing to trust the front
end to ensure that user A can never do anything over user B's
connection, but you're not willing to trust it to enforce security
otherwise. That doesn't seem to hold water to me.

Another issue with a SET SESSION AUTH extension of this kind is that it
would force every multi-user installation to maintain password security
whether they want it or not. In an environment where users do not
normally use database passwords (perhaps they use IDENT auth instead)
it's entirely likely that they'd not bother to select good passwords or
guard them. In that case the option to get into someone else's account
via SET SESSION AUTH becomes a security hole that people are unlikely to
think to plug --- the old "out of sight, out of mind" problem.

regards, tom lane

-----Original Message-----

From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, January 27, 2004 1:35 PM

"Ezra Epstein" <eepstein@prajnait.com> writes:

I do not think SET SESSION AUTH is a suitable replacement for logging
in. For one thing, it doesn't apply per-user GUC settings. For

OK, what are GUC settings. Can SET SESSION AUTH be extended to

do this as

needed?

Not very easily; it's not clear to me how you undo the previous settings
taken from the other user, nor how you go back at RESET SESSION AUTH.
(It's not so much that you don't know what settings are specified in
pg_shadow, as that you don't know what might have been adopted if they'd
not been there.) I am also concerned about whether layering such
semantics onto SET SESSION AUTH wouldn't break its existing uses.

Maybe you could declare by fiat that you don't care and users in this
sort of environment don't get to have per-user GUC settings. If they
are sharing a webapp front end then maybe they don't need 'em. I dunno
how important it really is, but we'd have to think about the implications.

Since I still don't know what "GUC" even stands for, I'll just take the
entirely naive approach and assume it doesn't matter for these purposes.

another, using it this way in a pooling environment would be completely
insecure --- what if you forget to "log out", or your attempt to do so
is dropped because it was inside a failed transaction block?

Well, consider the alternative. A web user logs in to the web

app, not to

the DB. The web app connects to the DB as a user which has the

union of ALL

privs of each of the web users! This is the default mode of

ALL production

web apps. In other words, the alternative is an even bigger

security hole

No, the alternative is that the web app is responsible for managing
security, which I think is the only reasonable place to put the
responsibility if you intend to use shared connections. I find it

Yes and if you've already had the DBA configure the DB to have group-based
security at the rather fine level of granularity that SQL gives, why not
have a means of leveraging that -- and thereby simplifying the applications,
oh and getting security consistency across all such apps free of charge --
rather than replicate it all in a different tier?

simply illusory to think that a shared-connection setup is going to be
secure if you don't have complete confidence in the front end.
Basically what you're saying is that you're willing to trust the front
end to ensure that user A can never do anything over user B's
connection, but you're not willing to trust it to enforce security
otherwise. That doesn't seem to hold water to me.

Another issue with a SET SESSION AUTH extension of this kind is that it
would force every multi-user installation to maintain password security
whether they want it or not. In an environment where users do not
normally use database passwords (perhaps they use IDENT auth instead)
it's entirely likely that they'd not bother to select good passwords or
guard them. In that case the option to get into someone else's account
via SET SESSION AUTH becomes a security hole that people are unlikely to
think to plug --- the old "out of sight, out of mind" problem.

This last one is the only concern raised that I can see being one I'd worry
over. It makes me think that enabling the alternate mode of SET SESSION
AUTH could itself be subject to a DB parameter (settable at startup or via
the SET mechanism) and turned off by default.

== Ezra Epstein