Re: PDO: Switch database user without reopening connection

Gordon schreef:
I want to add a feature to a project I'm working on where i have
multiple users set up on my Postgres database with varying levels of
access. At the bare minimum there will be a login user who only has
read access to the users table so that users can log in. Once a user
has been logged in successfully I want to escalate that user's access
level to one appropriate to their role, which will include switching
the postgres user they are logged in as to one that can make
modifications to the database as well (editors get update permission,
supereditors get insert/delete permission for articles, admin get
insert/delete access on the user database etc).

The problem is the only way I can find of doing this is to close the
open PDO and create a new one, in other words disconnect from the
database and reconnect. As database connections are expensive to
initialize I really want to avoid this and do the postgres of an su
instead.

Back when I was doing this the old fashioned way (php 4, MySQL, MySQL
extension, no OOP) I could use mysql_change_user () to switch DB users
once a logging in user's credentials had been validated. PDO is a
great new addition to PHP and has so many excellent new features that
there's really little excuse not to use it, but one thing it
apparently lacks is a PDO equivalent to the old mysql_change_user
command.

I'm pretty sure that user switching is supported in Postgres, but with
no change_user function how do I go about doing it?

Hi Gordon,

Unless you are using persistent connections, there is not much use in changing.
I stopped using persistent connections (PHP4.3 on Postgresql8.2) because I got into strange troubles that I didn't understand.
Reading around in here confirmed my suspicions, a few more regulars in here don't like the persistent connections either. (ask Jerry)
So I quites using them.

Each script I write makes a fresh connection. (I believe PHP does some pooling behind the scenes anyway, but that never gave me troubles.)
I actually never had performanceproblems using a fresh connection on Postgresql.

In your case I would simply store the Postgresql-username you assign to a certain visitor in the Session, and use that value to start the right (and fresh) connection each invocation of your scripts.

just my 2 cent.

Regards,
Erwin Moller
.