Permission Problems

From: Bill Thoen <bthoen(at)gisnet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Permission Problems
Date: 2011-11-23 21:54:42
Message-ID: 4ECD6BA2.9080005@gisnet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm trying to put together a very simple web application to display
information about any table in my database that a web user wants to see.
The general idea is to present a list of schemata and their associated
tables to the user who then picks one, which causes the server to send
the list of fields in that table. Pretty simple.

My thought was to use an internal, low-privileged account to do the
look-up and pass the requested info back to the client, but I think I'm
running into permission problems and I don't know where to add all the
"GRANTS" so that the go-between account has enough privilege to do its
job, but not more.

I'm getting the list of schemata from the information_schema.schemata
table, and using my superuser account it works fine. However, using the
account I've set up for this job isn't getting very far and I'm getting
nothing returned. I've granted permissions for SELECT and REFERENCES on
all of my tables, and granted USEAGE on the schemas including
information_schema and pg_catalog and the relevant views; I've even
granted execute priv on the functions used in the view, but I'm still
not getting results for this psuedo user, even though the SQL selection
works fine for my account. I'd prefer not to just hand out a superuser
privilege to the database go-between because my purpose is to keep this
db-web interface role's reach short.

Am I digging too deep here, or what am I missing? Is there a better way
to tranfer info between my database and the web than by using a generic
account? It sure seems like I'm granting too much access to too little a
player. Any advice would be welcome.

TIA,
- Bill Thoen

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2011-11-23 21:57:41 Re: Permission Problems
Previous Message Jerry Sievers 2011-11-23 21:50:19 Re: plpgsql Difference in behaviour between versions?