Re: Question about permissions on database. - Mailing list pgsql-general
From | Condor |
---|---|
Subject | Re: Question about permissions on database. |
Date | |
Msg-id | 19f1c996c9abc955b8699a458a5db756@stz-bg.com Whole thread Raw |
In response to | Re: Question about permissions on database. (Ryan Kelly <rpkelly22@gmail.com>) |
List | pgsql-general |
On 2012-09-22 23:47, Ryan Kelly wrote: > On Sat, Sep 22, 2012 at 11:35:00PM +0300, Condor wrote: >> Hello, >> I wanna ask: is there a short way to giver permission to one user to >> select/insert (all privileges) on whole database ? >> Im create a user and try to give him all permission on existing >> database, but when I try to select always got: >> ERROR: permission denied for relation table_name >> >> I simple do: >> GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; >> and when I do that my idea and what Im trying to do is to give all >> privileges on for select, insert, update ... using sequences, exec >> functions >> to one user, but when I try to select, I receive error message: >> ERROR: permission denied for relation table_name >> >> I look at documentation and remained less scarred about how many >> grants I should do for tables, for sequences, execution. >> Im using postgresql 9.2 > You don't want to GRANT on the database. That doesn't do what you > think > it does. You, however, can do: > > GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user; Before I write first email I try this query: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user; but result was the same error: ERROR: permission denied for relation table_name > > This is documented clearly here: > http://www.postgresql.org/docs/9.2/static/sql-grant.html > > This is generally a bad idea. > > You can alternatively make the user a super user: > > ALTER ROLE your_user WITH SUPERUSER; > > But this is an even worse idea. > > If one role owns all the tables in that database, you can make your > role > a member of that role: > > GRANT owner_role TO your_role; > > But are you really sure that your user needs permissions on > everything? Yes, well not for everything but for 90% of the tables. The person before me use postgres user in front end apache to storing and fetching data from tables. Front end work with whole database, insert new data on tables, fetching. I want to replace usage of postgres super user. Last night when read documentation I understand it exact as what David J explain in second mail on the list. Why I say "little scared" because tables in database is over 150 plus sequences .. almost 200 objects. Giving separate permission on every single table to user, this it's seems bad idea for me, because in future if I need to change something or add/remove new tables I always should give permissions and if I forgot front end will display errors. From other side is a very good that I can say what user in which tables and fields have permissions, but it's seems in this case I can't use it. Last think that coming in my mind is to change owner to all tables in database my_db from current user (postgres) to my new user (my_user). Any advice what I can do ? Cheers, C
pgsql-general by date: