Log in

No account? Create an account
Grant question. - PostgreSQL [entries|archive|friends|userinfo]
PostgreSQL Users

[ website | PostgreSQL Web site ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Grant question. [Jun. 6th, 2007|01:32 pm]
PostgreSQL Users



I have a user. let's call him "websitedev". I also have a database called "events" which was created by user "postgres". I have a php application that this user "websitedev" is able to connect to the database, the problem is it doesn't have permissions to access the tables.

grant all privileges on database events to websitedev with grant option;

But when I try to connect via the website application I am still getting.

Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: permission denied for relation tablename in path/to/file.php on line 59



[User Picture]From: pauamma
2007-06-06 05:59 pm (UTC)
Did you read http://www.postgresql.org/docs/7.4/static/client-authentication.html (or similar for whichever PostgreSQL version you're using)
(Reply) (Thread)
[User Picture]From: thenetimp
2007-06-06 06:10 pm (UTC)
Yes. The database is on the same server as the webapplication by default it trusts all local connections/users, so it's not the "pg_hba.conf".

Also just look at the error I provided. It's not saying that it can't connect to the database. It's saying it can't access the table in the database so I can't see where this info might help.
(Reply) (Parent) (Thread)
[User Picture]From: mattcwood
2007-06-06 06:13 pm (UTC)
PostgreSQL doesn't have the built-in ability to grant permissions to all the tables in a database at once. Check out this link to find a function that will grant rights to all the tables at once:


(Reply) (Thread)
[User Picture]From: thenetimp
2007-06-06 06:49 pm (UTC)
Hrm, I tried that script and it didn't seem to do anything, but doing individual grants on each tables. Now to solve my other db related problems, thanks for the info.
(Reply) (Parent) (Thread)
[User Picture]From: decibel45
2007-06-06 09:09 pm (UTC)
BTW, when you need to do stuff like this, querying the catalog tables can be very handy, and just form the query so that it outputs commands; ie:

SELECT 'GRANT SELECT ON ' || schemaname || '.' || tablename || ' TO public;' FROM pg_tables.
(Reply) (Parent) (Thread)
[User Picture]From: mattcwood
2007-06-07 03:29 pm (UTC)
That's basically what the function is doing, but it also executes the GRANT statements.

My favorite trick when querying the catalog tables is to use pg_constrint_def() in order to get a list of FK definitions that can be applied all at once.

(Reply) (Parent) (Thread)