Обсуждение: Question about permissions on database.
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 C.
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; 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? -Ryan Kelly
On Sep 22, 2012, at 16:35, Condor <condor@stz-bg.com> 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 > Each object type needs to be handled independently. A database is a distinct object type with its own actions to allow/disallow(mainly connect, usage?). You need to issue similar commands for schemas, tables, functions, etc... Would suggest creating group (i.e., non-login) roles and grant to those then make the user (i.e., login) roles a member ofthe appropriate group role(s). David J.
On Sun, Sep 23, 2012 at 6:47 AM, Ryan Kelly <rpkelly22@gmail.com> 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 > 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; > > 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. I have a similar situation; I want to have a userid for doing the regular backups, which therefore (for pg_dump) needs SELECT privilege on all tables. Is: GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_userid; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_userid; the right way to do this? (Obviously I need to also do this for every other schema I use.) Alternatively, is there some better way to do backups? I'd rather not snapshot the entire data directory; we burn to disc, so a larger backup requires more media and slower backup/restore process. ChrisA
On 09/23/2012 04:35 AM, Condor wrote: > I look at documentation and remained less scarred about how many grants > I should do for tables, for sequences, execution. You probably need to read this: http://stackoverflow.com/questions/11599533/postgresql-8-4-grant-dml-privileges-on-all-tables-to-a-role That while that question is about 8.4 so it doesn't cover ALTER DEFAULT PRIVILEGES (http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html), which is the right way to to solve this going forward. It should be useful, though. -- Craig Ringer
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