Skip site navigation (1) Skip section navigation (2)

Re: Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super user'...

From: Chander Ganesan <chander(at)otg-nc(dot)com>
To: Greg Cocks <gcocks(at)stoller(dot)com>
Cc: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>, PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Can't get MS Access via ODBC (or MapServer) to 'see' the data unless the user is a 'super user'...
Date: 2008-02-01 03:01:13
Message-ID: 47A28B79.3080800@otg-nc.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-odbc
Greg Cocks wrote:  
>
> I don't remember changing those... it is maybe because my 'production' 
> database is in the PUBLIC schema??
>
The the schema that your tables are in wouldn't have any effect on the 
"system" rights granted to roles on the server (such as the login 
privilege or inheritance).
>
>  
>
> (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?])
>
It's true that all users that can connect to a database have the CREATE 
right in the public schema, but you can revoke that right from public 
(and it's quite easy...(revoke create on schema public from public;), 
and if you want this to be applied by default for all new databases, 
just issue it in the template1 databases (this assumes that you always 
use template1 as your template) .  Also, keep in mind that once an 
object is created in a schema, the owner is the only one with rights to 
that object...so while you could have rogue users creating objects 
(tables, sequences, indexes, etc.), they wouldn't be able to access 
tables (or data in those tables) unless rights had been granted to them.

As such, the fact that your tables (a database is a superset of a 
schema, so your tables would be created in the public schema inside your 
database) are in the public schema shouldn't be much of an issue unless 
you granted the public role rights to them.

-- 
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.

In response to

pgsql-novice by date

Next:From: Andres LedesmaDate: 2008-02-01 05:19:31
Subject: Re: Protecting a web app from Postgresql injection
Previous:From: Chander GanesanDate: 2008-02-01 02:47:23
Subject: Re: Group Roles are now Login Roles in pgAdmin?

pgsql-odbc by date

Next:From: Hiroshi SaitoDate: 2008-02-01 18:55:06
Subject: psqlODBC 08.03.0100 released.
Previous:From: Raymond O'DonnellDate: 2008-01-29 15:29:25
Subject: Re: VB6 - convertion from Access database to PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group