PDO: Switch database user without reopening connection

[Gordon]

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_us er () 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_us er
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?

[Erwin Moller]

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_us er () 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_us er
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 performanceprob lems 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

[Gordon]

Re: PDO: Switch database user without reopening connection

On May 19, 12:37 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

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_us er () 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_us er
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 performanceprob lems 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

Thanks for the quick reply but I don't think I fully made myself
clear. I'm not using persistant connections or any kind of witchcraft
like that. :) I meant during the lifetime of a given script
invokation I want to be able to switch users. Some quick pseudocode
is given below: The first one is the approach where the user gets a
new PDO

if ($_SESSION ['user'])
{
// Connect as guest user
$db = new PDO ('guest user login credentials');
if (user successfully verified against database)
{
unset ($db);
$db = new PDO ('authenticated user login credentials');
// Do stuff
}
}

I don't like this approach because 1) it involves dropping and
reinstating a connection to the database and the destruction and
recreation of an object, both of which have overhead which I'd rather
avoid, and 2) You have all the references to the $db object to contend
with.

I think a more elegant solution would be something like:

if ($_SESSION ['user'])
{
// Connect as guest user
$db = new PDO ('guest user login credentials');
if (user successfully verified against database)
{
$db -switchUser ('authenticated user login credentials');
// Do stuff
}
}

The main reason for wanting to do this is of course security. I want
a guest role and an authenticated role, where the guest role is
limited to reading from a handful of approved table. I don't want
everybody who runs my scripts to do so with a database user that has
the Power of Greyskull for obvious reasons. :)

[Erwin Moller]

Re: PDO: Switch database user without reopening connection

Gordon schreef:

On May 19, 12:37 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

>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
>>modificatio ns to the database as well (editors get update permission,
>>supereditor s 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_us er () 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_us er
>>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 performanceprob lems 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

>
Thanks for the quick reply but I don't think I fully made myself
clear. I'm not using persistant connections or any kind of witchcraft
like that. :) I meant during the lifetime of a given script
invokation I want to be able to switch users. Some quick pseudocode
is given below: The first one is the approach where the user gets a
new PDO
>
if ($_SESSION ['user'])
{
// Connect as guest user
$db = new PDO ('guest user login credentials');
if (user successfully verified against database)
{
unset ($db);
$db = new PDO ('authenticated user login credentials');
// Do stuff
}
}
>
I don't like this approach because 1) it involves dropping and
reinstating a connection to the database and the destruction and
recreation of an object, both of which have overhead which I'd rather
avoid, and 2) You have all the references to the $db object to contend
with.
>
I think a more elegant solution would be something like:
>
if ($_SESSION ['user'])
{
// Connect as guest user
$db = new PDO ('guest user login credentials');
if (user successfully verified against database)
{
$db -switchUser ('authenticated user login credentials');
// Do stuff
}
}
>
The main reason for wanting to do this is of course security. I want
a guest role and an authenticated role, where the guest role is
limited to reading from a handful of approved table. I don't want
everybody who runs my scripts to do so with a database user that has
the Power of Greyskull for obvious reasons. :)

Hi Gordon,

OK, more clear now.
I don't work your way, hence the misinterpretati on. ;-)
Isn't it possible to simply use the SESSION to hold the login as I
suggested?
In my humble opinion, your first route to the database (as guestuser to
check the login credentials) shouldn't be needed anyway.
I think it is easier to set the result of that query (succes or not) in
the session AFTER the first login (or second, or whatever).

Why authenticate a user multiple times?
'my way' would be:
1) user logs in (login.php), POSTS username/password to login_process.p hp
2) login_process.p hp checks the passed username/password.
As a result it:
a) rejects the login (unknown username/password)
or
b) Sets the result (level) in the Session, eg:
$_SESSION["authentica ted"] = "Y";
$_SESSION["userid"] = row["userid"];
$_SESSION["DB_user_connec t"] = row["dblevel"];

The last 2 rows contain fantasynames of course, but the idea is that you
store in the latter a username to connect to to the database.
eg:
$db = "";
if ($_SESSION["DB_user_connec t"] == "guest"){
$db = new PDO ('guest user login credentials');
} else {
if ($_SESSION["DB_user_connec t"] == "level1"){
$db = new PDO ('level1 user login credentials');
}
}

