Re: authentication - Mailing list pgsql-cygwin

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. :-)