From 951769e19786631c2773665ac3db90cadd1b453c Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Wed, 29 Sep 2010 15:08:48 +0000 Subject: [PATCH] add support for recapthcha on form to reduce spam --- cgi-bin/pgstatus.pl | 8 ++- cgi-bin/register-form.pl | 26 +++++++ cgi-bin/register.pl | 23 ++++-- cgi-bin/show_history.pl | 2 +- cgi-bin/show_log.pl | 4 +- cgi-bin/show_members.pl | 2 +- cgi-bin/show_status.pl | 5 +- htdocs/index.html | 4 +- schema/bfwebdb.sql | 144 ++++++++++++++++++++++++++++++++++++- templates/bfwrapper.tt | 2 +- templates/dyn/history.tt | 2 +- templates/dyn/status.tt | 2 +- templates/register-form.tt | 111 ++++++++++++++++++++++++++++ 13 files changed, 313 insertions(+), 22 deletions(-) create mode 100755 cgi-bin/register-form.pl create mode 100644 templates/register-form.tt diff --git a/cgi-bin/pgstatus.pl b/cgi-bin/pgstatus.pl index 8743be3..5896761 100755 --- a/cgi-bin/pgstatus.pl +++ b/cgi-bin/pgstatus.pl @@ -74,6 +74,8 @@ $sth->execute($animal); my ($secret)=$sth->fetchrow_array(); $sth->finish; +my $tsdiff = time - $ts; + my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($ts); $year += 1900; $mon +=1; my $date= @@ -84,6 +86,7 @@ if ($ENV{BF_DEBUG} || ($ts > time) || ($ts + 86400 < time ) || (! $secret) ) open(TX,">../buildlogs/$animal.$date"); print TX "sig=$sig\nlogtar-len=" , length($log_archive), "\nstatus=$res\nstage=$stage\nconf:\n$conf\n", + "tsdiff:$tsdiff\n", "changed_this_run:\n$changed_this_run\n", "changed_since_success:\n$changed_since_success\n", "log:\n",$log; @@ -91,7 +94,7 @@ if ($ENV{BF_DEBUG} || ($ts > time) || ($ts + 86400 < time ) || (! $secret) ) close(TX); } -unless ($ts < time) +unless ($ts < time + 120) { my $gmt = gmtime($ts); print "Status: 491 bad ts parameter - $ts ($gmt GMT) is in the future.\n", @@ -227,6 +230,7 @@ if (@config_flags) { @config_flags = grep {! m/=/ } @config_flags; map {s/\s+//g; $_=qq("$_"); } @config_flags; + push @config_flags,'git' if $client_conf->{scm} eq 'git'; $config_flags = '{' . join(',',@config_flags) . '}' ; } @@ -329,7 +333,7 @@ $sth->finish; $db->begin_work; -$db->do("truncate dashboard_mat"); +$db->do("delete from dashboard_mat"); $db->do("insert into dashboard_mat select * from dashboard_mat_data2"); $db->commit; diff --git a/cgi-bin/register-form.pl b/cgi-bin/register-form.pl new file mode 100755 index 0000000..3281a1f --- /dev/null +++ b/cgi-bin/register-form.pl @@ -0,0 +1,26 @@ +#!/usr/bin/perl + +use strict; +use Template; +use Captcha::reCAPTCHA; + +use vars qw( $captcha_pubkey ); +require "$ENV{BFConfDir}/BuildFarmWeb.pl"; + + +my $c = Captcha::reCAPTCHA->new; + +my $captcha = $c->get_html($captcha_pubkey); + + +my $template = new Template({INCLUDE_PATH => '/home/community/pgbuildfarm/templates' }); + +print "Content-Type: text/html\n\n"; + + +$template->process('register-form.tt',{captcha => $captcha}); + + + + + diff --git a/cgi-bin/register.pl b/cgi-bin/register.pl index 3d68cb6..7f8d6fc 100755 --- a/cgi-bin/register.pl +++ b/cgi-bin/register.pl @@ -4,8 +4,10 @@ use strict; use DBI; use Template; use CGI; +use Template; +use Captcha::reCAPTCHA; -use vars qw($dbhost $dbname $dbuser $dbpass $dbport $notifyapp); +use vars qw($dbhost $dbname $dbuser $dbpass $dbport $notifyapp $captcha_pubkey $captcha_privkey); require "$ENV{BFConfDir}/BuildFarmWeb.pl"; #require "BuildFarmWeb.pl"; @@ -37,7 +39,7 @@ my $header = <Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
  • PostgreSQL.org
    • @@ -64,14 +66,23 @@ my $query = new CGI; my $params = $query->Vars; -my ($os, $osv, $comp, $compv, $arch, $email, $owner) = @{$params}{ - qw(os osv comp compv arch email owner)}; +my ($os, $osv, $comp, $compv, $arch, $email, $owner, $challenge, $response ) = @{$params}{ + qw(os osv comp compv arch email owner recaptcha_challenge_field recaptcha_response_field)}; + +my $captcha = Captcha::reCAPTCHA->new; +my $captcha_ok = $captcha->check_answer + ( + $captcha_privkey, + $ENV{'REMOTE_ADDR'}, + $challenge, $response + ); + -unless ($os && $osv && $comp && $compv && $arch && $email && $owner) +unless ($os && $osv && $comp && $compv && $arch && $email && $owner && $captcha_ok->{is_valid}) { print "Content-Type: text/html\n\n", $header, - "

    You need to complete all the form items. Please try again.

    \n", + "

    You need to complete all the form items. Please try again.

    \n", $footer; exit; } diff --git a/cgi-bin/show_history.pl b/cgi-bin/show_history.pl index 98a8860..f600175 100755 --- a/cgi-bin/show_history.pl +++ b/cgi-bin/show_history.pl @@ -101,7 +101,7 @@ __DATA__
  • Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
    • diff --git a/cgi-bin/show_log.pl b/cgi-bin/show_log.pl index 925be5e..cfee571 100755 --- a/cgi-bin/show_log.pl +++ b/cgi-bin/show_log.pl @@ -148,7 +148,7 @@ if ($stage eq 'OK')
  • Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
    • @@ -272,7 +272,7 @@ print <Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
    • diff --git a/cgi-bin/show_members.pl b/cgi-bin/show_members.pl index 341103e..d4b03b2 100755 --- a/cgi-bin/show_members.pl +++ b/cgi-bin/show_members.pl @@ -100,7 +100,7 @@ __DATA__
  • Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
    • diff --git a/cgi-bin/show_status.pl b/cgi-bin/show_status.pl index 8c962f5..59d0e63 100755 --- a/cgi-bin/show_status.pl +++ b/cgi-bin/show_status.pl @@ -110,6 +110,7 @@ __DATA__ xml => '/img/xml.png', 'thread-safety' => '/img/threads.gif', 'integer-datetimes' = '/img/days.png', + git => '/img/git.png', } -%] [%- BLOCK img ; IF flag == 'depend' or flag == 'gnu-ld' ; ; ELSIF flag_imgs.$flag %][% flag %] [% ELSE %][%# @@ -140,7 +141,7 @@ __DATA__
  • Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
  • PostgreSQL.org
    • @@ -159,7 +160,7 @@ __DATA__ [% FOREACH flagset IN flag_imgs %] -[% IF loop.count == 6 %][% END %] +[% IF loop.count == 7 %][% END %] [% END %]
    Legend[% flagset.key %] = [% flagset.key %]

    diff --git a/htdocs/index.html b/htdocs/index.html index 37545f2..0968db1 100644 --- a/htdocs/index.html +++ b/htdocs/index.html @@ -20,7 +20,7 @@
  • Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
    • @@ -38,7 +38,7 @@ To see the current status of tests on various branches, check the

    If you are interested in running a member of the build farm, then please visit -the Registration Page. +the Registration Page. We are particularly interested in unusual platforms or combinations of architecture, operating system and compiler.

    diff --git a/schema/bfwebdb.sql b/schema/bfwebdb.sql index 7e8ae33..2e575b0 100644 --- a/schema/bfwebdb.sql +++ b/schema/bfwebdb.sql @@ -180,6 +180,41 @@ CREATE FUNCTION target(t text) RETURNS text ALTER FUNCTION public.target(t text) OWNER TO pgbuildfarm; +-- +-- Name: transitions(text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm +-- + +CREATE FUNCTION transitions(text, text, text, text, text, text) RETURNS integer + AS $_$ + +my ($os,$osv,$comp,$compv,$arch,$owner) = @_; +# count transitions to and from upper case +my $trans = 1; +my $counttrans = 0; +foreach (split "" ,"$os$osv$comp$compv$arch$owner") +{ + if (/[A-Z]/) + { + next if $trans; + $trans = 1; + $counttrans++; + } + else + { + next unless $trans; + $trans = 0; + $counttrans++; + } +} + +return $counttrans; + +$_$ + LANGUAGE plperl; + + +ALTER FUNCTION public.transitions(text, text, text, text, text, text) OWNER TO pgbuildfarm; + SET default_tablespace = ''; SET default_with_oids = true; @@ -215,7 +250,9 @@ CREATE TABLE build_status ( log_archive bytea, log_archive_filenames text[], build_flags text[], - report_time timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone + report_time timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone, + scm text, + scmurl text ); @@ -263,7 +300,9 @@ CREATE TABLE buildsystems ( sys_owner text NOT NULL, owner_email text NOT NULL, status_ts timestamp without time zone DEFAULT (('now'::text)::timestamp(6) with time zone)::timestamp without time zone, - no_alerts boolean DEFAULT false + no_alerts boolean DEFAULT false, + sys_notes text, + sys_notes_ts timestamp with time zone ); @@ -316,6 +355,57 @@ CREATE VIEW dashboard AS ALTER TABLE public.dashboard OWNER TO pgbuildfarm; +-- +-- Name: dashboard_ex; Type: VIEW; Schema: public; Owner: pgbuildfarm +-- + +CREATE VIEW dashboard_ex AS + SELECT ((timezone('GMT'::text, now()))::timestamp(0) without time zone - b.snapshot) AS when_ago, b.sysname, b.snapshot, b.status, b.stage, b.branch, b.build_flags, s.operating_system, COALESCE(b.os_version, s.os_version) AS os_version, s.compiler, COALESCE(b.compiler_version, s.compiler_version) AS compiler_version, s.architecture, s.sys_notes, (s.sys_notes_ts)::date AS sys_notes_date FROM buildsystems s, (SELECT DISTINCT ON (bs.sysname, bs.branch, bs.report_time) bs.sysname, bs.snapshot, bs.status, bs.stage, bs.branch, bs.build_flags, bs.report_time, p.compiler_version, p.os_version FROM ((build_status bs NATURAL JOIN latest_snapshot m) LEFT JOIN personality p ON (((p.name = bs.sysname) AND (p.effective_date <= bs.report_time)))) WHERE (m.snapshot > (now() - '30 days'::interval)) ORDER BY bs.sysname, bs.branch, bs.report_time, (p.effective_date IS NULL), p.effective_date DESC) b WHERE ((s.name = b.sysname) AND (s.status = 'approved'::text)); + + +ALTER TABLE public.dashboard_ex OWNER TO pgbuildfarm; + +-- +-- Name: dashboard_mat; Type: TABLE; Schema: public; Owner: pgbfweb; Tablespace: +-- + +CREATE TABLE dashboard_mat ( + sysname text, + snapshot timestamp without time zone, + status integer, + stage text, + branch text, + build_flags text[], + operating_system text, + os_version text, + compiler text, + compiler_version text, + architecture text +); + + +ALTER TABLE public.dashboard_mat OWNER TO pgbfweb; + +-- +-- Name: dashboard_mat_data; Type: VIEW; Schema: public; Owner: pgbuildfarm +-- + +CREATE VIEW dashboard_mat_data AS + SELECT b.sysname, b.snapshot, b.status, b.stage, b.branch, b.build_flags, s.operating_system, COALESCE(b.os_version, s.os_version) AS os_version, s.compiler, COALESCE(b.compiler_version, s.compiler_version) AS compiler_version, s.architecture FROM buildsystems s, (SELECT DISTINCT ON (bs.sysname, bs.branch, bs.report_time) bs.sysname, bs.snapshot, bs.status, bs.stage, bs.branch, bs.build_flags, bs.report_time, p.compiler_version, p.os_version FROM ((build_status bs NATURAL JOIN latest_snapshot m) LEFT JOIN personality p ON (((p.name = bs.sysname) AND (p.effective_date <= bs.report_time)))) WHERE (m.snapshot > (now() - '30 days'::interval)) ORDER BY bs.sysname, bs.branch, bs.report_time, (p.effective_date IS NULL), p.effective_date DESC) b WHERE ((s.name = b.sysname) AND (s.status = 'approved'::text)); + + +ALTER TABLE public.dashboard_mat_data OWNER TO pgbuildfarm; + +-- +-- Name: dashboard_mat_data2; Type: VIEW; Schema: public; Owner: pgbuildfarm +-- + +CREATE VIEW dashboard_mat_data2 AS + SELECT b.sysname, b.snapshot, b.status, b.stage, b.branch, CASE WHEN ((b.conf_sum ~ 'use_vpath'::text) AND (b.conf_sum !~ '''use_vpath'' => undef'::text)) THEN (b.build_flags || 'vpath'::text) ELSE b.build_flags END AS build_flags, s.operating_system, COALESCE(b.os_version, s.os_version) AS os_version, s.compiler, COALESCE(b.compiler_version, s.compiler_version) AS compiler_version, s.architecture FROM buildsystems s, (SELECT DISTINCT ON (bs.sysname, bs.branch, bs.report_time) bs.sysname, bs.snapshot, bs.status, bs.stage, bs.branch, bs.build_flags, bs.conf_sum, bs.report_time, p.compiler_version, p.os_version FROM ((build_status bs NATURAL JOIN latest_snapshot m) LEFT JOIN personality p ON (((p.name = bs.sysname) AND (p.effective_date <= bs.report_time)))) WHERE (m.snapshot > (now() - '30 days'::interval)) ORDER BY bs.sysname, bs.branch, bs.report_time, (p.effective_date IS NULL), p.effective_date DESC) b WHERE ((s.name = b.sysname) AND (s.status = 'approved'::text)); + + +ALTER TABLE public.dashboard_mat_data2 OWNER TO pgbuildfarm; + -- -- Name: failures; Type: VIEW; Schema: public; Owner: pgbuildfarm -- @@ -456,6 +546,15 @@ CREATE INDEX bs_sysname_branch_report_idx ON build_status USING btree (sysname, ALTER INDEX public.bs_sysname_branch_report_idx OWNER TO pgbuildfarm; +-- +-- Name: build_status_log_snapshot_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace: +-- + +CREATE INDEX build_status_log_snapshot_idx ON build_status_log USING btree (snapshot); + + +ALTER INDEX public.build_status_log_snapshot_idx OWNER TO pgbuildfarm; + -- -- Name: set_latest_snapshot; Type: TRIGGER; Schema: public; Owner: pgbuildfarm -- @@ -529,7 +628,7 @@ GRANT SELECT ON TABLE build_status_log TO rssfeed; REVOKE ALL ON TABLE buildsystems FROM PUBLIC; REVOKE ALL ON TABLE buildsystems FROM pgbuildfarm; GRANT ALL ON TABLE buildsystems TO pgbuildfarm; -GRANT INSERT,SELECT ON TABLE buildsystems TO pgbfweb; +GRANT INSERT,SELECT,UPDATE ON TABLE buildsystems TO pgbfweb; GRANT SELECT ON TABLE buildsystems TO rssfeed; @@ -564,6 +663,45 @@ GRANT ALL ON TABLE dashboard TO pgbuildfarm; GRANT SELECT ON TABLE dashboard TO pgbfweb; +-- +-- Name: dashboard_ex; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE dashboard_ex FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_ex FROM pgbuildfarm; +GRANT ALL ON TABLE dashboard_ex TO pgbuildfarm; +GRANT SELECT ON TABLE dashboard_ex TO pgbfweb; + + +-- +-- Name: dashboard_mat; Type: ACL; Schema: public; Owner: pgbfweb +-- + +REVOKE ALL ON TABLE dashboard_mat FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_mat FROM pgbfweb; +GRANT ALL ON TABLE dashboard_mat TO pgbfweb; + + +-- +-- Name: dashboard_mat_data; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE dashboard_mat_data FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_mat_data FROM pgbuildfarm; +GRANT ALL ON TABLE dashboard_mat_data TO pgbuildfarm; +GRANT SELECT ON TABLE dashboard_mat_data TO pgbfweb; + + +-- +-- Name: dashboard_mat_data2; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE dashboard_mat_data2 FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_mat_data2 FROM pgbuildfarm; +GRANT ALL ON TABLE dashboard_mat_data2 TO pgbuildfarm; +GRANT SELECT ON TABLE dashboard_mat_data2 TO pgbfweb; + + -- -- PostgreSQL database dump complete -- diff --git a/templates/bfwrapper.tt b/templates/bfwrapper.tt index cb95ef9..75cd7a6 100644 --- a/templates/bfwrapper.tt +++ b/templates/bfwrapper.tt @@ -25,7 +25,7 @@ a> atus
  • Members
    • -
  • Register
  • PGFo undry
    • diff --git a/templates/dyn/history.tt b/templates/dyn/history.tt index 402a932..4713047 100644 --- a/templates/dyn/history.tt +++ b/templates/dyn/history.tt @@ -22,7 +22,7 @@
  • Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
    • diff --git a/templates/dyn/status.tt b/templates/dyn/status.tt index 17ee2ac..f17e415 100644 --- a/templates/dyn/status.tt +++ b/templates/dyn/status.tt @@ -22,7 +22,7 @@
  • Home
  • Status
  • Members
    • -
  • Register
    • +
  • Register
  • PGFoundry
  • PostgreSQL.org
    • diff --git a/templates/register-form.tt b/templates/register-form.tt new file mode 100644 index 0000000..0f3b289 --- /dev/null +++ b/templates/register-form.tt @@ -0,0 +1,111 @@ + + + + + PostgreSQL BuildFarm Application + + + + + +
    + +
    +

    Application to join PostgreSQL BuildFarm

    + +

    Here is a short description of what is required to join the buildfarm successfully. Please read it carefully +before submitting this form.

    + +
      +
    • your machine will need to be able to contact http://www.pgbuildfarm.org + either directly or via proxy, and it will need access to a PostgreSQL CVS repository, + either the one at postgresql.org or a mirror (you can set up your own mirror using CSVup on a Linux or FreeBSD machine - + this is recommended).
      • +
    • have a working Postgresql build environment for your platform + (for Windows this means MSys/MinGW with the libz and libintl stuff, and ideally native Python and Tcl).
      • +
    • Windows only: you will need a native perl installed as well as the one in the MSys DTK. The one from ActiveState works fine.
      • +
    • download and unpack the latest release of client code from + http://pgfoundry.org/frs/?group_id=1000040
      • +
    • read instructions at + http://pgfoundry.org/docman/view.php/1000040/4/PGBuildFarm-HOWTO.txt
      • +
    • get the software running locally using flags --force --nostatus --nosend
      • +
    • register your machine on this page
      • +
    • when you receive credentials, put them in the config file, and schedule regular builds (without those flags) + for the branches you want to support - which should be at least HEAD and the most recent stable branch.
      • +
    + +

    There is also some extra information in this +article about buildfarm on the O'Reilly network.

    + +
    + +

    Please complete all items.

    +

    For Linux, please specify the name and version of the Distribution for the Operating Systems items. +Do not use the name "Linux". For example, for my test machine the Operating +Systems is "Fedora Core" and the version is "4".

    +
    + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
    Operating System
    OS Version
    Compiler
    Compiler Version
    Architecture
    Your name
    Your email address
    [% captcha %]
    +
    +
    +
    +

    +Hosting for the PostgreSQL Buildfarm is generously +provided by: +CommandPrompt, +The PostgreSQL Company +

    +
    + + + + -- 2.30.2