| Author |
Switching user within connection.
|
|
| Rob Kirkbride 2005-11-23, 7:24 am |
| Hi,
Is there a standard way (or indeed a special way in Postgres) to switch
user/password without creating a new connection?
You can obviously do it within psql with \c but I wondered whether there
was a way in JDBC?
Thanks for any help,
Rob
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Oliver Jowett 2005-11-23, 7:24 am |
| Rob Kirkbride wrote:
> Is there a standard way (or indeed a special way in Postgres) to switch
> user/password without creating a new connection?
> You can obviously do it within psql with \c but I wondered whether there
> was a way in JDBC?
There is no standard JDBC way I know of, short of reconnecting.
AFAIK there's no way at the protocol level to reauthenticate as a
different user on an existing connection. I believe psql's \c command
actually establishes a new connection.
If you originally connected as the database superuser you could use SET
SESSION AUTHORIZATION to get a similar effect; see
http://www.postgresql.org/docs/8.1/...orization.html.
-O
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Rob Kirkbride 2005-11-23, 7:24 am |
| Oliver Jowett wrote:
> Rob Kirkbride wrote:
>
>
>
> There is no standard JDBC way I know of, short of reconnecting.
>
> AFAIK there's no way at the protocol level to reauthenticate as a
> different user on an existing connection. I believe psql's \c command
> actually establishes a new connection.
>
> If you originally connected as the database superuser you could use
> SET SESSION AUTHORIZATION to get a similar effect; see
> http://www.postgresql.org/docs/8.1/...orization.html.
>
>
Thanks for your reply. I think I'd guessed that would be the reply. It's
probably off topic really now but I wanted to maintain a series of
pooled connections but with potentially different users. I got lost
reading the JCA documentation which may do something clever but I'm
guessing if the underlying JDBC driver doesn't support it then there's
not a lot it can do really.
Rob
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Rob Kirkbride 2005-11-23, 7:24 am |
|
smitha wrote:
> Hi Rob !!
>
> Just now I got ur Query. I will check it. I have doubt. If
> You know the answer for this Please reply to me. I am breaking my head
> to solve this.
>
> This is my Java Program. I get connected with the Postgresql Database
> and the query get executed and it affects in the Database.....A new
> user is created in the Database.. But i am getting the Exception "No
> result were returned by the query" what could be the reason.....Plz
> tell me
>
>
> // .java - example connection to a Postgresql (Database)
>
> import java.io.*;
> import java.sql.*;
>
> public class connect
> {
> public static String villageName = null;
> public static final boolean DEBUG = false;
>
> public static void main (String[] args)
> {
> //Ge t a Connection to Postgres
> try
> {
> Class.forName("org.postgresql.Driver");
> }
> catch (ClassNotFoundExcept
ion e)
> {
> System.err.println( "Driver not found: " + e + "\n" +
> e.getMessage() );
> }
> try
> {
> String url="jdbc:postgresql://10.163.2.95:5432/collabland";
> Connection
> con=DriverManager.getConnection(url,"postgres","postgres");*
> Statement st=con.createStatement();
>
> String logName = "kala";
> String passwd = "kala";
>
> String query1 = "CREATE user " + logName + " with password '" +
> passwd + "' createuser";
> ResultSet rs= st. executeQuery(query1)
;
> System.out.println(query1);
> System.out.println("user created");
> }
> catch (SQLException sqlex)
> {
> if (DEBUG) sqlex.printStackTrace();
> System.out.println(sqlex.getMessage());
> // return false;
> }
> System.out.println("after catch block");
>
> }
> }
>
> *
(Correct top post - also please don't email off list)
I use the following syntax :
create user 'username' encrypted password 'password'
It works ok for me providing I don't try and use a prepared statement -
it doesn't seem to translate the $1 correctly in the username.
Rob
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Oliver Jowett 2005-11-23, 7:24 am |
| Rob Kirkbride wrote:
> Thanks for your reply. I think I'd guessed that would be the reply. It's
> probably off topic really now but I wanted to maintain a series of
> pooled connections but with potentially different users. I got lost
> reading the JCA documentation which may do something clever but I'm
> guessing if the underlying JDBC driver doesn't support it then there's
> not a lot it can do really.
My JCA is a bit rusty, but from memory the way you deal with this is by
implementing ManagedConnectionFac
tory. matchManagedConnecti
ons() to only
consider connections that have matching authentication info. So the
container can maintain a single pool of connections, but when it asks
the connector for a suitable connection authenticated as Joe then only
connections in the pool that are already authenticated as Joe are
considered.
You may end up needing more connections in the pool depending on the
actual usage pattern, but you don't need to reauthenticate on an
existing connection.
-O
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Rob Kirkbride 2005-11-23, 9:26 am |
| Oliver Jowett wrote:
> Rob Kirkbride wrote:
>
>
>
> My JCA is a bit rusty, but from memory the way you deal with this is
> by implementing ManagedConnectionFac
tory. matchManagedConnecti
ons() to
> only consider connections that have matching authentication info. So
> the container can maintain a single pool of connections, but when it
> asks the connector for a suitable connection authenticated as Joe then
> only connections in the pool that are already authenticated as Joe are
> considered.
>
> You may end up needing more connections in the pool depending on the
> actual usage pattern, but you don't need to reauthenticate on an
> existing connection.
>
Thanks for you help - I see yes what you've given would be helpful.
However I'm struggling at the moment to work out how to configure JCA on
JBoss using Hibernate. But I guess that's a question for another forum!
Thanks again Oliver.
Rob
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
|
|
|