pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy sequence grant is separated from table?
7+ messages / 4 participants
[nested] [flat]
* Why sequence grant is separated from table?
@ 2013-06-19 09:35 Rural Hunter <ruralhunter@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rural Hunter @ 2013-06-19 09:35 UTC (permalink / raw)
To: pgsql-admin
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?
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why sequence grant is separated from table?
@ 2013-06-19 09:47 Szymon Guz <mabewlun@gmail.com>
parent: Rural Hunter <ruralhunter@gmail.com>
1 sibling, 2 replies; 7+ messages in thread
From: Szymon Guz @ 2013-06-19 09:47 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why sequence grant is separated from table?
@ 2013-06-19 10:06 Rural Hunter <ruralhunter@gmail.com>
parent: Szymon Guz <mabewlun@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Rural Hunter @ 2013-06-19 10:06 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: pgsql-admin
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<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 moz-do-not-send="true"
href="mailto:ruralhunter@gmail.com" target="_blank">ruralhunter@gmail.com</a>></span>
wrote:<br>
<div class="gmail_extra">
<div class="gmail_quote">
<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 forget to grant additionally on the
related sequence to users. Can the permission of table and
related sequences be merged?<span class="HOEnZb"><font
color="#888888"><br>
<br>
</font></span></blockquote>
<div><br>
</div>
<div style="">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.</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">
<div class="gmail_quote">
<div style=""><br>
</div>
<div style="">What's more, in general, you cannot say which
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>
<blockquote
cite="mid:CAFjNrYto7C0G_2+UFLcWng3T6rZih5t54P0tRO4w+_Z0wONxMA@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<div style=""><br>
</div>
<div style="">Which permissions of a table do you want to
merge with which permissions of sequences?</div>
</div>
</div>
</div>
</blockquote>
At least granting insert on 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">
<div class="gmail_quote">
<div style=""><br>
</div>
<div style="">regards</div>
<div style="">Szymon</div>
</div>
</div>
</div>
</blockquote>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why sequence grant is separated from table?
@ 2013-06-19 10:15 Szymon Guz <mabewlun@gmail.com>
parent: Rural Hunter <ruralhunter@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Szymon Guz @ 2013-06-19 10:15 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
On 19 June 2013 12:06, Rural Hunter <ruralhunter@gmail.com> wrote:
> 于 2013/6/19 17:47, Szymon Guz 写道:
>
> 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.
>
> oh, is it possible to separately grant nextval and setval on sequence
> currently?
>
>
According to http://www.postgresql.org/docs/9.2/static/sql-grant.html
SELECTFor sequences, this privilege also allows the use of the currval
function.
UPDATEFor sequences, this privilege allows the use of the nextval and setval
functions.
>
> What's more, in general, you cannot say which sequence is used for which
> table, if the sequence is not owned by the table.
>
> Can we get it from the column definition such as a serial column?
>
>
Sure we can.
>
> Which permissions of a table do you want to merge with which permissions
> of sequences?
>
> At least granting insert on a table means to grant update on the sequence
> used by serial columns.
>
It could be done. Unfortunately it doesn't cover all cases, like some ORMs
which can use one sequence for many different tables, but of course for
serials it can be done.
Szymon
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why sequence grant is separated from table?
@ 2013-06-19 19:48 Craig James <cjames@emolecules.com>
parent: Rural Hunter <ruralhunter@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Craig James @ 2013-06-19 19:48 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
On Wed, Jun 19, 2013 at 2:35 AM, 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?
>
You asked this question back in March; here's what I suggested at the time:
On Thu, Mar 28, 2013 at 10:56 PM, Rural Hunter <ruralhunter@gmail.com>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
I suggest is that having the "grant ... on tablename" fail would serve your
purpose. What you want is for it to let you know you've made a security
change that is bound to fail. I think it would actually be better to have
the GRANT fail since it would notify you that the script or procedure you
are using is incorrect.
Craig
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin;
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why sequence grant is separated from table?
@ 2013-06-20 01:38 Rural Hunter <ruralhunter@gmail.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rural Hunter @ 2013-06-20 01:38 UTC (permalink / raw)
To: pgsql-admin
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Yes, that's also an acceptable
solution.<br>
<br>
于 2013/6/20 3:48, Craig James 写道:<br>
</div>
<blockquote
cite="mid:CAFwQ8rcwDqPArROq30MOXtN1c7yZn3ODy2fy8W6mcvZSEZeVhg@mail.gmail.com"
type="cite"><br>
<br>
<div class="gmail_quote">On Wed, Jun 19, 2013 at 2:35 AM, Rural
Hunter <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:ruralhunter@gmail.com" 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 forget to grant additionally on
the related sequence to users. Can the permission of table and
related sequences be merged?<span class="HOEnZb"><font
color="#888888"><br>
</font></span></blockquote>
<div><br>
You asked this question back in March; here's what I suggested
at the time:<br>
<pre style="margin-left:40px">On Thu, Mar 28, 2013 at 10:56 PM, Rural Hunter <<a moz-do-not-send="true" href="mailto:ruralhunter@gmail.com">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 let you
know you've made a security change that is bound to fail. I
think it would actually be better to have the GRANT fail since
it would notify you that the script or procedure you are using
is incorrect.<br>
<br>
Craig<br>
<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex"><span
class="HOEnZb"><font color="#888888">
<br>
<br>
-- <br>
Sent via pgsql-admin mailing list (<a
moz-do-not-send="true"
href="mailto:pgsql-admin@postgresql.org" target="_blank">pgsql-admin@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a moz-do-not-send="true"
href="http://www.postgresql.org/mailpref/pgsql-admin";
target="_blank">http://www.postgresql.org/mailpref/pgsql-admin</a><br;
</font></span></blockquote>
</div>
<br>
</blockquote>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why sequence grant is separated from table?
@ 2013-06-20 09:46 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Szymon Guz <mabewlun@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Achilleas Mantzios @ 2013-06-20 09:46 UTC (permalink / raw)
To: pgsql-admin
On 19/06/2013 12:47, Szymon Guz wrote:
> On 19 June 2013 11:35, Rural Hunter <ruralhunter@gmail.com
> <mailto: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.
Right, there might be cases, when one sequence is used by more than one
table. Like for instance when the user wishes to create a view on a
union of two tables, with their IDs included, and still those IDs to be
guaranteed to be unique across the union.
However, after I dumped a table recently whose sequence is shared by
another table as wee i noticed this :
ALTER SEQUENCE vessels_id_seq OWNED BY vessels.id;
Which means that the initial relationship between the "creating" table
and the sequence is preserved for the whole life of the table. This
could tempt new users to think that it could be meaningful to bind those
two in the GRANT.
>
> Which permissions of a table do you want to merge with which
> permissions of sequences?
>
> regards
> Szymon
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2013-06-20 09:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-06-19 09:35 Why sequence grant is separated from table? Rural Hunter <ruralhunter@gmail.com>
2013-06-19 09:47 ` Szymon Guz <mabewlun@gmail.com>
2013-06-19 10:06 ` Rural Hunter <ruralhunter@gmail.com>
2013-06-19 10:15 ` Szymon Guz <mabewlun@gmail.com>
2013-06-20 09:46 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2013-06-19 19:48 ` Craig James <cjames@emolecules.com>
2013-06-20 01:38 ` Rural Hunter <ruralhunter@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox