Thread: Syntax Help Requested

   I'm working with the XRMS Contact Management System developers to get the
application working with postgres as well as the original MySQL. My role is
trying what they've produced and reporting errors.

   The application runs on a httpd server (apache-1.3.34 here) with any
browser; similar to SQL-Ledger, but xrms is written in php rather than perl.

   I've not yet been able to get last Friday's CVS version to install properly
after I dropped the old database and created a new one. I'm still missing
something on the proper syntax to grant priviledges to these tables to the
application. I'd appreciate some help translating from mysql to postgres
here.

   Following are what the INSTALL file provides for MySQL, and what I've done
in postgresql-8.1.2.

mysql:

Create a MySQL database to hold the XRMS tables, and pick a valid MySQL
account for XRMS to use.

Example:
    from a mysql prompt
    create database xrms;
    grant all privileges on xrms.* to xrms@localhost identified by
'yourpasswordhere';

postgres (from my user account):

createdb contacts
createuser xrms     # This creates the ROLE xrms
grant all on contacts to xrms; # This generates an error at 'to'.

   When I request a list of databases (psql -l), the one named contacts is
there. However, when I open the database (psql contacts), there's no one
home; that is, 'psql -d' returns 'no relations found.'

   I need a clue on how to let user 'xrms' access the database 'contacts' so
the install script runs correctly.

TIA,

Rich

--
Richard B. Shepard, Ph.D.             |  Quantifying subjectivity for the
Applied Ecosystem Services, Inc.(TM)  |  benefit of business and society.
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863

Rich Shepard <rshepard@appl-ecosys.com> writes:
> postgres (from my user account):

> createdb contacts
> createuser xrms     # This creates the ROLE xrms
> grant all on contacts to xrms; # This generates an error at 'to'.

That last is a SQL command, not a shell command, and anyway it would
default to assuming you were trying to grant privileges on a table
named "contacts" not a database named contacts.

What you probably really should do is

    createuser xrms
    createdb --owner=xrms contacts

and go from there.

>    When I request a list of databases (psql -l), the one named contacts is
> there. However, when I open the database (psql contacts), there's no one
> home; that is, 'psql -d' returns 'no relations found.'

You didn't show us any attempt to create anything in contacts ...

>    I need a clue on how to let user 'xrms' access the database 'contacts' so
> the install script runs correctly.

What exactly happens when you try to run the script?  With the default
setup it shouldn't really matter whether xrms is the owner of the
contacts database or not.

            regards, tom lane

>    When I request a list of databases (psql -l), the one named contacts is
> there. However, when I open the database (psql contacts), there's no one
> home; that is, 'psql -d' returns 'no relations found.'
>
>    I need a clue on how to let user 'xrms' access the database 'contacts' so
> the install script runs correctly.

You can't grant all on multiple objects. You have to grant on each
object explicitly.

Joshua D. Drake



>
> TIA,
>
> Rich
>
--

            === The PostgreSQL Company: Command Prompt, Inc. ===
      Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
      Providing the most comprehensive  PostgreSQL solutions since 1997
                     http://www.commandprompt.com/

On Mon, 17 Apr 2006, Tom Lane wrote:

> That last is a SQL command, not a shell command, and anyway it would
> default to assuming you were trying to grant privileges on a table named
> "contacts" not a database named contacts.

Tom,

   I should have been clearer, that I was logged in to psql when I issued the
latter command.

   An off-list suggestion to use the syntax shown in psql's '\h grant' seems
to have worked:
              grant all priviledges on database contacts to xrms;
returned no errors.

> You didn't show us any attempt to create anything in contacts ...

   The install script does this.

> What exactly happens when you try to run the script?

   I get a bunch of error messages. I've passed those on to the xrms
developers.

> With the default setup it shouldn't really matter whether xrms is the owner
> of the contacts database or not.

   I didn't think so.

Thanks,

Rich

--
Richard B. Shepard, Ph.D.             |  Quantifying subjectivity for the
Applied Ecosystem Services, Inc.(TM)  |  benefit of business and society.
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863

On Mon, 17 Apr 2006, Tom Lane wrote:

> You didn't show us any attempt to create anything in contacts ...

   To tie off the end of this thread, the application scripts have mixed case
names as well as no primary key values in some tables. The developers can now
see exactly what changes are necessary to get it fully functional with
postgres, and will do so as their time permits.

Rich

--
Richard B. Shepard, Ph.D.             |  Quantifying subjectivity for the
Applied Ecosystem Services, Inc.(TM)  |  benefit of business and society.
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863