Thread: Permission Problems
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
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
On 11/23/2011 01:54 PM, Bill Thoen wrote: > 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. If you are just letting people view, I would create views of what is allowed to be viewed and grant read permission to just the views to the unprivalged account. JD > > TIA, > - Bill Thoen > -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579
Am 23.11.2011 22:54, schrieb Bill Thoen: > 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. The information schema gives you only access to objects the user has usage rights for. That means you have to grant rights for the objects you want to list for your unprivileged user. If you want these information without these restrictions you must use the tables of the pg_catalog schema. Sven