[Server-devel] PostgreSQL + Moodle

Myles Carrick mylescarrick at gmail.com
Sun Jun 29 03:58:49 EDT 2008


hi Tim,

Mate you posted in your setup of Moodle:

"looks like the package already created /var/www/moodle/data with
appropriate permissions so above was not necessary"

It's really important for security that the moodledata directory NOT be in
the webroot like this, otherwise Moodle's roles & permissions system doesn't
control access to any course files & materials - they all become public.
Make sure you're using something like that folder you created earlier in
your procedure (/var/moodledata).

Myles C.

Myles S Carrick
Software Services
Sydney Centre for Innovation in Learning (SCIL)


2008/6/25 Tim Moody <timmoody at sympatico.ca>:

>  Hi Tony,
>
> Here are my notes minus a lot of wrong turns.  The part that probably needs
> Martin's help is PAM SOTP.  I made postgresql trusted because the default
> ident setting did not work.
>
>  Postgre
>
> ·        Find and run PostgreSQL
>
> o          psql (default install) is not postgresql-server; it's just the
> client
>
> o          Yum install postgresql-server (8.2.5-1.fc7)
>
> o          service postgresql initdb – OK
>
> o          service postgresql start – OK
>
> o          psql – not work as root
>
> o          su – postgres
>
> o          psql – connects
>
> o          \l shows postgres, template0, template1 databases
> PHP & Libs
>
> ·        yum install php – installed: php.i386 0:5.2.4-1.fc7 (surprised it
> is not in default install)
>
> o          Make sure php.conf added to /etc/httpd/conf.d
>
> o          /etc/php.ini now found
>
> o          service httpd restart
>
> ·        Test PHP
>
> o          Create php script as follows and execute; it should dump a lot
> about your php environment
>
> o          <?php
>
> o          phpinfo();
>
> o          ?>
>
> o          In my environment this is in /var/www/html/timtest/ =
> http://192.168.0.160/timtest/info.php
>
> ·        yum install postgresql-python
>
> o          Installed: postgresql-python.i386 0:8.2.5-1.fc7
>
> o          Dependency Installed: mx.i386 0:2.0.6-3
>
> o          Don't know what this is for
>
> ·        yum install php-pgsql
>
> o          Installed: php-pgsql.i386 0:5.2.4-1.fc7
>
> o          Service httpd restart
>
> o          Phpinfo now shows pgsql
>
> ·        Test php access to postgre
>
> o          create database timdb;
>
> o          \c timdb
>
> o          create table test (text varchar(10), num decimal);
>
> o          insert into test values ('abc', 123);
>
> o          select * from test;
>
> o          run testpg.php:
>
> o          <?php
>
> o          // Connecting, selecting database
>
> o          $dbconn = pg_connect("host=localhost port = 5432 dbname=timdb
> user=postgres password=xxx")
>
> o              or die('Could not connect: ' . pg_last_error());
>
> o
>
> o          // Performing SQL query
>
> o          $query = 'SELECT * FROM test';
>
> o          $result = pg_query($query) or die('Query failed: ' .
> pg_last_error());
>
> o
>
> o          // Printing results in HTML
>
> o          echo "<table>\n";
>
> o          while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
>
> o              echo "\t<tr>\n";
>
> o              foreach ($line as $col_value) {
>
> o                  echo "\t\t<td>$col_value</td>\n";
>
> o              }
>
> o              echo "\t</tr>\n";
>
> o          }
>
> o          echo "</table>\n";
>
> o
>
> o          // Free resultset
>
> o          pg_free_result($result);
>
> o
>
> o          // Closing connection
>
> o          pg_close($dbconn);
>
> o          ?>
>
> ·        Figure out why php won't connect to postgresql
>
> o          can not connect and no error (so php not working)
>
> o          log is in /var/lib/pgsql/data/pg_log
>
> o          LOG:  could not connect to Ident server at address "127.0.0.1",
> port 113: Connection refused
>
> o          FATAL:  Ident authentication failed for user "postgres"
>
> o          pg_hba.conf has sameuser which is supposed to mean that the
> linux and db user names are the same
>
> o          Steve <ksobrien AT pcisys.net>04 Jan 2006 0:50:43
>
> o          Even though you add wide-open access permission in the
> pg_hba.conf file like this:  host all all 127.0.0.1/32  trust
>
> o          You will still be unable to connect to the database with a PHP
> (or whatever language) program and an Apache httpd server unless you add an
> "apache" (or whatever user your httpd daemon is running as) user to
> PostgreSQL thus:
>
> o          su - postgres
>
> o          createuser apache
>
> o          Of course, be sure you GRANT SELECT ON table TO APACHE; to give
> the apache user permission to actually read data.
>
> o          I assigned pw postgres to user postgres
>
> o          Modified /var/lib/pgsql/data/ pg_hba.conf to change host    all
> all         127.0.0.1/32          ident sameuser to
>
> o          host    all         all         127.0.0.1/32          trust
>
> o          run testpg.php – works
>
> o          BOTTOM LINE – I could only get this to work if I used trusted
> mode.  I know Martin specified PAM SOTP, but I don't know how this works
> Moodle
>
> ·        Install Moodle
>
> o
> http://docs.moodle.org/en/Step-by-step_Install_Guide_for_Ubuntu#Install_Postgresql_.28skip_MySQL.29
>
> o
> http://docs.moodle.org/en/Installing_Moodle#Creating_an_empty_database
>
> o          # su - postgres
>
> o            > psql -c "create user moodleuser createdb;" template1 –
> worked
>
> o            > psql -c "create database moodle with encoding 'unicode';"
> -U moodleuser template1 – failed on authentication (Ident authentication
> failed for user "moodleuser")
>
> o          createdb -E utf8 -O moodleuser moodle – worked
>
> o            > psql -c "alter user moodleuser nocreatedb;" template1
> (alter user moodleuser nocreatedb;) – worked
>
> o            > psql -c "alter user moodleuser with encrypted password
> 'moodle';" template1 (alter user moodleuser with encrypted password
> 'moodle';) – worked
>
> o            > su - root
>
> o            # /etc/init.d/postgresql reload
>
> ·        Finish moodle install
>
> o          Modified /etc/php.ini according to
> http://docs.moodle.org/en/Installing_Moodle (docs seem to have 0 and 1
> where file has off and on; I mostly followed the docs, which worked.)
>
> o          Created /var/moodledata (NOT NECESSARY)
>
> o          chown -R nobody:apache moodledata
>
> o          chmod -R 0770 moodledata
>
> o          looks like the package already created /var/www/moodle/data
> with appropriate permissions so above was not necessary
>
> o          /var/www/moodle/web/config.php has mysql as db – I changed to
> postgres
>
> o           http://192.168.0.160/moodle/<http://192.168.0.160/moodle/web/install.php>- Error: Database connection failed
>
> o          modified host, dbname, user, and password in config.php
>
> o          now does redirect to http://localhost/moodle/admin/index.php)because wwwroot
> = 'http://localhost/moodle'June <http://localhost/moodle%27June> 20, 2008
>
> o          now  http://192.168.0.160/moodle/<http://192.168.0.160/moodle/web/install.php>redirects to
> http://192.168.0.160/moodle/admin/index.php - gives error DB Type:
> postgres not supported by XMLDDB
>
> o          click continue – repeats error
>
> o          N.B> changed dbtype to 'postgres7' (!?) – now gets past error
> (didn't read fine print)
>
> o          Database was successfully upgraded
>
> o          (should have taken unattended installation)
>
> o          workshop tables have been set up correctly
>
> o          truefalse tables have been set up correctly
>
> o          Database was successfully upgraded (x2)
>
> o          Various tables
>
> o          Create admin (admin/moodle)
>
> o          Seems to be installed and running.
>
> ·        Set up cron
>
> o          This still needs to be done
>
>
>
> Tim
>
>
> ----- Original Message -----
> *From:* Tony Pearson <tpearson at us.ibm.com>
> *To:* Tim Moody <timmoody at sympatico.ca>
> *Sent:* Tuesday, June 24, 2008 1:08 PM
> *Subject:* PostgreSQL + Moodle
>
>
> Tim,
> Please share me your procedures.  If you have written down any notes, or
> have any "gotcha's" I should try to avoid, please provide me.  I will be
> doing this week to send the computer off this Monday.
>
>
> From: "Tim Moody" <timmoody at sympatico.ca>
> Subject: Re: [Server-devel] XS SW
> To: <server-devel at lists.laptop.org>
> Message-ID: <000701c8d58e$4f981b60$0b00a8c0 at saturn>
> Content-Type: text/plain; format=flowed; charset="iso-8859-1";
>                 reply-type=original
>
> Got throught the moodle + postgre install.  I'll now play with setting up
> some courses and users.
>
> I cheated on the authentication as I don't know how PAM SOTP works; I just
> set the authentication to trusted.
>
>     *Tony Pearson*
> Senior Storage Consultant, IBM System Storage™
> Telephone: +1 520-799-4309 |  tie 321-4309 |  Cell: +1 520 990-8669
> email: tpearson at us.ibm.com |  GSA: http://tucgsa.ibm.com/~tpearson<http://tucgsa.ibm.com/%7Etpearson>
> Blog: *http://www.ibm.com/developerworks/blogs/page/InsideSystemStorage*<http://www.ibm.com/developerworks/blogs/page/InsideSystemStorage>
> AKA: 990tony Paravane, eightbar specialist
>
>
> _______________________________________________
> Server-devel mailing list
> Server-devel at lists.laptop.org
> http://lists.laptop.org/listinfo/server-devel
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.laptop.org/pipermail/server-devel/attachments/20080629/b027ce7c/attachment-0001.htm 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 3808 bytes
Desc: not available
Url : http://lists.laptop.org/pipermail/server-devel/attachments/20080629/b027ce7c/attachment-0001.jpeg 


More information about the Server-devel mailing list