etc.
(Use a switch statement if you have many instead of if-then-else mess)

In that way you always build 1 connection.

The above is only to show how I approach this, maybe it is not possible
in your setup.

Regards,
Erwin Moller

[Gordon]

Re: PDO: Switch database user without reopening connection

On May 19, 1:34 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

Gordon schreef:
>
>
>

On May 19, 12:37 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

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
>modification s 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_us er () 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_us er
>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 performanceprob lems 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

>

Thanks for the quick reply but I don't think I fully made myself
clear. I'm not using persistant connections or any kind of witchcraft
like that. :) I meant during the lifetime of a given script
invokation I want to be able to switch users. Some quick pseudocode
is given below: The first one is the approach where the user gets a
new PDO

>

if ($_SESSION ['user'])
{
// Connect as guest user
$db = new PDO ('guest user login credentials');
if (user successfully verified against database)
{
unset ($db);
$db = new PDO ('authenticated user login credentials');
// Do stuff
}
}

>

I don't like this approach because 1) it involves dropping and
reinstating a connection to the database and the destruction and
recreation of an object, both of which have overhead which I'd rather
avoid, and 2) You have all the references to the $db object to contend
with.

>

I think a more elegant solution would be something like:

>

if ($_SESSION ['user'])
{
// Connect as guest user
$db = new PDO ('guest user login credentials');
if (user successfully verified against database)
{
$db -switchUser ('authenticated user login credentials');
// Do stuff
}
}

>

The main reason for wanting to do this is of course security. I want
a guest role and an authenticated role, where the guest role is
limited to reading from a handful of approved table. I don't want
everybody who runs my scripts to do so with a database user that has
the Power of Greyskull for obvious reasons. :)

>
Hi Gordon,
>
OK, more clear now.
I don't work your way, hence the misinterpretati on. ;-)
Isn't it possible to simply use the SESSION to hold the login as I
suggested?
In my humble opinion, your first route to the database (as guestuser to
check the login credentials) shouldn't be needed anyway.
I think it is easier to set the result of that query (succes or not) in
the session AFTER the first login (or second, or whatever).
>
Why authenticate a user multiple times?
'my way' would be:
1) user logs in (login.php), POSTS username/password to login_process.p hp
2) login_process.p hp checks the passed username/password.
As a result it:
a) rejects the login (unknown username/password)
or
b) Sets the result (level) in the Session, eg:
$_SESSION["authentica ted"] = "Y";
$_SESSION["userid"] = row["userid"];
$_SESSION["DB_user_connec t"] = row["dblevel"];
>
The last 2 rows contain fantasynames of course, but the idea is that you
store in the latter a username to connect to to the database.
eg:
$db = "";
if ($_SESSION["DB_user_connec t"] == "guest"){
$db = new PDO ('guest user login credentials');} else {
>
if ($_SESSION["DB_user_connec t"] == "level1"){
$db = new PDO ('level1 user login credentials');
}
>
}
>
etc.
(Use a switch statement if you have many instead of if-then-else mess)
>
In that way you always build 1 connection.
>
The above is only to show how I approach this, maybe it is not possible
in your setup.
>
Regards,
Erwin Moller

Okay, maybe authenticate is a bad choice of words, perhaps verify
would be better.

My login system sets a user ID in the session that corresponds to the
user ID of the user in the database (unique integer generated by an
autoincrement, but you probably guessed that already). Each time a
script which is covered by a login is invoked it checks the number in
the session against the database.

As a general rule of thumb I tend to try and store as little as
possible about a user in a session, typically just the ID of the
logged in user. I have written systems in the past where there was
more data than that stored but I always found them to be problematic
because you had to remember to change things in mor than one place if
the user decided to change a parameter that's kept in the session, and
because there are issues involving user administration while a user
being administrated is logged in. For example, if you want to
escalate the permissions of a user who is currently logged in with
limited permissions then that user has to log out and back in again
for the changes to take effect. Worse, if you decide to delete or ban
a user and they have an ['authenticated'] = 1 param in their session
then they can remain logged in until they terminate the session by
logging out or quitting the browser. Having as little as possible in
the session means that if you choose to delete a user, that user's
session will no longer refer to a valid user in the database and he
will instantly lose his login on next page view.

[Erwin Moller]

