Re: [ADMIN] Why sequence grant is separated from table?
On 19/06/2013 12:47, Szymon Guz wrote: On 19 June 2013 11:35, Rural Hunter ruralhun...@gmail.com mailto:ruralhun...@gmail.com wrote: I really hate the error permission denied for sequence x 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
Re: [ADMIN] Why sequence grant is separated from table?
On 19 June 2013 11:35, Rural Hunter ruralhun...@gmail.com wrote: I really hate the error permission denied for sequence x 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
Re: [ADMIN] Why sequence grant is separated from table?
于 2013/6/19 17:47, Szymon Guz 写道: On 19 June 2013 11:35, Rural Hunter ruralhun...@gmail.com wrote: I really hate the error "permission denied for sequence x" 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? 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? 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. regards Szymon
Re: [ADMIN] Why sequence grant is separated from table?
On 19 June 2013 12:06, Rural Hunter ruralhun...@gmail.com wrote: 于 2013/6/19 17:47, Szymon Guz 写道: On 19 June 2013 11:35, Rural Hunter ruralhun...@gmail.com wrote: I really hate the error permission denied for sequence x 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
Re: [ADMIN] Why sequence grant is separated from table?
On Wed, Jun 19, 2013 at 2:35 AM, Rural Hunter ruralhun...@gmail.com wrote: I really hate the error permission denied for sequence x 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 ruralhun...@gmail.comwrote: 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-adminhttp://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Why sequence grant is separated from table?
Yes, that's also an acceptable solution. 于 2013/6/20 3:48, Craig James 写道: On Wed, Jun 19, 2013 at 2:35 AM, Rural Hunter ruralhun...@gmail.com wrote: I really hate the error "permission denied for sequence x" 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 ruralhun...@gmail.comwrote: 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
