Re: Question about permissions on database.

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