Re: PDO: Switch database user without reopening connection

Gordon schreef:

On May 19, 1:34 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

>Gordon schreef:
>>
>>
>>

>>On May 19, 12:37 pm, Erwin Moller
>><Since_humans _read_this_I_am _spammed_too_m. ..@spamyourself .comwrote:
>>>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
>>>>modificatio ns to the database as well (editors get update permission,
>>>>supereditor s 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
>>>>initializ e 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_us er () 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
>>>>apparentl y lacks is a PDO equivalent to the old mysql_change_us er
>>>>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 performanceprob lems 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
>>Thanks for the quick reply but I don't think I fully made myself
>>clear. I'm not using persistant connections or any kind of witchcraft
>>like that. :) I meant during the lifetime of a given script
>>invokation I want to be able to switch users. Some quick pseudocode
>>is given below: The first one is the approach where the user gets a
>>new PDO
>>if ($_SESSION ['user'])
>>{
>> // Connect as guest user
>> $db = new PDO ('guest user login credentials');
>> if (user successfully verified against database)
>> {
>> unset ($db);
>> $db = new PDO ('authenticated user login credentials');
>> // Do stuff
>> }
>>}
>>I don't like this approach because 1) it involves dropping and
>>reinstating a connection to the database and the destruction and
>>recreation of an object, both of which have overhead which I'd rather
>>avoid, and 2) You have all the references to the $db object to contend
>>with.
>>I think a more elegant solution would be something like:
>>if ($_SESSION ['user'])
>>{
>> // Connect as guest user
>> $db = new PDO ('guest user login credentials');
>> if (user successfully verified against database)
>> {
>> $db -switchUser ('authenticated user login credentials');
>> // Do stuff
>> }
>>}
>>The main reason for wanting to do this is of course security. I want
>>a guest role and an authenticated role, where the guest role is
>>limited to reading from a handful of approved table. I don't want
>>everybody who runs my scripts to do so with a database user that has
>>the Power of Greyskull for obvious reasons. :)

>Hi Gordon,
>>
>OK, more clear now.
>I don't work your way, hence the misinterpretati on. ;-)
>Isn't it possible to simply use the SESSION to hold the login as I
>suggested?
>In my humble opinion, your first route to the database (as guestuser to
>check the login credentials) shouldn't be needed anyway.
>I think it is easier to set the result of that query (succes or not) in
>the session AFTER the first login (or second, or whatever).
>>
>Why authenticate a user multiple times?
>'my way' would be:
>1) user logs in (login.php), POSTS username/password to login_process.p hp
>2) login_process.p hp checks the passed username/password.
>As a result it:
>a) rejects the login (unknown username/password)
>or
>b) Sets the result (level) in the Session, eg:
>$_SESSION["authentica ted"] = "Y";
>$_SESSION["userid"] = row["userid"];
>$_SESSION["DB_user_connec t"] = row["dblevel"];
>>
>The last 2 rows contain fantasynames of course, but the idea is that you
>store in the latter a username to connect to to the database.
>eg:
>$db = "";
>if ($_SESSION["DB_user_connec t"] == "guest"){
> $db = new PDO ('guest user login credentials');} else {
>>
> if ($_SESSION["DB_user_connec t"] == "level1"){
> $db = new PDO ('level1 user login credentials');
> }
>>
>}
>>
>etc.
>(Use a switch statement if you have many instead of if-then-else mess)
>>
>In that way you always build 1 connection.
>>
>The above is only to show how I approach this, maybe it is not possible
>in your setup.
>>
>Regards,
>Erwin Moller

>
Okay, maybe authenticate is a bad choice of words, perhaps verify
would be better.
>
My login system sets a user ID in the session that corresponds to the
user ID of the user in the database (unique integer generated by an
autoincrement, but you probably guessed that already). Each time a
script which is covered by a login is invoked it checks the number in
the session against the database.
>
As a general rule of thumb I tend to try and store as little as
possible about a user in a session, typically just the ID of the
logged in user. I have written systems in the past where there was
more data than that stored but I always found them to be problematic
because you had to remember to change things in mor than one place if
the user decided to change a parameter that's kept in the session, and
because there are issues involving user administration while a user
being administrated is logged in. For example, if you want to
escalate the permissions of a user who is currently logged in with
limited permissions then that user has to log out and back in again
for the changes to take effect. Worse, if you decide to delete or ban
a user and they have an ['authenticated'] = 1 param in their session
then they can remain logged in until they terminate the session by
logging out or quitting the browser. Having as little as possible in
the session means that if you choose to delete a user, that user's
session will no longer refer to a valid user in the database and he
will instantly lose his login on next page view.

