Thread: Rights for view.
Hi. Hext problem: I have table table1 with owner user1; I have view view1 based only on table1 owner user1 too. I have user2 which not own anything. user1 grant rights to select on view1 to user2. Why user2 when try select from view1 get 'table1: permission denied'? Thanks.
Viacheslav N Tararin <taras@dekasoft.com.ua> writes:
> I have table table1 with owner user1;
> I have view view1 based only on table1 owner user1 too.
> I have user2 which not own anything.
> user1 grant rights to select on view1 to user2.
> Why user2 when try select from view1 get 'table1: permission denied'?
You'll need to give more details, because as far as I can tell that
works:
regression=# create user user1;
CREATE USER
regression=# create user user2;
CREATE USER
regression=# \c - user1
You are now connected as new user user1.
regression=> create table table1(f1 int);
CREATE TABLE
regression=> create view view1 as select * from table1;
CREATE VIEW
regression=> grant select on view1 to user2;
GRANT
regression=> \c - user2
You are now connected as new user user2.
regression=> select * from table1;
ERROR: table1: permission denied
regression=> select * from view1;
f1
----
(0 rows)
regards, tom lane
Tom Lane пишет: >Viacheslav N Tararin <taras@dekasoft.com.ua> writes: > > >>I have table table1 with owner user1; >>I have view view1 based only on table1 owner user1 too. >>I have user2 which not own anything. >>user1 grant rights to select on view1 to user2. >>Why user2 when try select from view1 get 'table1: permission denied'? >> >> > >You'll need to give more details, because as far as I can tell that >works: > ---- My test script ---- create user user2 password 'u2'; create schema user2; create table user2.table1(t1 integer); create view user2.view1 as select * from user2.table1; grant select on user2.view1 to user2; \c - user2; select * from user2.view1; select * from user2.table1; ---- My output ----- CREATE USER CREATE SCHEMA CREATE TABLE CREATE VIEW GRANT You are now connected as new user user2. psql:test.sql:7: ERROR: user2: permission denied psql:test.sql:8: ERROR: user2: permission denied Thanks
On Tue, Feb 11, 2003 at 18:17:05 +0200, Viacheslav N Tararin <taras@dekasoft.com.ua> wrote: > ---- My test script ---- > create user user2 password 'u2'; > create schema user2; > create table user2.table1(t1 integer); > create view user2.view1 as select * from user2.table1; > grant select on user2.view1 to user2; > \c - user2; > select * from user2.view1; > select * from user2.table1; > > ---- My output ----- > CREATE USER > CREATE SCHEMA > CREATE TABLE > CREATE VIEW > GRANT > You are now connected as new user user2. > psql:test.sql:7: ERROR: user2: permission denied > psql:test.sql:8: ERROR: user2: permission denied I think you will need to grant select access on the schema or else user2 won't have access to user2.view1 because they don't have access to the schema.
Thanks. (heaped up, though) Tom Lane пишет: >>create user user2 password 'u2'; >>create schema user2; >>create table user2.table1(t1 integer); >>create view user2.view1 as select * from user2.table1; >>grant select on user2.view1 to user2; >>\c - user2; >>select * from user2.view1; >>select * from user2.table1; >> >> > > > >>---- My output ----- >>CREATE USER >>CREATE SCHEMA >>CREATE TABLE >>CREATE VIEW >>GRANT >>You are now connected as new user user2. >>psql:test.sql:7: ERROR: user2: permission denied >>psql:test.sql:8: ERROR: user2: permission denied >> >> > >It's complaining about the user2 schema, which is owned by user1 >(or whoever did that CREATE SCHEMA) and has no permissions for >user2 to access it. You need at least > >grant usage on schema user2 to user2; > >Alternatively you might have wanted to make the user2 schema be >owned by user2 in the first place. > > regards, tom lane > > > >
Select from view1 will be passed to table1(kinda like a symbolic link), so you need give user2 access to table1 too. Viacheslav N Tararin wrote: > Hi. > Hext problem: > I have table table1 with owner user1; > I have view view1 based only on table1 owner user1 too. > I have user2 which not own anything. > user1 grant rights to select on view1 to user2. > > Why user2 when try select from view1 get 'table1: permission denied'? > > Thanks. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Tue, 2003-02-11 at 15:02, Rudy Young wrote: > Select from view1 will be passed to table1(kinda like a symbolic link), > so you need > give user2 access to table1 too. That is not the case in version 7.3, where user2 will be able to see the contents of the view but not of the table on which it is based. (I just verified this by experiment, because it would make granting permissions on views an almost useless exercise if the problem were still there.) > Viacheslav N Tararin wrote: > > > Hi. > > Hext problem: > > I have table table1 with owner user1; > > I have view view1 based only on table1 owner user1 too. > > I have user2 which not own anything. > > user1 grant rights to select on view1 to user2. > > > > Why user2 when try select from view1 get 'table1: permission denied'? What version is this? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "But as many as received him, to them gave he power to become the sons of God, even to them that believe on his name" John 1:12
Oliver Elphick <olly@lfix.co.uk> writes:
>> Viacheslav N Tararin wrote:
>>> Why user2 when try select from view1 get 'table1: permission denied'?
> What version is this?
I thought we'd concluded that his problem was really one of not having
granted USAGE permission on the schema containing the view.
regards, tom lane
On Sun, 2003-02-16 at 21:41, Tom Lane wrote: > Oliver Elphick <olly@lfix.co.uk> writes: > >> Viacheslav N Tararin wrote: > >>> Why user2 when try select from view1 get 'table1: permission denied'? > > > What version is this? > > I thought we'd concluded that his problem was really one of not having > granted USAGE permission on the schema containing the view. Sorry; I had not connected the message with that thread. I just checked, and see that what I replied to was a message that has just got retransmitted after several days. Why does that keep on happening? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "But as many as received him, to them gave he power to become the sons of God, even to them that believe on his name" John 1:12
Oliver Elphick <olly@lfix.co.uk> writes:
> Sorry; I had not connected the message with that thread. I just
> checked, and see that what I replied to was a message that has just got
> retransmitted after several days. Why does that keep on happening?
I believe the problem is typically
1. newbie sends message to list
2. newbie gets back autoresponse that message has been queued
for moderator approval, because he's not a subscriber
3. newbie subscribes to list
4. newbie re-sends message; now it goes through
and some while later
5. Marc scans the moderator queue and approves the original copy
It would be real nice if this could be fixed, preferably in an automated
way that would reduce Marc's manual load instead of increase it.
I certainly don't expect Marc to remember messages that have been
reposted.
I wonder if we could somehow connect majordomo to the moderator's
message queue, so that when someone subscribes to a list, pending
messages from that someone to that list are automatically removed from
the queue and posted (or at least reprocessed; they might still get
held if they fail to pass some other filter). Or bounce them back to
the someone with a note suggesting he repost for himself. It'd be
important that the new subscriber be informed that the messages are
being released before he gets to step 4 for himself.
I have no idea how feasible this is, but if we could implement this
or some variant, it'd reduce Marc's workload as well as annoyance to
existing subscribers.
regards, tom lane