https://wiki.davical.org/index.php?title=PostgreSQL_Config&feed=atom&action=historyPostgreSQL Config - Revision history2024-03-29T10:07:57ZRevision history for this page on the wikiMediaWiki 1.40.1https://wiki.davical.org/index.php?title=PostgreSQL_Config&diff=3573&oldid=prevFenton: /* Changes to pg_hba.conf to allow the 'davical_dba' and 'davical_app' users to connect */ Removed link to Database/Users (obsolete)2015-09-18T18:56:47Z<p><span dir="auto"><span class="autocomment">Changes to pg_hba.conf to allow the 'davical_dba' and 'davical_app' users to connect: </span> Removed link to Database/Users (obsolete)</span></p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 19:56, 18 September 2015</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l14">Line 14:</td>
<td colspan="2" class="diff-lineno">Line 14:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"></del></div></td><td colspan="2" class="diff-side-added"></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;">See [[Database/Users]] for more information about setting up the 'davical_app' and 'davical_dba' users.</del></div></td><td colspan="2" class="diff-side-added"></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== Installing with a remote database ==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== Installing with a remote database ==</div></td></tr>
<!-- diff cache key davical:diff::1.12:old-3451:rev-3573 -->
</table>Fentonhttps://wiki.davical.org/index.php?title=PostgreSQL_Config&diff=3451&oldid=prevGorka: copied information from orphaned page 'users'2015-06-29T16:57:48Z<p>copied information from orphaned page 'users'</p>
<p><b>New page</b></p><div>{{Languages|PostgreSQL Config}}<br />
Some things to check in your PostgreSQL configuration are: <br />
<br />
== pg_hba.conf ==<br />
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:<br />
* administration of the database schema and static lookup tables<br />
* application connectivity to the database<br />
<br />
Since 0.9.3 of DAViCal the database creation script sets these up by default as:<br />
* davical_dba<br />
* davical_app<br />
<br />
=== Changes to pg_hba.conf to allow the 'davical_dba' and 'davical_app' users to connect ===<br />
<br />
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.<br />
<br />
See [[Database/Users]] for more information about setting up the 'davical_app' and 'davical_dba' users.<br />
<br />
== Installing with a remote database ==<br />
<br />
The create-database.sh script assumes a database on the same server as the web application. A [http://davical-general.89287.n3.nabble.com/Changes-to-create-database-sh-that-allow-for-remote-host-creation-td892388.html 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:<br />
<br />
createuser -U postgres davical_dba<br />
createuser -U postgres davical_app<br />
<br />
and a database: <br />
<br />
createdb -U postgres -O davical_dba davical_YOURORG<br />
<br />
(where YOURORG is some sort of tag, optional, in case you might want to host multiple instances of davical). Set some passwords:<br />
<br />
$ psql -U postgres davical_YOURORG<br />
psql (8.4.9)<br />
Type "help" for help.<br />
<br />
postgres=# ALTER USER davical_dba WITH PASSWORD 'YOURPASSWORDHERE';<br />
postgres=# ALTER USER davical_app WITH PASSWORD 'YOURPASSWORDHERE';<br />
<br />
Now, back on your webserver make sure you can connect as davical_dba:<br />
<br />
psql -U davical_dba -h DATABASE_HOST davical_YOURORG<br />
<br />
and if that's good, you can start loading the SQL.<br />
<br />
Assuming default paths, substitute your values for caps in the following:<br />
<br />
psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/awl/dba/awl-tables.sql <br />
psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/awl/dba/schema-management.sql<br />
psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/davical/dba/davical.sql <br />
<br />
Now, run the update-davical-database script, which does know how to contact a remote database:<br />
<br />
/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<br />
<br />
And then finish up loading the SQL:<br />
<br />
psql -qXAt -U davical_dba -h DATABASE_HOST davical_YOURORG < /usr/share/davical/dba/base-data.sql <br />
psql -qX -U davical_dba -h DATABASE_HOST -c "UPDATE usr SET password = '**YOURADMINPASSWORD' WHERE user_no = 1;" davical_YOURORG<br />
<br />
(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]].<br />
<br />
== Installations prior to 0.9.4 ==<br />
<br />
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.<br />
<br />
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.<br />
<br />
===Changes to pg_hba.conf to allow the 'general' user to connect===<br />
<br />
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.</div>Gorka