[pkg-horde] Bug#508571: horde3: pgsql create script fails

Karl Nack karlnack at futurityinc.com
Fri Dec 12 17:20:33 UTC 2008

Package: horde3
Version: 3.2.2+debian0-1
Severity: normal
Tags: patch

The create.pgsql.sql script fails (running postgresql 8.3.5). After creating and switching to the horde database, it fails to create new relations with error 
"permission denied for schema public".

The script erroneously grants CREATE on the database, which only allows creation of new schemas:

GRANT CREATE on DATABASE horde to horde;
\c horde horde;

The script should grant CREATE on the schema:

\c horde
SET ROLE horde;

Also, the script should probably run inside a transaction. After the script fails, the user is left the horde user and an empty horde database.

-- System Information:
Debian Release: lenny/sid
  APT prefers testing
  APT policy: (990, 'testing'), (500, 'unstable')
Architecture: amd64 (x86_64)

Kernel: Linux 2.6.26-1-amd64 (SMP w/2 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/bash

Versions of packages horde3 depends on:
ii  apache2                       2.2.9-10   Apache HTTP Server metapackage
ii  apache2-mpm-prefork [httpd]   2.2.9-10   Apache HTTP Server - traditional n
ii  libapache2-mod-php5           5.2.6-5    server-side, HTML-embedded scripti
ii  libjs-scriptaculous           1.8.1-5    JavaScript library for dynamic web
ii  php-log                       1.10.0-1   Log module for PEAR
ii  php-mail                      1.1.14-1   PHP PEAR module for sending email
ii  php-mail-mime                 1.5.2-0.1  PHP PEAR module for creating MIME 
ii  php5-gd                       5.2.6-5    GD module for php5
ii  php5-mcrypt                   5.2.6-5    MCrypt module for php5

Versions of packages horde3 recommends:
pn  fckeditor                     <none>     (no description available)
ii  locales                       2.7-16     GNU C Library: National Language (
ii  logrotate                     3.7.1-5    Log rotation utility
pn  php-date                      <none>     (no description available)
pn  php-db                        <none>     (no description available)
pn  php-file                      <none>     (no description available)
pn  php-services-weather          <none>     (no description available)
ii  php5-cli                      5.2.6-5    command-line interpreter for the p
ii  php5-pgsql                    5.2.6-5    PostgreSQL module for php5
pn  tinymce2 | tinymce            <none>     (no description available)

Versions of packages horde3 suggests:
pn  chora2                        <none>     (no description available)
ii  enscript                      1.6.4-13   Converts ASCII text to Postscript,
ii  gettext                       0.17-4     GNU Internationalization utilities
pn  gollem                        <none>     (no description available)
pn  imp4                          <none>     (no description available)
pn  kronolith2                    <none>     (no description available)
pn  libgeoip1                     <none>     (no description available)
pn  libwpd-tools                  <none>     (no description available)
pn  mnemo2                        <none>     (no description available)
pn  php-net-imap                  <none>     (no description available)
pn  php5-auth-pam                 <none>     (no description available)
pn  php5-mhash                    <none>     (no description available)
pn  ppthtml                       <none>     (no description available)
pn  rpm                           <none>     (no description available)
pn  source-highlight              <none>     (no description available)
pn  turba2                        <none>     (no description available)
pn  unrtf                         <none>     (no description available)
pn  webcpp                        <none>     (no description available)
pn  wv                            <none>     (no description available)
pn  xlhtml                        <none>     (no description available)

-- no debconf information
-------------- next part --------------
-- $Horde: horde/scripts/sql/create.pgsql.sql,v 2008/05/27 14:24:37 mrubinsk Exp $
-- Uncomment the ALTER line below and change the password.  Then run as:
-- $ psql -d template1 -f create.pgsql.sql



\c horde
SET ROLE horde;

CREATE TABLE horde_users (
    user_uid                    VARCHAR(255) NOT NULL,
    user_pass                   VARCHAR(255) NOT NULL,
    user_soft_expiration_date   INTEGER,
    user_hard_expiration_date   INTEGER,
    PRIMARY KEY (user_uid)

CREATE TABLE horde_groups (
    group_uid INTEGER NOT NULL,
    group_name VARCHAR(255) NOT NULL UNIQUE,
    group_parents VARCHAR(255) NOT NULL,
    group_email VARCHAR(255),
    PRIMARY KEY (group_uid)

CREATE TABLE horde_groups_members (
    group_uid INTEGER NOT NULL,
    user_uid VARCHAR(255) NOT NULL

CREATE INDEX group_uid_idx ON horde_groups_members (group_uid);
CREATE INDEX user_uid_idx ON horde_groups_members (user_uid);

CREATE TABLE horde_perms (
    perm_id INTEGER NOT NULL,
    perm_name VARCHAR(255) NOT NULL UNIQUE,
    perm_parents VARCHAR(255) NOT NULL,
    perm_data TEXT,
    PRIMARY KEY (perm_id)

CREATE TABLE horde_prefs (
    pref_uid        VARCHAR(255) NOT NULL,
    pref_scope      VARCHAR(16) DEFAULT '' NOT NULL,
    pref_name       VARCHAR(32) NOT NULL,
    pref_value      TEXT,
    PRIMARY KEY (pref_uid, pref_scope, pref_name)

CREATE INDEX pref_uid_idx ON horde_prefs (pref_uid);
CREATE INDEX pref_scope_idx ON horde_prefs (pref_scope);

CREATE TABLE horde_datatree (
    datatree_id INT NOT NULL,
    group_uid VARCHAR(255) NOT NULL,
    user_uid VARCHAR(255) NOT NULL,
    datatree_name VARCHAR(255) NOT NULL,
    datatree_parents VARCHAR(255) NOT NULL,
    datatree_order INT,
    datatree_data TEXT,
    datatree_serialized SMALLINT DEFAULT 0 NOT NULL,

    PRIMARY KEY (datatree_id)

CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name);
CREATE INDEX datatree_group_idx ON horde_datatree (group_uid);
CREATE INDEX datatree_user_idx ON horde_datatree (user_uid);
CREATE INDEX datatree_order_idx ON horde_datatree (datatree_order);
CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized);
CREATE INDEX datatree_parents_idx ON horde_datatree (datatree_parents);

CREATE TABLE horde_datatree_attributes (
    datatree_id INT NOT NULL,
    attribute_name VARCHAR(255) NOT NULL,
    attribute_key VARCHAR(255),
    attribute_value TEXT

CREATE INDEX datatree_attribute_idx ON horde_datatree_attributes USING HASH(datatree_id);
CREATE INDEX datatree_attribute_name_idx ON horde_datatree_attributes (attribute_name);
CREATE INDEX datatree_attribute_key_idx ON horde_datatree_attributes (attribute_key);
CREATE INDEX datatree_attribute_value_idx ON horde_datatree_attributes (attribute_value);

CREATE TABLE horde_tokens (
    token_address    VARCHAR(100) NOT NULL,
    token_id         VARCHAR(32) NOT NULL,
    token_timestamp  BIGINT NOT NULL,
    PRIMARY KEY (token_address, token_id)

CREATE TABLE horde_vfs (
    vfs_id        BIGINT NOT NULL,
    vfs_type      SMALLINT NOT NULL,
    vfs_path      VARCHAR(255) NOT NULL,
    vfs_name      VARCHAR(255) NOT NULL,
    vfs_modified  BIGINT NOT NULL,
    vfs_owner     VARCHAR(255) NOT NULL,
    vfs_data      TEXT,

    PRIMARY KEY   (vfs_id)

CREATE INDEX vfs_path_idx ON horde_vfs (vfs_path);
CREATE INDEX vfs_name_idx ON horde_vfs (vfs_name);

CREATE TABLE horde_histories (
    history_id       BIGINT NOT NULL,
    object_uid       VARCHAR(255) NOT NULL,
    history_action   VARCHAR(32) NOT NULL,
    history_ts       BIGINT NOT NULL,
    history_desc     TEXT,
    history_who      VARCHAR(255),
    history_extra    TEXT,
    PRIMARY KEY (history_id)

CREATE INDEX history_action_idx ON horde_histories (history_action);
CREATE INDEX history_ts_idx ON horde_histories (history_ts);
CREATE INDEX history_uid_idx ON horde_histories (object_uid);

CREATE TABLE horde_sessionhandler (
    session_id             VARCHAR(32) NOT NULL,
    session_lastmodified   BIGINT NOT NULL,
    session_data           TEXT,
    PRIMARY KEY (session_id)

CREATE TABLE horde_syncml_map (
    syncml_syncpartner VARCHAR(255) NOT NULL,
    syncml_db          VARCHAR(255) NOT NULL,
    syncml_uid         VARCHAR(255) NOT NULL,
    syncml_cuid        VARCHAR(255),
    syncml_suid        VARCHAR(255),
    syncml_timestamp   BIGINT

CREATE INDEX syncml_syncpartner_idx ON horde_syncml_map (syncml_syncpartner);
CREATE INDEX syncml_db_idx ON horde_syncml_map (syncml_db);
CREATE INDEX syncml_uid_idx ON horde_syncml_map (syncml_uid);
CREATE INDEX syncml_cuid_idx ON horde_syncml_map (syncml_cuid);
CREATE INDEX syncml_suid_idx ON horde_syncml_map (syncml_suid);

CREATE TABLE horde_syncml_anchors(
    syncml_syncpartner  VARCHAR(255) NOT NULL,
    syncml_db           VARCHAR(255) NOT NULL,
    syncml_uid          VARCHAR(255) NOT NULL,
    syncml_clientanchor VARCHAR(255),
    syncml_serveranchor VARCHAR(255)

CREATE INDEX syncml_anchors_syncpartner_idx ON horde_syncml_anchors (syncml_syncpartner);
CREATE INDEX syncml_anchors_db_idx ON horde_syncml_anchors (syncml_db);
CREATE INDEX syncml_anchors_uid_idx ON horde_syncml_anchors (syncml_uid);

CREATE TABLE horde_alarms (
    alarm_id        VARCHAR(255) NOT NULL,
    alarm_uid       VARCHAR(255),
    alarm_start     TIMESTAMP NOT NULL,
    alarm_end       TIMESTAMP,
    alarm_methods   VARCHAR(255),
    alarm_params    TEXT,
    alarm_title     VARCHAR(255) NOT NULL,
    alarm_text      TEXT,
    alarm_snooze    TIMESTAMP,
    alarm_dismissed SMALLINT DEFAULT 0 NOT NULL,
    alarm_internal  TEXT

CREATE INDEX alarm_id_idx ON horde_alarms (alarm_id);
CREATE INDEX alarm_user_idx ON horde_alarms (alarm_uid);
CREATE INDEX alarm_start_idx ON horde_alarms (alarm_start);
CREATE INDEX alarm_end_idx ON horde_alarms (alarm_end);
CREATE INDEX alarm_snooze_idx ON horde_alarms (alarm_snooze);
CREATE INDEX alarm_dismissed_idx ON horde_alarms (alarm_dismissed);

CREATE TABLE horde_cache (
    cache_id          VARCHAR(32) NOT NULL,
    cache_timestamp   BIGINT NOT NULL,
    cache_expiration  BIGINT NOT NULL,
    cache_data        TEXT,
    PRIMARY KEY  (cache_id)

CREATE TABLE horde_locks (
    lock_id                  VARCHAR(36) NOT NULL,
    lock_owner               VARCHAR(32) NOT NULL,
    lock_principal           VARCHAR(255) NOT NULL,
    lock_origin_timestamp    BIGINT NOT NULL,
    lock_update_timestamp    BIGINT NOT NULL,
    lock_expiry_timestamp    BIGINT NOT NULL,
    lock_type                SMALLINT NOT NULL,

    PRIMARY KEY (lock_id)

More information about the pkg-horde-hackers mailing list