OK, all clear.
If you have the need to change userpermission during a session and do
not want the user to login again, yes: you need your current setup/approach.
Lets hope somebody can help you with the switching user on PDO, because
I cannot. ;-)

Good luck.
Regards,
Erwin Moller

[Gordon]

Re: PDO: Switch database user without reopening connection

On May 19, 3:54 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

Gordon schreef:
>
>
>

On May 19, 1:34 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

Gordon schreef:

>

>On May 19, 12:37 pm, Erwin Moller
><Since_humans_ read_this_I_am_ spammed_too_m.. .@spamyourself. comwrote:
>>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
>>>modification s to the database as well (editors get update permission,
>>>superedito rs 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_us er () 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_us er
>>>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 performanceprob lems 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
>Thanks for the quick reply but I don't think I fully made myself
>clear. I'm not using persistant connections or any kind of witchcraft
>like that. :) I meant during the lifetime of a given script
>invokation I want to be able to switch users. Some quick pseudocode
>is given below: The first one is the approach where the user gets a
>new PDO
>if ($_SESSION ['user'])
>{
> // Connect as guest user
> $db = new PDO ('guest user login credentials');
> if (user successfully verified against database)
> {
> unset ($db);
> $db = new PDO ('authenticated user login credentials');
> // Do stuff
> }
>}
>I don't like this approach because 1) it involves dropping and
>reinstating a connection to the database and the destruction and
>recreation of an object, both of which have overhead which I'd rather
>avoid, and 2) You have all the references to the $db object to contend
>with.
>I think a more elegant solution would be something like:
>if ($_SESSION ['user'])
>{
> // Connect as guest user
> $db = new PDO ('guest user login credentials');
> if (user successfully verified against database)
> {
> $db -switchUser ('authenticated user login credentials');
> // Do stuff
> }
>}
>The main reason for wanting to do this is of course security. I want
>a guest role and an authenticated role, where the guest role is
>limited to reading from a handful of approved table. I don't want
>everybody who runs my scripts to do so with a database user that has
>the Power of Greyskull for obvious reasons. :)
Hi Gordon,

>

OK, more clear now.
I don't work your way, hence the misinterpretati on. ;-)
Isn't it possible to simply use the SESSION to hold the login as I
suggested?
In my humble opinion, your first route to the database (as guestuser to
check the login credentials) shouldn't be needed anyway.
I think it is easier to set the result of that query (succes or not) in
the session AFTER the first login (or second, or whatever).

>

Why authenticate a user multiple times?
'my way' would be:
1) user logs in (login.php), POSTS username/password to login_process.p hp
2) login_process.p hp checks the passed username/password.
As a result it:
a) rejects the login (unknown username/password)
or
b) Sets the result (level) in the Session, eg:
$_SESSION["authentica ted"] = "Y";
$_SESSION["userid"] = row["userid"];
$_SESSION["DB_user_connec t"] = row["dblevel"];

>

The last 2 rows contain fantasynames of course, but the idea is that you
store in the latter a username to connect to to the database.
eg:
$db = "";
if ($_SESSION["DB_user_connec t"] == "guest"){
$db = new PDO ('guest user login credentials');} else {

>

if ($_SESSION["DB_user_connec t"] == "level1"){
$db = new PDO ('level1 user login credentials');
}

>

}

>

etc.
(Use a switch statement if you have many instead of if-then-else mess)

>

In that way you always build 1 connection.

>

The above is only to show how I approach this, maybe it is not possible
in your setup.

>

Regards,
Erwin Moller

>

Okay, maybe authenticate is a bad choice of words, perhaps verify
would be better.

>

My login system sets a user ID in the session that corresponds to the
user ID of the user in the database (unique integer generated by an
autoincrement, but you probably guessed that already). Each time a
script which is covered by a login is invoked it checks the number in
the session against the database.

>

