From: | "Greg Cocks" <gcocks(at)stoller(dot)com> |
---|---|
To: | "PostgreSQL List - Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super user'... |
Date: | 2007-11-30 19:58:08 |
Message-ID: | 66F6CF82BF58CE4DB4285BE816B297E83548DA@tribble.SMStoller.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice pgsql-odbc |
Hello,
Yep, a newbie, at least to PostgreSQL + ODBC / MapServer... :-)
I have:
- built and populated a PostgreSQL database (including PostGIS, in case that matters in this case)
- set up ODBC (using psqlodbc-08_02_0500), using the Unicode version as a System DNS
- successfully connected to the tables in PostgreSQL from an Access 'front end' I built, updated tables, etc, etc
The user in PostgreSQL/ODBC was a super user - lets call that user FRED...
Wanting now to 'lock this down' a bit security-wise, I:
- set FRED as *not* being a Super User
- made a new group role, lets call that grpWrite
- assigned (sic) FRED to grpWrite
- set the GRANT permissions on all the non-system tables to be SELECT, INSERT, DELETE and UPDATE (took me a bit to find and use that function!), so the grpWrite privileges on each non-system table reads 'arwdx'
- *tested FRED with phpPgAdmin - works just as expected*, full read write access to the data - but NOT things such as vacuum, etc
- checked the TEST on my ODBC driver, 'CONNECTION SUCCESSFUL'
When I go to the Access 'front end' now, I can refresh all the tables in the Linked Table Manager (suggest the CONNECT is A-OK) but when I try and view data in a table, etc I get the error in MS Access:
ODBC--call failed
ERROR: permission denied for relation <table_name>;
Error while executing the query (#7)
Tried, with no luck:
- setting the GRANT on the group role to include REFERENCES
- opening the MS Access database on the PostgreSQL server
- as a last resort, setting the GRANT in grpWrite to ALL
The minute I change FRED back to being a Super User, works like a charm...
** Suggestions and experiences gratefully accepted! **
Note that MapServer has the same need for FRED (sic) to be a Super User...
Thanks in advance!
----------
Regards,
GREG COCKS
GIS Analyst V
Gcocks |at| stoller.com
S. M. Stoller Corp
105 Technology Drive, Suite 190
Broomfield, CO 80021
www.stoller.com
303-546-4300
303-443-1408 fax
303-546-4422 direct
303-828-7576 cell
From | Date | Subject | |
---|---|---|---|
Next Message | Obe, Regina | 2007-12-03 12:23:09 | Re: Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super user'... |
Previous Message | Greg Cocks | 2007-11-30 19:54:49 | Group Roles are now Login Roles in pgAdmin? |
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Inoue | 2007-12-02 08:25:47 | Re: possible ODBC bug with '-infinity' |
Previous Message | Richard Broersma Jr | 2007-11-30 18:12:18 | Re: possible ODBC bug with '-infinity' |