Обсуждение: Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super user'...

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
findand 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
isA-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

 Its strange that it works in PhpPgAdmin unless you aren't really logging in as Fred as you think you are.

Assuming your privledges are set correctly on your group, I would suspect that maybe you don't have Fred set to inherit
rightsfrom parent roles.
 

That threw me for a loop the first time I saw it that you can have a role that is not set to inherit rights from its
parentroles.  So in order for it to use the rights of its parent, it has to do a set role or be set to inherit.
 

So to make sure a login is set to inherit rights from its parent role, make sure you have something like

CREATE ROLE fred LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT mapservgroup TO fred;

Hope that helps,
Regina

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Greg Cocks
Sent: Friday, November 30, 2007 2:58 PM
To: PostgreSQL List - Novice
Subject: [NOVICE] Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super user'...

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
findand 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
isA-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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

Regina,

Perfect - that was it...  fixed it up for both MapServer and ODBC...

With hindsight, this seems logical of course - but wasn't not made clear in the reference text I am using, at least to
me("PostgreSQL 8 for Windows")) - part of the whole newbie thing I guess...   :-) 

(Confirmed that I was logging in as FRED (sic) to phpPgAdmin without inheritance, just tested... hmmm... more to
learn!)

(aside - the three *group* roles I set up for my main 'production' database have somehow 'migrated' to *login* roles
whenI look at them after restating the Widows service and reopening pgAdmin - but still seem to be working as group
roles(I think...) - pgAdmin v1.8.0 revision dated 10/19/07 and PostgreSQL ) 


Cheers:
GREG COCKS
gcocks |at| stoller.com



--------------------------------

-----Original Message-----
From: Obe, Regina [mailto:robe.dnd@cityofboston.gov]
Sent: Monday, December 03, 2007 5:23 AM
To: Greg Cocks; PostgreSQL List - Novice
Subject: RE: [NOVICE] Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super
user'...


 Its strange that it works in PhpPgAdmin unless you aren't really logging in as Fred as you think you are.

Assuming your privledges are set correctly on your group, I would suspect that maybe you don't have Fred set to inherit
rightsfrom parent roles. 

That threw me for a loop the first time I saw it that you can have a role that is not set to inherit rights from its
parentroles.  So in order for it to use the rights of its parent, it has to do a set role or be set to inherit. 

So to make sure a login is set to inherit rights from its parent role, make sure you have something like

CREATE ROLE fred LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT mapservgroup TO fred;

Hope that helps,
Regina

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Greg Cocks
Sent: Friday, November 30, 2007 2:58 PM
To: PostgreSQL List - Novice
Subject: [NOVICE] Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super user'...

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
findand 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
isA-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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

I don’t remember changing those… it is maybe because my ‘production’ database is in the PUBLIC schema??

 

(would like to change this as I have now read that PUBLIC schemas have ‘default’ user rights for the logged in user rights that overrides those that – but have (manually) emulated the relationships in the ODBC-linked tables in MS Access - to help in Query Manager, etc - and don’t want to set that up again, at least not right now! [aside – I am thinking a long term ‘project’ would be to write some VBA in Access that grabs/tests (??) all the PK/FK relationships in the linked tables and sets them up in the relationships in Access ‘automagically’…; and then maybe if I am feeling even braver something that sets the lookup display controls in the Access linked tables based on the FKs… although maybe they would not save on linked tables?])

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Ask me about our expert PostgreSQL & PostGIS Training delivered worldwide.