Обсуждение: Why sequence grant is separated from table?

I really hate the error "permission denied for sequence xxxxx" when I
grant on a table but forget to grant additionally on the related
sequence to users. Can the permission of table and related sequences be
merged?

<div class="moz-cite-prefix">于 2013/6/19 17:47, Szymon Guz 写道:<br /></div><blockquote
cite="mid:CAFjNrYto7C0G_2+UFLcWng3T6rZih5t54P0tRO4w+_Z0wONxMA@mail.gmail.com"type="cite"><div dir="ltr">On 19 June 2013
11:35,Rural Hunter <span dir="ltr"><<a href="mailto:ruralhunter@gmail.com" moz-do-not-send="true"
target="_blank">ruralhunter@gmail.com</a>></span>wrote:<br /><div class="gmail_extra"><div
class="gmail_quote"><blockquoteclass="gmail_quote" style="margin:0 0 0             .8ex;border-left:1px #ccc
solid;padding-left:1ex">Ireally hate the error "permission denied for sequence xxxxx" when I grant on a table but
forgetto grant additionally on the related sequence to users. Can the permission of table and related sequences be
merged?<spanclass="HOEnZb"><font color="#888888"><br /><br /></font></span></blockquote><div><br /></div><div
style="">Ican imagine a situation where you want a user to be granted rights on a table, to use sequence with curval
andnextval functions, but not setval, as it can provide some database problems.</div></div></div></div></blockquote>
oh,is it possible to separately grant nextval and setval on sequence currently?<br /><blockquote
cite="mid:CAFjNrYto7C0G_2+UFLcWng3T6rZih5t54P0tRO4w+_Z0wONxMA@mail.gmail.com"type="cite"><div dir="ltr"><div
class="gmail_extra"><divclass="gmail_quote"><div style=""><br /></div><div style="">What's more, in general, you cannot
saywhich sequence is used for which table, if the sequence is not owned by the
table.</div></div></div></div></blockquote>Can we get it from the column definition such as a serial column?<br
/><blockquotecite="mid:CAFjNrYto7C0G_2+UFLcWng3T6rZih5t54P0tRO4w+_Z0wONxMA@mail.gmail.com" type="cite"><div
dir="ltr"><divclass="gmail_extra"><div class="gmail_quote"><div style=""><br /></div><div style="">Which permissions of
atable do you want to merge with which permissions of sequences?</div></div></div></div></blockquote> At least granting
inserton a table means to grant update on the sequence used by serial columns.<br /><blockquote
cite="mid:CAFjNrYto7C0G_2+UFLcWng3T6rZih5t54P0tRO4w+_Z0wONxMA@mail.gmail.com"type="cite"><div dir="ltr"><div
class="gmail_extra"><divclass="gmail_quote"><div style=""><br /></div><div style="">regards</div><div
style="">Szymon</div></div></div></div></blockquote><br/> 

<div class="moz-cite-prefix">Yes, that's also an acceptable solution.<br /><br /> 于 2013/6/20 3:48, Craig James 写道:<br
/></div><blockquotecite="mid:CAFwQ8rcwDqPArROq30MOXtN1c7yZn3ODy2fy8W6mcvZSEZeVhg@mail.gmail.com" type="cite"><br /><br
/><divclass="gmail_quote">On Wed, Jun 19, 2013 at 2:35 AM, Rural Hunter <span dir="ltr"><<a
href="mailto:ruralhunter@gmail.com"moz-do-not-send="true" target="_blank">ruralhunter@gmail.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0         .8ex;border-left:1px #ccc
solid;padding-left:1ex">I really hate the error "permission denied for sequence xxxxx" when I grant on a table but
forgetto grant additionally on the related sequence to users. Can the permission of table and related sequences be
merged?<spanclass="HOEnZb"><font color="#888888"><br /></font></span></blockquote><div><br /> You asked this question
backin March; here's what I suggested at the time:<br /><pre style="margin-left:40px">On Thu, Mar 28, 2013 at 10:56 PM,
RuralHunter <<a href="mailto:ruralhunter@gmail.com" moz-do-not-send="true">ruralhunter@gmail.com</a>>wrote: 
> Hi,
>
> I encounter the same issue often: Granted update/insert to an user but
> forgot to grant it on the related sequence. It's hard to understand that an
> user has write access on table but not on necessary sequences. I think the
> grant on tables should cascade to related sequences. What do you think?
>

Wouldn't it make more sense for the grant on the table to fail with an
appropriate error message?  That would solve your problem, and it wouldn't
be making security assumptions.  Cascading permissions seems like a recipe
for trouble.

Craig
</pre> I suggest is that having the "grant ... on tablename" fail would serve your purpose.  What you want is for it to
letyou know you've made a security change that is bound to fail.  I think it would actually be better to have the GRANT
failsince it would notify you that the script or procedure you are using is incorrect.<br /><br /> Craig<br />  <br
/></div><blockquoteclass="gmail_quote" style="margin:0 0 0         .8ex;border-left:1px #ccc
solid;padding-left:1ex"><spanclass="HOEnZb"><font color="#888888"><br /><br /> -- <br /> Sent via pgsql-admin mailing
list(<a href="mailto:pgsql-admin@postgresql.org" moz-do-not-send="true"
target="_blank">pgsql-admin@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-admin"moz-do-not-send="true"
target="_blank">http://www.postgresql.org/mailpref/pgsql-admin</a><br/></font></span></blockquote></div><br
/></blockquote><br/> 

On 19 June 2013 11:35, Rural Hunter <ruralhunter@gmail.com> wrote:

I really hate the error "permission denied for sequence xxxxx" when I grant on a table but forget to grant additionally on the related sequence to users. Can the permission of table and related sequences be merged?

I can imagine a situation where you want a user to be granted rights on a table, to use sequence with curval and nextval functions, but not setval, as it can provide some database problems.

What's more, in general, you cannot say which sequence is used for which table, if the sequence is not owned by the table.

Which permissions of a table do you want to merge with which permissions of sequences?

regards

Szymon

-- 
Achilleas Mantzios