Обсуждение: Switching user within connection.
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
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/static/sql-set-session-authorization.html. -O
Oliver Jowett wrote: > 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/static/sql-set-session-authorization.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
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 (ClassNotFoundException 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
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 ManagedConnectionFactory.matchManagedConnections() 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
Oliver Jowett wrote: > 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 ManagedConnectionFactory.matchManagedConnections() 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