As a general rule of thumb I tend to try and store as little as
possible about a user in a session, typically just the ID of the
logged in user. I have written systems in the past where there was
more data than that stored but I always found them to be problematic
because you had to remember to change things in mor than one place if
the user decided to change a parameter that's kept in the session, and
because there are issues involving user administration while a user
being administrated is logged in. For example, if you want to
escalate the permissions of a user who is currently logged in with
limited permissions then that user has to log out and back in again
for the changes to take effect. Worse, if you decide to delete or ban
a user and they have an ['authenticated'] = 1 param in their session
then they can remain logged in until they terminate the session by
logging out or quitting the browser. Having as little as possible in
the session means that if you choose to delete a user, that user's
session will no longer refer to a valid user in the database and he
will instantly lose his login on next page view.

>
OK, all clear.
If you have the need to change userpermission during a session and do
not want the user to login again, yes: you need your current setup/approach.
Lets hope somebody can help you with the switching user on PDO, because
I cannot. ;-)
>
Good luck.
Regards,
Erwin Moller

Thanks for the effort though, it's certainly appreciated.

There must be some kind of SQL statement or backslash command you can
execute during a session to log in as a different DB user, I just
don't know what it is. This was so much easier in the good old MySQL
and non-OO days :)

[Gordon]

Re: PDO: Switch database user without reopening connection

On May 19, 3:54 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

Gordon schreef:
>
>
>

On May 19, 1:34 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

Gordon schreef:

>

>On May 19, 12:37 pm, Erwin Moller
><Since_humans_ read_this_I_am_ spammed_too_m.. .@spamyourself. comwrote:
>>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
>>>modification s to the database as well (editors get update permission,
>>>superedito rs 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_us er () 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_us er
>>>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 performanceprob lems 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
>Thanks for the quick reply but I don't think I fully made myself
>clear. I'm not using persistant connections or any kind of witchcraft
>like that. :) I meant during the lifetime of a given script
>invokation I want to be able to switch users. Some quick pseudocode
>is given below: The first one is the approach where the user gets a
>new PDO
>if ($_SESSION ['user'])
>{
> // Connect as guest user
> $db = new PDO ('guest user login credentials');
> if (user successfully verified against database)
> {
> unset ($db);
> $db = new PDO ('authenticated user login credentials');
> // Do stuff
> }
>}
>I don't like this approach because 1) it involves dropping and
>reinstating a connection to the database and the destruction and
>recreation of an object, both of which have overhead which I'd rather
>avoid, and 2) You have all the references to the $db object to contend
>with.
>I think a more elegant solution would be something like:
>if ($_SESSION ['user'])
>{
> // Connect as guest user
> $db = new PDO ('guest user login credentials');
> if (user successfully verified against database)
> {
> $db -switchUser ('authenticated user login credentials');
> // Do stuff
> }
>}
>The main reason for wanting to do this is of course security. I want
>a guest role and an authenticated role, where the guest role is
>limited to reading from a handful of approved table. I don't want
>everybody who runs my scripts to do so with a database user that has
>the Power of Greyskull for obvious reasons. :)
Hi Gordon,

>

OK, more clear now.
I don't work your way, hence the misinterpretati on. ;-)
Isn't it possible to simply use the SESSION to hold the login as I
suggested?
In my humble opinion, your first route to the database (as guestuser to
check the login credentials) shouldn't be needed anyway.
I think it is easier to set the result of that query (succes or not) in
the session AFTER the first login (or second, or whatever).

>

Why authenticate a user multiple times?
'my way' would be:
1) user logs in (login.php), POSTS username/password to login_process.p hp
2) login_process.p hp checks the passed username/password.
As a result it:
a) rejects the login (unknown username/password)
or
b) Sets the result (level) in the Session, eg:
$_SESSION["authentica ted"] = "Y";
$_SESSION["userid"] = row["userid"];
$_SESSION["DB_user_connec t"] = row["dblevel"];

>

The last 2 rows contain fantasynames of course, but the idea is that you
store in the latter a username to connect to to the database.
eg:
$db = "";
if ($_SESSION["DB_user_connec t"] == "guest"){
$db = new PDO ('guest user login credentials');} else {

>

if ($_SESSION["DB_user_connec t"] == "level1"){
$db = new PDO ('level1 user login credentials');
}

>

}

>

