Re: authentication

Hello

I am using postgres in the cygwin environment. I have two services set up -
one launches it with no networking and one with networking so that it runs
on port 5432. Both are fine in terms of the service and I can connect with
psql with both. However, I need to connect with a JDBC connection. I can get
the JDBC connection to work with networking but not without. SInce my Java
app always runs locally, I would like to leave the networking off.

The connection parameter for networking (all OK with this one)
<init-param url="jdbc:postgresql://localhost:5432/template1"/>

With no networking, it is as follows (doesn't connect)
<init-param url="jdbc:postgresql:template1"/>

I have read threads elsewhere which seem to suggest that it can be made to
work like this - any ideas anyone ?

The second part to this is about authentication. I would like to connect
with a low-privileged user, but all users should use a password. This is my
only entry in /usr/share/postgresql/pg_hba.conf

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all password

WHatever change I make to this, psql never prompts when running on the same
machine. Does cygwin support pg_hba.conf ? If I use psql with -U and -W, I
get prompted for a password but it lets me in whatever I type.

Thanks for ANY help or pointers.

Chris

Chris,

You can't do what you are trying to do with java. Java does not support
a non-portable concept like unix sockets. So the only way to connect to
postgres from jdbc is through tcpip.

The two jdbc connection URLs you give below are identical in
functionality. If you don't specify the machine and port in the URL it
uses the default values of localhost and 5432.

thanks,
--Barry

Chris Faulkner wrote:
> Hello
>
> I am using postgres in the cygwin environment. I have two services set up -
> one launches it with no networking and one with networking so that it runs
> on port 5432. Both are fine in terms of the service and I can connect with
> psql with both. However, I need to connect with a JDBC connection. I can get
> the JDBC connection to work with networking but not without. SInce my Java
> app always runs locally, I would like to leave the networking off.
>
> The connection parameter for networking (all OK with this one)
> <init-param url="jdbc:postgresql://localhost:5432/template1"/>
>
> With no networking, it is as follows (doesn't connect)
> <init-param url="jdbc:postgresql:template1"/>
>
> I have read threads elsewhere which seem to suggest that it can be made to
> work like this - any ideas anyone ?
>
> The second part to this is about authentication. I would like to connect
> with a low-privileged user, but all users should use a password. This is my
> only entry in /usr/share/postgresql/pg_hba.conf
>
> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
> local all all password
>
> WHatever change I make to this, psql never prompts when running on the same
> machine. Does cygwin support pg_hba.conf ? If I use psql with -U and -W, I
> get prompted for a password but it lets me in whatever I type.
>
> Thanks for ANY help or pointers.
>
> Chris
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Hello

Thanks for the feedback. MySQL offers the ability to disable TCP/IP
networking and use named pipes. It would be nice if postgres offered the
same. They claim improved performance but I am not sure of the security
implications, although removing an open tcp/ip port feels safer.

Chris

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Barry Lind
Sent: 23 September 2003 17:01
To: Chris Faulkner
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] authentication

Chris,

You can't do what you are trying to do with java. Java does not support
a non-portable concept like unix sockets. So the only way to connect to
postgres from jdbc is through tcpip.

The two jdbc connection URLs you give below are identical in
functionality. If you don't specify the machine and port in the URL it
uses the default values of localhost and 5432.

thanks,
--Barry

Chris Faulkner wrote:
> Hello
>
> I am using postgres in the cygwin environment. I have two services set
up -
> one launches it with no networking and one with networking so that it runs
> on port 5432. Both are fine in terms of the service and I can connect with
> psql with both. However, I need to connect with a JDBC connection. I can
get
> the JDBC connection to work with networking but not without. SInce my Java
> app always runs locally, I would like to leave the networking off.
>
> The connection parameter for networking (all OK with this one)
> <init-param url="jdbc:postgresql://localhost:5432/template1"/>
>
> With no networking, it is as follows (doesn't connect)
> <init-param url="jdbc:postgresql:template1"/>
>
> I have read threads elsewhere which seem to suggest that it can be made to
> work like this - any ideas anyone ?
>
> The second part to this is about authentication. I would like to connect
> with a low-privileged user, but all users should use a password. This is
my
> only entry in /usr/share/postgresql/pg_hba.conf
>
> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
> local all all password
>
> WHatever change I make to this, psql never prompts when running on the
same
> machine. Does cygwin support pg_hba.conf ? If I use psql with -U and -W, I
> get prompted for a password but it lets me in whatever I type.
>
> Thanks for ANY help or pointers.
>
> Chris
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Chris Faulkner writes:

> I am using postgres in the cygwin environment. I have two services set up -
> one launches it with no networking and one with networking so that it runs
> on port 5432.

You're going to have a pretty hard time connecting to a PostgreSQL server
without networking. Or what is your definition of no networking?

> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
> local all all password

> WHatever change I make to this, psql never prompts when running on the same
> machine.

Perhaps you're not connecting through a Unix-domain socket, but instead
via TCP/IP?

--
Peter Eisentraut peter_e(at)gmx(dot)net

By no-networking I meant using --tcip_socket=false. I think this was
accurate. When you run postgres with this option and use unix domain sockets
and connect with psql or pgAdmin, you are connecting to a postgres server
without networking. These work under cygwin, but not from a Java client.

"UNIX domain sockets communicate only between processes on a single host.
Sockets in the UNIX domain are not considered part of the network protocols
because they can be used to communicate only between processes on a single
host."

I guess I brought the vocabulary from MySQL were you use "skip networking"
and the server allows you to connect on the local machine with named pipes,
even with JDBC.

Chris

-----Original Message-----
From: Peter Eisentraut [mailto:peter_e(at)gmx(dot)net]
Sent: 23 September 2003 20:58
To: Chris Faulkner
Cc: pgsql-cygwin(at)postgresql(dot)org; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [CYGWIN] authentication

Chris Faulkner writes:

> I am using postgres in the cygwin environment. I have two services set
up -
> one launches it with no networking and one with networking so that it runs
> on port 5432.

You're going to have a pretty hard time connecting to a PostgreSQL server
without networking. Or what is your definition of no networking?

> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
> local all all
password

> WHatever change I make to this, psql never prompts when running on the
same
> machine.

Perhaps you're not connecting through a Unix-domain socket, but instead
via TCP/IP?

--
Peter Eisentraut peter_e(at)gmx(dot)net

"Chris Faulkner" <chrisf(at)oramap(dot)com> writes:
> I guess I brought the vocabulary from MySQL were you use "skip networking"
> and the server allows you to connect on the local machine with named pipes,
> even with JDBC.

Just out of curiosity, what do they use to support that on the client
side? We were recently told that there is no pure-Java access to
Unix sockets (a/k/a named pipes), because it's not a portable OS feature.
Do they have a type 4 JDBC driver that handles this?

regards, tom lane

Chris Faulkner writes:

> By no-networking I meant using --tcip_socket=false. I think this was
> accurate. When you run postgres with this option and use unix domain sockets
> and connect with psql or pgAdmin, you are connecting to a postgres server
> without networking. These work under cygwin, but not from a Java client.

The PostgreSQL JDBC driver cannot connect via Unix-domain sockets, on any
operating system. So if you want to use that interface, you need to
enable TCP/IP connections.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Tom Lane writes:

> Just out of curiosity, what do they use to support that on the client
> side? We were recently told that there is no pure-Java access to
> Unix sockets (a/k/a named pipes), because it's not a portable OS feature.
> Do they have a type 4 JDBC driver that handles this?

In my book, named pipes are FIFOs, so they'd use normal file system
operations.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Chris Faulkner wrote:

> Hello
>
> I am using postgres in the cygwin environment. I have two services set up -
> one launches it with no networking and one with networking so that it runs
> on port 5432. Both are fine in terms of the service and I can connect with
> psql with both. However, I need to connect with a JDBC connection. I can get
> the JDBC connection to work with networking but not without. SInce my Java
> app always runs locally, I would like to leave the networking off.
>
> The connection parameter for networking (all OK with this one)
> <init-param url="jdbc:postgresql://localhost:5432/template1"/>
>
> With no networking, it is as follows (doesn't connect)
> <init-param url="jdbc:postgresql:template1"/>
>
> I have read threads elsewhere which seem to suggest that it can be made to
> work like this - any ideas anyone ?
____________________________________________________________
JDBC SUPPORTING LOCAL/INTERNAL CONNECTIONS

I'm afraid I can't speak on this subject. Local connections require the
use of Unix sockets. Does JDBC support this? 'fraid I'm a bit out of
my element on this end.

If JDBC requires a TCP/IP connection (or if you can't get it to work
otherwise), it's fairly simple to configure PostgreSQL to only permit
TCP/IP connections from localhost.

> The second part to this is about authentication. I would like to connect
> with a low-privileged user, but all users should use a password. This is my
> only entry in /usr/share/postgresql/pg_hba.conf
>
> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
> local all all password
>
> WHatever change I make to this, psql never prompts when running on the same
> machine. Does cygwin support pg_hba.conf ? If I use psql with -U and -W, I
> get prompted for a password but it lets me in whatever I type.
____________________________________________________________
PG_HBA.CONF

Yes, PostgreSQL under Cygwin supports pg_hba.conf. PostgreSQL runs
under Cygwin like it would under any *nix, and pg_hba.conf file is a
config file for PostgreSQL, so not really a Cygwin support issue.

That said, I notice you mentioned the file was in

/usr/share/postgresql

If you installed PostgreSQL using the usual Cygwin setup.exe (i.e., you
did NOT build from source), this is the directory where the templates
are located. There is no pg_hba.conf file there by default, other than
the sample file pg_hba.conf.sample.

When you did your 'initdb -D' command and specified/initialized a
datastore, THAT directory is where you will find the file you need to
tweak. If you followed Jason's README, the file you want is in

/usr/share/postgresql/data

Next: The config you have is fine for local/internal connections, but
does not cover TCP/IP connections. My config allows for either
local/internal connections or TCP/IP connections on the loopback
interface, and the file has these lines:
____________________________________________________________
...
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all password
host all all 127.0.0.1 255.255.255.255 password
____________________________________________________________

____________________________________________________________
POSTGRESQL USERS/PASSWORDS

Most likely PostgreSQL is letting you in no matter what you type as your
password because unless you took steps to do otherwise, your PostgreSQL
user accounts do not HAVE passwords. Do a 'man createuser' for details
on how to set when adding users from the command line, but in short, use

$ createuser -P [newusername]

and you'll be prompted to provide a password for the user you are
creating. Note it can get confusing when executing these commands
whether you're typing in the password of the user you are connecting to
PostgreSQL as (indicated by the -U flag) OR the password to give the new
user being created. For example, type

$ createuser -U postgres -P bubba

and you'll likely be prompted for user postgres' password first (can't
do PostgreSQL stuff without logging in first!), then later you'll be
prompted for what password to give user bubba.

As for the default user 'postgres' created when you initialized
PostgreSQL, run 'psql' and type the following command for help:

\h alter user

To set a password for user 'postgres', you can do something in psql like

ALTER USER postgres WITH PASSWORD 'somepassword';
^^^^^^^^^^^^^^ single quotes NEEDED

Note in PostgreSQL that internal information like PostgreSQL users are
stored just like any of your database info; that is, in tables...which
begin "pg_". A full list is available by typing

\dS

The table that stores PostgreSQL users is 'pg_user', and typing the SQL
query

SELECT * FROM pg_user;

might help you understand.

Anyway, hope this Illiad helped. Apologies in advance for the length. :-)

In response to your first question - I have no idea ! In my case, the client
side is on the same machine. Initially, I was using the resin MySQL driver
which didn't connect when I was using named pipes. I switched to Connector/J
from mysql.com and it then worked.

Chris

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: 23 September 2003 23:35
To: Chris Faulkner
Cc: Peter Eisentraut; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] [CYGWIN] authentication

"Chris Faulkner" <chrisf(at)oramap(dot)com> writes:
> I guess I brought the vocabulary from MySQL were you use "skip networking"
> and the server allows you to connect on the local machine with named
pipes,
> even with JDBC.

Just out of curiosity, what do they use to support that on the client
side? We were recently told that there is no pure-Java access to
Unix sockets (a/k/a named pipes), because it's not a portable OS feature.
Do they have a type 4 JDBC driver that handles this?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Hello

I am using postgres on Linux and cygwin. ARe the database files in PGDATA
compatible between the two platforms ? I am familiar with pg_dump but would
like to transfer the files from the system itself. The database is quite
large and I am in a position where I can do this after shutting both
databases down. This mechanism is documented in section 9.2 of the
administrators guide but I just wondered whether there are any particular
issues because these files are being transferred from Linux<->cygwin.

Thanks

Chris

Chris Faulkner writes:

> I am using postgres on Linux and cygwin. ARe the database files in PGDATA
> compatible between the two platforms ?

The internal layout of database files depend mostly on your compiler
(regarding struct layout), CPU (endianness, etc.), and configuration
options (block size, etc.). If you're sure you have all those the same
you can try it.

--
Peter Eisentraut peter_e(at)gmx(dot)net