Thread: BUG #1794: inheritance removes permissions from the parent table
The following bug has been logged online: Bug reference: 1794 Logged by: Sean Burlington Email address: sean@uncertainty.org.uk PostgreSQL version: 7.4 Operating system: Debian GNU/Linux Description: inheritance removes permissions from the parent table Details: Hi, I'm not sure if this is strictly a bug or just a side effect of inheritance that could do with being added to the documentation. If you create a new table that inherits from another table - a user cannot select from the parent if they cannot select from the child. To recreate: as dba create table a (id int); grant select on a to auser; insert into a (id) values (1); as auser select * from a; id ---- 1 as dba create table b (data int) inherits (a); as auser select * from a; ERROR: permission denied for relation b I didn't expect to need permission for table b in order to select from a...
On Thu, Jul 28, 2005 at 12:48:35PM +0100, Sean Burlington wrote: > > Description: inheritance removes permissions from the parent table I think a more accurate description would be "permissions not inherited by children," and that isn't necessarily a bug. > If you create a new table that inherits from another table - a user cannot > select from the parent if they cannot select from the child. [snip] > select * from a; > ERROR: permission denied for relation b > > I didn't expect to need permission for table b in order to select from a... Records in the child are visible when you select from the parent, so it follows that you'd need permission on both tables. If you want only records that are in the parent then use FROM ONLY: SELECT * FROM ONLY a; -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Thu, Jul 28, 2005 at 03:56:14PM +0100, Sean Burlington wrote: > Michael Fuhr wrote: > > On Thu, Jul 28, 2005 at 12:48:35PM +0100, Sean Burlington wrote: > > > >>Description: inheritance removes permissions from the parent table > > > > I think a more accurate description would be "permissions not > > inherited by children," and that isn't necessarily a bug. > > I agree it may not be a bug - but it's more than the permissions not > being inherited: the parent is affected. Not really, once you understand what's happening. Unless you use FROM ONLY, selecting from the parent selects from the parent *and* its children. The parent itself isn't affected, as queries with FROM ONLY should demonstrate. I understand what you're saying -- that there's an apparent effect on the parent -- but there really isn't. > It would be handy if this was in the documentation for anyone else who > comes across this issue Feel free to submit a documentation patch to pgsql-patches :-) > Would it be possible to allow the usual select and just return the > records the user has permission for - possibly along with a warning > about the child table? I think you'd have a hard time selling that to the developers, because a query that returns successfully but with an incomplete record set isn't solving any problem. If the problem is inadequate documentation, then the correct solution is to fix the documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote: > On Thu, Jul 28, 2005 at 12:48:35PM +0100, Sean Burlington wrote: > >>Description: inheritance removes permissions from the parent table > > > I think a more accurate description would be "permissions not > inherited by children," and that isn't necessarily a bug. > I agree it may not be a bug - but it's more than the permissions not being inherited: the parent is affected. [snip] > Records in the child are visible when you select from the parent, > so it follows that you'd need permission on both tables. If you > want only records that are in the parent then use FROM ONLY: > > SELECT * FROM ONLY a; > That makes sense :-) I was experimenting with inheritance for the first time and for me this effect was a real suprise. It would be handy if this was in the documentation for anyone else who comes across this issue Would it be possible to allow the usual select and just return the records the user has permission for - possibly along with a warning about the child table? -- Sean
Michael Fuhr wrote:
> On Thu, Jul 28, 2005 at 03:56:14PM +0100, Sean Burlington wrote:
>
>>Michael Fuhr wrote:
>>
>>>On Thu, Jul 28, 2005 at 12:48:35PM +0100, Sean Burlington wrote:
>>>
>>>
>>>>Description: inheritance removes permissions from the parent table
>>>
>>>I think a more accurate description would be "permissions not
>>>inherited by children," and that isn't necessarily a bug.
>>
>>I agree it may not be a bug - but it's more than the permissions not
>>being inherited: the parent is affected.
>
>
> Not really, once you understand what's happening. Unless you use
> FROM ONLY, selecting from the parent selects from the parent *and*
> its children. The parent itself isn't affected, as queries with
> FROM ONLY should demonstrate. I understand what you're saying --
> that there's an apparent effect on the parent -- but there really
> isn't.
>
>
>>It would be handy if this was in the documentation for anyone else who
>>comes across this issue
>
>
> Feel free to submit a documentation patch to pgsql-patches :-)
>
OK - patch attached
I hope it's OK - I'm afraid I didn't spend too much time looking at the
best way to contribute patches and just went ahead and made one ...
--
Sean
Index: doc/src/sgml/ddl.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v
retrieving revision 1.42
diff -u -F^f -r1.42 ddl.sgml
--- doc/src/sgml/ddl.sgml 14 Jul 2005 06:17:35 -0000 1.42
+++ doc/src/sgml/ddl.sgml 31 Jul 2005 16:12:54 -0000
@@ -1117,6 +1117,17 @@
support this <quote>ONLY</quote> notation.
</para>
+
+<note>
+ <title>Inheritance and Permissions</title>
+ <para>
+ Because permissions are not inherited automatically a user attempting to access
+ a parent table must either have at least the same permission for the child table
+ or must use the <quote>ONLY</quote> notation. If creating a new inheritance
+ relationship in an existing system be careful that this does not create problems.
+ </para>
+</note>
+
<note>
<title>Deprecated</title>
<para>
Patch applied. Thanks. --------------------------------------------------------------------------- Sean Burlington wrote: > Michael Fuhr wrote: > > On Thu, Jul 28, 2005 at 03:56:14PM +0100, Sean Burlington wrote: > > > >>Michael Fuhr wrote: > >> > >>>On Thu, Jul 28, 2005 at 12:48:35PM +0100, Sean Burlington wrote: > >>> > >>> > >>>>Description: inheritance removes permissions from the parent table > >>> > >>>I think a more accurate description would be "permissions not > >>>inherited by children," and that isn't necessarily a bug. > >> > >>I agree it may not be a bug - but it's more than the permissions not > >>being inherited: the parent is affected. > > > > > > Not really, once you understand what's happening. Unless you use > > FROM ONLY, selecting from the parent selects from the parent *and* > > its children. The parent itself isn't affected, as queries with > > FROM ONLY should demonstrate. I understand what you're saying -- > > that there's an apparent effect on the parent -- but there really > > isn't. > > > > > >>It would be handy if this was in the documentation for anyone else who > >>comes across this issue > > > > > > Feel free to submit a documentation patch to pgsql-patches :-) > > > > OK - patch attached > > I hope it's OK - I'm afraid I didn't spend too much time looking at the > best way to contribute patches and just went ahead and made one ... > > -- > > Sean > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073