I am still a little confused as to how the security works in postgres.
I am doing the following: -
1) Creating a unix user 'dpdba' who will effectively be the DBA for the
database
2) Create the postgres user $createuser dpdba (specifying the same unix ID
and all privliges)
3) Change user to dpdba '$su dpdba' and create the database '$createdb
dpstatistics'
4) Now, the bit that confuses me is the setup of the pg_hba.conf file. On
our test machine which is running the same version of both OS and DB, the
password authentification works fine. The entry in the pg_hba.conf for both
machines looks like this: -
local beatfreak password
local dpstatistics password
host all 127.0.0.1 255.255.255.255 password
I am trying to gain access to the database via the interactive monitor (I
assume I am therefore accessing the data as 'local' despite it being a
remote server) and I can get into any database as the postgres superuser (I
can also get in as anyone if I change the setting to trust). The problem
occurs when I try to connect using the 'dpdba' login, I have changed the
password in pg_shadow and still it gives me the same error: -
Connection to database 'dpstatistics' failed.
Password authentication failed for user 'dpdba'
The only differences that I can tell between test and production DB's is the
unix profiles. For some reason the command line in psql acts as though you
are typing in a user name and password. i.e. ^H when you press delete. Could
this possibly have anything to do with it??? The only other difference is
that the postgres files on test are owned by postgres:postgres and on
production are postgres:users.
I have tried all the combos I can think of and have drawn a blank any ideas
would be greatfully recieved.
Cheers
Graham
