Can"t get ODBC to work unless the connection user is a "superuser"...

Hello,

Yep, a newbie, at least to PostgreSQL + ODBC... :-)

I have:

- built and populated a PostgreSQL database (including PostGIS, in case th=
at 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 superuser - lets call that user FRED...


Wanting now to lock this down a bit I security-wise, I:

- set FRED as *not* being a SuperUser

- 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, INS=
ERT, DELETE and UPDATE (took me a bit to find and use that function!), so t=
he grpWrite privileges on each non-system table reads 'arwdx'=20

- tested FRED with phpPgAdmin - works just as expected, full read write ac=
cess 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 th=
e Linked Table Manager (suggest the CONNECT is A-OK) but when I try and vie=
w data in a table, etc I get the error in MS Access:

ODBC--call failed
ERROR: permission denied for relation ;
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 SuperUser, works like a charm...

** Suggestions and experiences gratefully accepted! **

Note that MapServer has the same need for FRED to be a SuperUser...

Thanks in advance!


----------
Regards,
GREG COCKS
GIS Analyst V
Gcocks |a| 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=A0cell


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org