Thread: Grant select on view
Hi, I try encapsulate my db in views such that users access it by the views and only get to see the rows they are allowed to. However, I created a view and put select select ricghts on it for the respective users, but a select on it ends up with a permission denied. So, I granted select rights on the base table, but to no avail. A select by the users on the base table is no possible, but on the view it stays denied. What have I overlooked? Thx Thiemo <fontfamily><param>Helvetica</param> -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur </fontfamily>Hi, I try encapsulate my db in views such that users access it by the views and only get to see the rows they are allowed to. However, I created a view and put select select ricghts on it for the respective users, but a select on it ends up with a permission denied. So, I granted select rights on the base table, but to no avail. A select by the users on the base table is no possible, but on the view it stays denied. What have I overlooked? Thx Thiemo -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur
thiemo <thiemo@gmx.ch> writes:
> What have I overlooked?
A complete example; we can't possibly guess what you did wrong with
only such sketchy details. It would also be useful to know which
version of PG you are using.
regards, tom lane
<excerpt>A complete example; we can't possibly guess what you did wrong with only such sketchy details. It would also be useful to know which version of PG you are using. </excerpt> Oh, sure. A self-compiled 7.2, without any source code modifications. I first instal a sequence: "CREATE SEQUENCE seq INCREMENT 1 MINVALUE 1 START 1 CACHE 1" then a table: "CREATE TABLE base ( att1 type(x) default nextval(seq), ... attn type(x) )" a view: "CREATE VIEW vie AS SELECT att2, ..., attn_1 FROM base WHERE attn = CURRENT_USER::varchar" a user: "CREATE USER usr PASSWORD 'somewhat' NOCREATEDB NOCREATEUSER" and finally the grant: "GRANT SELECT ON vie TO usr" Hope this helps. Thx Thiemo <fontfamily><param>Helvetica</param> -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur </fontfamily>> A complete example; we can't possibly guess what you did wrong with > only such sketchy details. It would also be useful to know which > version of PG you are using. Oh, sure. A self-compiled 7.2, without any source code modifications. I first instal a sequence: "CREATE SEQUENCE seq INCREMENT 1 MINVALUE 1 START 1 CACHE 1" then a table: "CREATE TABLE base ( att1 type(x) default nextval(seq), ... attn type(x) )" a view: "CREATE VIEW vie AS SELECT att2, ..., attn_1 FROM base WHERE attn = CURRENT_USER::varchar" a user: "CREATE USER usr PASSWORD 'somewhat' NOCREATEDB NOCREATEUSER" and finally the grant: "GRANT SELECT ON vie TO usr" Hope this helps. Thx Thiemo -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur
thiemo <thiemo@gmx.ch> writes:
> Oh, sure. A self-compiled 7.2, without any source code modifications. I=20
> first instal a sequence:
You're still not being specific enough, because as closely as I can
reproduce your example, it works fine:
regression=# CREATE SEQUENCE seq;
CREATE
regression=# CREATE TABLE base (
regression(# att1 int default nextval('seq'),
regression(# att2 text,
regression(# attn varchar);
CREATE
regression=# CREATE VIEW vie AS select * from base
regression-# WHERE attn = CURRENT_USER::varchar;
CREATE
regression=# create user usr;
CREATE USER
regression=# GRANT SELECT ON vie TO usr;
GRANT
regression=# \c - usr
You are now connected as new user usr.
regression=> select * from base;
ERROR: base: Permission denied.
regression=> select * from vie;
att1 | att2 | attn
------+------+------
(0 rows)
regression=>
It's not apparent to me what you did differently from the above
to cause a permissions problem.
regards, tom lane
<excerpt>You're still not being specific enough, because as closely as I can reproduce your example, it works fine: </excerpt> Uhm, then I don't know what information could be missing. I set the stuff up with dbvisualizer 2.1. I cannot say I really did it like I stated, 'cause it was a rather chaotic try and error process up to the point where I had the scripts how I wanted them. However, what I mentioned was the process I think not necessairily in this order. I proved the non workingness of this db with psql such that I boldly rule out a flaw in dbvis. Well, I shall drop the db and recreate it structuredly and post my findings again. Thx for your help anyway Thiemo <fontfamily><param>Helvetica</param> -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur </fontfamily>> You're still not being specific enough, because as closely as I can > reproduce your example, it works fine: Uhm, then I don't know what information could be missing. I set the stuff up with dbvisualizer 2.1. I cannot say I really did it like I stated, 'cause it was a rather chaotic try and error process up to the point where I had the scripts how I wanted them. However, what I mentioned was the process I think not necessairily in this order. I proved the non workingness of this db with psql such that I boldly rule out a flaw in dbvis. Well, I shall drop the db and recreate it structuredly and post my findings again. Thx for your help anyway Thiemo -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur
<excerpt>Well, I shall drop the db and recreate it structuredly and post my findings again. </excerpt> So I have done and all works fine. However, I certainly shall be curiuos how I could have lead my db to the effect I experienced. Thx Thiemo <fontfamily><param>Helvetica</param>-- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur </fontfamily>> Well, I shall drop the db and recreate it structuredly and post my > findings again. So I have done and all works fine. However, I certainly shall be curiuos how I could have lead my db to the effect I experienced. Thx Thiemo -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur