PostgreSQL Config

From Davical
Jump to: navigation, search
Help
Available languages

Some things to check in your PostgreSQL configuration are:

pg_hba.conf

The PostgreSQL pg_hba.conf file is used to control access to the PostgreSQL database. For security reasons it is good practice to have different database users for:

  • administration of the database schema and static lookup tables
  • application connectivity to the database

Since 0.9.3 of DAViCal the database creation script sets these up by default as:

  • davical_dba
  • davical_app

Changes to pg_hba.conf to allow the 'davical_dba' and 'davical_app' users to connect

The pg_hba.conf file is parsed from top to bottom by PostgreSQL and the first matching rule is the one that applies. This means that if you want to add a rule allowing the 'davical_app' and 'davical_dba' users access to the DAViCal database you probably need to add it fairly early in the file, rather than appending it at the bottom.

Installing with a remote database

The create-database.sh script assumes a database on the same server as the web application. A variant of create-database.sh that can handle remote hosts was created by Marek Jawurek but has bit-rotted a bit since. If your PostgreSQL database is not on the same host as your webserver, you have two choices. First, you can install the awl and davical packages on the database server and use the create-database.sh script directly. If that's not the best solution for you, you'll need to do a bit of manual work to get things going (basically doing the script's duties by hand). These instructions assume some familiarity with PostgreSQL administration. First, on the database server, create your users:

 createuser -U postgres davical_dba
 createuser -U postgres davical_app

and a database:

 createdb -U postgres -O davical_dba davical_YOURORG

(where YOURORG is some sort of tag, optional, in case you might want to host multiple instances of davical). Set some passwords:

 $ psql -U postgres davical_YOURORG
 psql (8.4.9)
 Type "help" for help.
 
 postgres=# ALTER USER davical_dba WITH PASSWORD 'YOURPASSWORDHERE';
 postgres=# ALTER USER davical_app WITH PASSWORD 'YOURPASSWORDHERE';

Now, back on your webserver make sure you can connect as davical_dba:

 psql -U davical_dba -h DATABASE_HOST davical_YOURORG

and if that's good, you can start loading the SQL.

Assuming default paths, substitute your values for caps in the following:

 psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/awl/dba/awl-tables.sql 
 psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/awl/dba/schema-management.sql
 psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/davical/dba/davical.sql 

Now, run the update-davical-database script, which does know how to contact a remote database:

 /usr/share/davical/dba/update-davical-database --dbname davical_YOURORG --dbuser davical_dba --dbhost DATABASE_HOST --dbpass YOURPASSWORDHERE --appuser davical_app --nopatch --owner davical_dba

And then finish up loading the SQL:

 psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/davical/dba/base-data.sql 
 psql -qX -U davical_dba -h DATABASE_HOST -c "UPDATE usr SET password = '**YOURADMINPASSWORD' WHERE user_no = 1;" davical_YOURORG

(and yes, the two asterisks are literal and required). Be sure to set the same values in your config file as per Configuration/Database Settings.

Installations prior to 0.9.4

In installations prior to 0.9.4 the database user used by the application was called 'general', rather than 'davical_app'. Installations prior to 0.9.3 the default database name was 'rscds' rather than 'davical'. These changes should be taken into consideration when reading the current documentation.

If your database is currently named in this way then there should really be no need to change it. You will very likely want to create a configuration file for the update-davical-database program however.

Changes to pg_hba.conf to allow the 'general' user to connect

The pg_hba.conf file is parsed from top to bottom by PostgreSQL and the first matching rule is the one that applies. This means that if you want to add a rule allowing the user 'general' access to the RSCDS database you probably need to add it fairly early in the file, rather than appending it at the bottom.