etc.
(Use a switch statement if you have many instead of if-then-else mess)

>

In that way you always build 1 connection.

>

The above is only to show how I approach this, maybe it is not possible
in your setup.

>

Regards,
Erwin Moller

>

Okay, maybe authenticate is a bad choice of words, perhaps verify
would be better.

>

My login system sets a user ID in the session that corresponds to the
user ID of the user in the database (unique integer generated by an
autoincrement, but you probably guessed that already). Each time a
script which is covered by a login is invoked it checks the number in
the session against the database.

>

As a general rule of thumb I tend to try and store as little as
possible about a user in a session, typically just the ID of the
logged in user. I have written systems in the past where there was
more data than that stored but I always found them to be problematic
because you had to remember to change things in mor than one place if
the user decided to change a parameter that's kept in the session, and
because there are issues involving user administration while a user
being administrated is logged in. For example, if you want to
escalate the permissions of a user who is currently logged in with
limited permissions then that user has to log out and back in again
for the changes to take effect. Worse, if you decide to delete or ban
a user and they have an ['authenticated'] = 1 param in their session
then they can remain logged in until they terminate the session by
logging out or quitting the browser. Having as little as possible in
the session means that if you choose to delete a user, that user's
session will no longer refer to a valid user in the database and he
will instantly lose his login on next page view.

>
OK, all clear.
If you have the need to change userpermission during a session and do
not want the user to login again, yes: you need your current setup/approach.
Lets hope somebody can help you with the switching user on PDO, because
I cannot. ;-)
>
Good luck.
Regards,
Erwin Moller

I thought I was on to something when I found

http://www.postgresql.org/docs/current/interactive/sql-set-session-authorization.html

but it apparently will only work if you're logged on as a superuser.
That seems of limited use to me. :)

Perhaps it's possible to log in as a superuser, then immediately drop
to a user with read-only privileges, then escalate to a user with the
correct privileges once the logged in web user has been verified.
This approach doesn't strike me as much more elegant than the recreate
the PDO version, though it does have the advantage of not having to
drop and reinitialize the connection...

[Erwin Moller]

Re: PDO: Switch database user without reopening connection

Gordon schreef:

<snip>

Thanks for the effort though, it's certainly appreciated.
>
There must be some kind of SQL statement or backslash command you can
execute during a session to log in as a different DB user, I just
don't know what it is. This was so much easier in the good old MySQL
and non-OO days :)

Hi Gordon,

When somebody says to me he wants MySQL over Postgresql, I am really
motivated to help him out. ;-)

You can of course reconnect (from psql) like this:

from http://www.postgresql.org/docs/8.2/i.../app-psql.html

-----------------------------------------------------------------

\connect (or \c) [ dbname [ username ] [ host ] [ port ] ]

Establishes a new connection to a PostgreSQL server. If the new
connection is successfully made, the previous connection is closed. If
any of dbname, username, host or port are omitted or specified as -, the
value of that parameter from the previous connection is used. If there
is no previous connection, the libpq default for the parameter's value
is used.

If the connection attempt failed (wrong user name, access denied,
etc.), the previous connection will only be kept if psql is in
interactive mode. When executing a non-interactive script, processing
will immediately stop with an error. This distinction was chosen as a
user convenience against typos on the one hand, and a safety mechanism
that scripts are not accidentally acting on the wrong database on the
other hand.
-----------------------------------------------------------------

But I doubt that is wise.
If you do that you'll end up with a PDO connection overruled from the
inside (via \connect command).
I wouldn't be surprised if that gives you a truckload of additional
problems. But I never tried it.

Personally I would use your original setup, and just reconnect despite
the overhead.

Regards,
Erwin Moller

[Jeremy]

Re: PDO: Switch database user without reopening connection

Gordon wrote:

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_us er () 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_us er
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?

SET ROLE is what you want. As long as it is not important for there to
be a layer of security between guest and valid users (i.e. guest must
have an inherent permission to switch to valid, meaning there is
essentially no point to this whole operation.)

http://www.postgresql.org/docs/8.1/interactive/sql-set-role.html

Is the DB on a separate machine, or is it accessed locally? If it's
local, it may surprise you how little penalty there is for creating a
new connection - try profiling and see where it takes you. Obviously
this doesn't apply for a TCP connection.

Jeremy