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.
