Thread: Strange Grant behavior in postgres 8.3
Version: PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1) 4.3.2 I have a DB "foo" created and owned by postgres. No I created another role called "bar" and with the user postgres in the db foo I did: #> grant all on foo to bar; when I select from pg_database I can see the correct line bar=CTc/postgres for the db foo I login with user bar to foo $ psql -U bar -h localhost foo but I cannot select anything from any table. Now the strange part. I grant to one table only a all privileges #> grant all on table nodes to bar $> \z nodes Schema | Name | Type | Access privileges --------+-------+-------+--------------------------------------------------- public | nodes | table | {postgres=arwdxt/postgres,foo=arwdxt/postgres} and then the user bar could read from all tables. What am I doing wrong? Or where can I look if I missed something -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Manager ] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703 Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclaimer@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates.
Schwaighofer Clemens wrote: > Version: > PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real(Debian 4.3.2-1) 4.3.2 > > I have a DB "foo" created and owned by postgres. > > No I created another role called "bar" and with the user postgres in > the db foo I did: > > #> grant all on foo to bar; That statement is wrong. It should be: GRANT ALL ON DATABASE foo TO bar; > when I select from pg_database I can see the correct line > > bar=CTc/postgres for the db foo > > I login with user bar to foo > > $ psql -U bar -h localhost foo > > but I cannot select anything from any table. That is ok, because you have no permissions on schemata and tables. > Now the strange part. > > I grant to one table only a all privileges > #> grant all on table nodes to bar > > $> \z nodes > Schema | Name | Type | Access privileges > --------+-------+-------+--------------------------------------------------- > public | nodes | table | {postgres=arwdxt/postgres,foo=arwdxt/postgres} That is very strange, because the user in your example is "bar" and not "foo". > and then the user bar could read from all tables. That is unlikely. > What am I doing wrong? Or where can I look if I missed something It seems that some of your statements or results are different from what actually happened. Try to reproduce it with a new clean database, and then copy and paste what you did and what results you got so that we can reproduce the behaviour. Yours, Laurenz Albe
Sorry for some confusion. I re-created the whole thing again with fresh users and a fresh database: (1) Create a new user and a new db, also create a table 'test' inside with user 'foo' $> createuser -U postgres -P -E foo $> createdb -U postgres -O foo -E utf8 foo_test (2) create a second user $> createuser -U postgres -P -E bar just a check for db ownership => \l List of databases Name | Owner | Encoding ----------------------+--------------+---------- foo_test | foo | UTF8 (3) check the database rights (with user postgres) # select * from pg_database where datname = 'foo_test'; datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl ----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+-------- foo_test | 72427 | 6 | f | t | -1 | 11510 | 379 | 1663 | | (1 row) (4) login as user bar and get correct error => select * from test; ERROR: permission denied for relation test; (5) give user 'bar' full rights to the database 'foo_test' as logged in with user 'postgres' # grant all on database foo_test to bar; (6) check rights again datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl ----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+----------------------------------- foo_test | 72427 | 6 | f | t | -1 | 11510 | 379 | 1663 | | {=Tc/foo,foo=CTc/foo,bar=CTc/foo} (7) login again with 'bar' user => select * from test; ERROR: permission denied for relation test still cannot select rights for the table inside (the same for postgres, foo or bar user): # \z Access privileges for database "foo_test" Schema | Name | Type | Access privileges --------+------+-------+------------------- public | test | table | So what do I do wrong? Even if I do the GRANT command as user 'foo' who is the database owner, I still cannot select with the user 'bar'. It only works if I set GRANT rights for the TABLE itself: as user 'foo' logged in => grant all on table test to bar; => \z Access privileges for database "foo_test" Schema | Name | Type | Access privileges --------+------+-------+------------------- public | test | table | {foo=arwdxt/foo,bar=arwdxt/foo} and then login again with user 'bar' => select * from test; test ------ foo (1 row) I am seriously confused and ask myself what I do wrong On Tue, Feb 17, 2009 at 21:06, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Schwaighofer Clemens wrote: >> Version: >> PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real(Debian 4.3.2-1) 4.3.2 >> >> I have a DB "foo" created and owned by postgres. >> >> No I created another role called "bar" and with the user postgres in >> the db foo I did: >> >> #> grant all on foo to bar; > > That statement is wrong. > It should be: > > GRANT ALL ON DATABASE foo TO bar; > >> when I select from pg_database I can see the correct line >> >> bar=CTc/postgres for the db foo >> >> I login with user bar to foo >> >> $ psql -U bar -h localhost foo >> >> but I cannot select anything from any table. > > That is ok, because you have no permissions on schemata and tables. > >> Now the strange part. >> >> I grant to one table only a all privileges >> #> grant all on table nodes to bar >> >> $> \z nodes >> Schema | Name | Type | Access privileges >> --------+-------+-------+--------------------------------------------------- >> public | nodes | table | {postgres=arwdxt/postgres,foo=arwdxt/postgres} > > That is very strange, because the user in your example is "bar" and not "foo". > >> and then the user bar could read from all tables. > > That is unlikely. > >> What am I doing wrong? Or where can I look if I missed something > > It seems that some of your statements or results are different > from what actually happened. > > Try to reproduce it with a new clean database, and then copy and > paste what you did and what results you got so that we can reproduce > the behaviour. > > Yours, > Laurenz Albe > -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Manager ] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703 Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclaimer@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates.
Schwaighofer Clemens wrote: > So what do I do wrong? Even if I do the GRANT command as user 'foo' > who is the database owner, I still cannot select with the user 'bar'. > It only works if I set GRANT rights for the TABLE itself: > > as user 'foo' logged in > => grant all on table test to bar; > that is correct. DATABASE privileges relate to connecting to the database, permissions to create objects and so forth. each object in the database has its own access rights. for typical application use, I create teh database so the primary application account owns the database, then let that account create all the tables so it owns those too. $ sudo -u postgres createuser someuser $ sudo -u postgres createdb -o someuser somedb then access this database with that user to create the tables and such
On 02/18/2009 01:15 PM, John R Pierce wrote: > Schwaighofer Clemens wrote: >> So what do I do wrong? Even if I do the GRANT command as user 'foo' >> who is the database owner, I still cannot select with the user 'bar'. >> It only works if I set GRANT rights for the TABLE itself: >> >> as user 'foo' logged in >> => grant all on table test to bar; >> > > that is correct. DATABASE privileges relate to connecting to the > database, permissions to create objects and so forth. > > > each object in the database has its own access rights. > > for typical application use, I create teh database so the primary > application account owns the database, then let that account create all > the tables so it owns those too. > > $ sudo -u postgres createuser someuser > $ sudo -u postgres createdb -o someuser somedb > > then access this database with that user to create the tables and such I see, normally I always create a user that owns the DB, so I don't have those problems. But yesterday I run in some issues with table ownership and thought if I just give the user all rights for the DB, he should have all rights to the tables too. The other problem is, that there is no "grant all on table db.* ..." but I have to do that for each table seperate, or in a "grant all on table a, b, ...". I am not sure if there is an easier way, except perhaps through a select from the pg_ catalog for this db and get the table names there ... -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706 Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ]
Attachment
Clemens Schwaighofer wrote: > The other problem is, that there is no "grant all on table db.* ..." but > I have to do that for each table seperate, or in a "grant all on table > a, b, ...". > > I am not sure if there is an easier way, except perhaps through a select > from the pg_ catalog for this db and get the table names there ... > I believe there are some scripts that can help.. http://pgedit.com/public/sql/acl_admin/index.html http://unf.be/postgresql/postgres_grant_all.perl http://www.archonet.com/pgdocs/grant-all.html one or more of those URLs may help. FYI, I got these from the infobot on the freenode #PostgreSQL IRC channel
On Wed, Feb 18, 2009 at 16:09, John R Pierce <pierce@hogranch.com> wrote: > Clemens Schwaighofer wrote: >> >> The other problem is, that there is no "grant all on table db.* ..." but >> I have to do that for each table seperate, or in a "grant all on table >> a, b, ...". >> >> I am not sure if there is an easier way, except perhaps through a select >> from the pg_ catalog for this db and get the table names there ... >> > > I believe there are some scripts that can help.. > > http://pgedit.com/public/sql/acl_admin/index.html > http://unf.be/postgresql/postgres_grant_all.perl > http://www.archonet.com/pgdocs/grant-all.html Thanks a lot. > > one or more of those URLs may help. FYI, I got these from the infobot on > the freenode #PostgreSQL IRC channel Time to start up another IRC connection. -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Manager ] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703 Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclaimer@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates.
John R Pierce wrote: > that is correct. DATABASE privileges relate to connecting to the > database, permissions to create objects and so forth. To be precise, there are 3 privileges: - create temporary tables - connect to the database - create schemata To be allowed to create a table, you need the create privilege on the containing schema. To be allowed to select from a table, you also need the usage privilege on the schema containing the object. You have all privileges on an object you own. I guess that should clarify the behaviour in the original question. Yours, Laurenz Albe
On Tue, Feb 17, 2009 at 9:20 PM, Clemens Schwaighofer <clemens.schwaighofer@tequila.jp> wrote: > But yesterday I run in some issues with table ownership and thought if I > just give the user all rights for the DB, he should have all rights to > the tables too. Try granting select on a database and you will get this: grant select on database smarlowe to stan; ERROR: invalid privilege type SELECT for database OTOH, grant connect on database smarlowe to stan; GRANT But even easier is to use the db owner as the ROLE instead of as a user, and just alter group guywhoownsthedb add user guywhodoesnt;
On Wed, Feb 18, 2009 at 17:42, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Tue, Feb 17, 2009 at 9:20 PM, Clemens Schwaighofer > <clemens.schwaighofer@tequila.jp> wrote: > >> But yesterday I run in some issues with table ownership and thought if I >> just give the user all rights for the DB, he should have all rights to >> the tables too. > > Try granting select on a database and you will get this: > > grant select on database smarlowe to stan; > ERROR: invalid privilege type SELECT for database > > OTOH, > > grant connect on database smarlowe to stan; > GRANT > > But even easier is to use the db owner as the ROLE instead of as a > user, and just > > alter group guywhoownsthedb add user guywhodoesnt; Thats a very good idea. Probably easier to handle, if you need to add just another user or so. -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Manager ] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703 Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclaimer@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates.