[MKSearch-dev] Postgres authentication

[Phil Shaw]

Chris,

This starts out as an open window for typing notes into. Hopefully, 
it'll come to something useful.

First thing is that if you use the -h argument with the psql command 
line client to specify a host, you get the same error.

psql -h localhost mksearch_test
psql -h 127.0.0.1 mksearch_test

Both give: FATAL: Ident authentication failed for user "phil"

I adapted DatabaseStoreManager to use fully qualified database 
connection URLs, so we can change the host and port configuration if 
necessary.

I edited /var/lib/pgsql/data/pg_hba.conf and added this line:

host all all 127.0.0.1 255.255.255.255 trust

This still restricts remote access to the database, but is more 
liberal. It's good enough for the testing. The server needs a re-
start.

su postgres

bash-3.00$ pg_ctl reload -D /var/lib/pgsql/data
postmaster signalled

The test script/class then makes a connection successfully with user 
phil and no password (ident).

It is better to use a separate user account with password 
authentication and more limited rights. Use createuser with the -P 
flag to assign a password:

$ createuser webapp -P
Enter password for new user:
Enter it again:
Shall the new user be allowed to create databases (y/n) n
Shall the new user be allowed to create more users (y/n) n
CREATE USER

Then log in to the default database template to check

$ psql template1

# select * from pg_shadow;


Then check password access

$ psql template1 -U webapp -W

Fails because we do not have password authentication configured. You 
need to edit the pg_hba.conf file.

$ su
Password:

# vi /var/lib/pgsql/data/pg_hba.conf

Add the line:

host all all 127.0.0.1 255.255.255.255 password

(You can be more specific with the users and databases later.)

# su postgres

bash-3.00$ pg_ctl reload -D /var/lib/pgsql/data
postmaster signaled
bash-3.00$ exit
# exit

$ psql template1 -h localhost -U webapp -W
Password:

This is successful with the psql client, but doesn't work with the 
JDBC driver test. There is some progress, the postmaster confirms 
password authentication is active for this connection attempt:

FATAL: password authentication failed for user "webapp".

Turns out this was because of a coding error in the test class -- 
using the database url for the password! Corrected and re-compiled 
this works too.

...
Preparing to get a database connection.
Database connection successful.

Still the JSpider indexer does not load the database driver, but this 
was because of a classpath error in the java-jspider-pgsql.sh and 
java-jspider-pgsql.sh scripts, now corrected.

You'll need to update your working copy and re-compile with:

$mk_home/bin/jar-mksearch.sh

All the storage tables are created and owned by webapp, so the Tomcat 
database configuration must specify a user that has password 
authentication and the right to read those tables, e.g. webapp.

It turns out the war-mksearch.sh script needed an additional line to 
copy any database driver JAR in /WEB-INF/lib into the WAR, now 
checked in. Just copy the relevant database driver JAR into 

$mk_home/src/app/WEB-INF/lib

Then run $mk_home/bin/war-mksearch.sh and follow the documentation on 
Tomcat on FC4.

Success! I have indexed the MKSearch test site and deployed to Tomcat 
using database storage on FC4.

Best regards,

Phil



 











--
MKSearch (beta)

http://www.mksearch.mkdoc.org/

Free, open source metadata search engine with RDF storage and query.