Thread: Relation does not exist problem
Hi, I'm new to the list and hope I don't make too much mistakes ;) I have a postgreSQL 8.1 here, where I have a little problem. Database is owned by user A, I have a group g_a and a user B. Both A and B are in group g_a. Using the grant assistant from pgadmin (up to 1.8b5) I gave SELECT, INSERT, UPDATE, DELETE and EXECUTE rights to g_a for every object in the scheme. Though when I try to select something from a table with user B, I get the error "Relation _table_ does not exist" (translated from german). Any advice where I should check for the problem? I already googled of course, and it's not the Upper/Lowercase problem... Lars
>From: "Oeschey, Lars (I/EK-142, extern)" <extern.Lars.Oeschey@AUDI.DE> >Though when I try to select something from a table >with user B, I get the error "Relation _table_ does not exist" the error mesage would be different, if user B has no rights for table _table_ . something like "permission denied for relation _table_". it looks like as if the table you asked for really does not exist. maybe you spelled it the wrong way. regards Sepp _________________________________________________________________ JETZT die neueste Version des Windows Live Messenger downloaden! http://get.live.com/de-at/messenger/overview
> >Though when I try to select something from a table > >with user B, I get the error "Relation _table_ does not exist" > > the error mesage would be different, if user B has no rights > for table > _table_ . something like > "permission denied for relation _table_". > > it looks like as if the table you asked for really does not > exist. maybe you > spelled it the wrong way. not really... when I connect to the database with User A, I can see everything perfectly (and select etc.) I have both connections parallel in pgadmin, and can compare that way. It even happens in pgadmin, that I get the error when I click on a table (so spelling shouldn't be a problem). I also did add a "ALTER ROLE User B SET search_path=schema 1, schema 2;" to the problem user... Lars
am Tue, dem 18.09.2007, um 8:04:27 +0000 mailte Robert Wimmer folgendes: > > > > >From: "Oeschey, Lars (I/EK-142, extern)" <extern.Lars.Oeschey@AUDI.DE> > > >Though when I try to select something from a table > >with user B, I get the error "Relation _table_ does not exist" > > the error mesage would be different, if user B has no rights for table > _table_ . something like > "permission denied for relation _table_". > > it looks like as if the table you asked for really does not exist. maybe > you spelled it the wrong way. Right. Or the table are in a different schema. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
> > it looks like as if the table you asked for really does not > exist. maybe > > you spelled it the wrong way. > Right. Or the table are in a different schema. nope, I checked that with select tablename,schemaname from pg_tables where schemaname='etefas'; which gives me same result done as User A or B. Though I have to correct something: when I do a select from a table I get a "no permission on schema" error. The "Relation does not exist" error comes up when I select a table in pgadmin. So it seems to come down to a permission problem, but where? Lars
>From: "Oeschey, Lars (I/EK-142, extern)" <extern.Lars.Oeschey@AUDI.DE> >To: <pgsql-novice@postgresql.org> >Subject: Re: [NOVICE] Relation does not exist problem >Date: Tue, 18 Sep 2007 10:38:23 +0200 > > > > it looks like as if the table you asked for really does not > > exist. maybe > > > you spelled it the wrong way. > > > Right. Or the table are in a different schema. > >nope, I checked that with > >select tablename,schemaname from pg_tables where schemaname='etefas'; > >when I do a select from a table I >get a "no permission on schema" error. The "Relation does not exist" >error comes up when I select a table in pgadmin. So it seems to come >down to a permission problem, but where? > GRANT USAGE ON SCHEMA ... TO ... regards Sepp _________________________________________________________________ JETZT die neueste Version des Windows Live Messenger downloaden! http://get.live.com/de-at/messenger/overview
> GRANT USAGE ON SCHEMA ... TO ... I have granted usage to the group g_a, in which both user A and B are... do I really have to explicitly grant usage to the user? Shouldn't the group be sufficient?
>From: "Oeschey, Lars (I/EK-142, extern)" <extern.Lars.Oeschey@AUDI.DE> >To: <pgsql-novice@postgresql.org> >Subject: Re: [NOVICE] Relation does not exist problem >Date: Tue, 18 Sep 2007 11:41:30 +0200 > > > GRANT USAGE ON SCHEMA ... TO ... > >I have granted usage to the group g_a, in which both user A and B are... >do I really have to explicitly grant usage to the user? Shouldn't the >group be sufficient? you have explicit to grant usage on the schema ... regards _________________________________________________________________ JETZT die neueste Version des Windows Live Messenger downloaden! http://get.live.com/de-at/messenger/overview
> >I have granted usage to the group g_a, in which both user A > and B are... > >do I really have to explicitly grant usage to the user? Shouldn't the > >group be sufficient? > > you have explicit to grant usage on the schema ... hm, when I try to do that with pgadmin, I only have groups available. I see public and g_a. And with every 7.4 database we have here, it's also done the same way. I have a group, with users, and I grant acess to the group (not the user). Did this really change with 8.1?
>From: "Oeschey, Lars (I/EK-142, extern)" <extern.Lars.Oeschey@AUDI.DE> > >hm, when I try to do that with pgadmin, I only have groups available. I >see public and g_a. And with every 7.4 database we have here, it's also >done the same way. I have a group, with users, and I grant acess to the >group (not the user). you dont have to grant the schema usage to the user and the group. if you grant it to the group, its granted to the groupmembers implicit. if you select the schema in pgadmin you should see something like -- Schema: "entry" -- DROP SCHEMA entry; CREATE SCHEMA entry AUTHORIZATION postgres; GRANT ALL ON SCHEMA entry TO postgres; GRANT USAGE ON SCHEMA entry TO GROUP contact_user_group; regards Sepp Wimmer _________________________________________________________________ JETZT die neueste Version des Windows Live Messenger downloaden! http://get.live.com/de-at/messenger/overview
> you dont have to grant the schema usage to the user and the > group. if you > grant it to the group, its granted to the groupmembers > implicit. if you > select the schema in pgadmin you should see something like > > -- Schema: "entry" > > -- DROP SCHEMA entry; > > CREATE SCHEMA entry > AUTHORIZATION postgres; > GRANT ALL ON SCHEMA entry TO postgres; > GRANT USAGE ON SCHEMA entry TO GROUP contact_user_group; I found the problem. Starting with postgreSQL 8, the rights for users have to be inherited from the group. So that a user inherits the rights from the group where it is member, it has to have the flag INHERIT set (standard when generating a user is NOINHERIT) Lars