Обсуждение: Permissions on copy
Since the copy statement is behaving differently than the normal select stuff, I think we should eighter introduce a new permission (name it copy or dump) or include the copy into the rewrite system. I would vote for the first and implement a new command: unload to <filename> [delimiter '|'] <select statement>; -- and load from <filename> [delimiter '|'] <insert statement>; that does behave like the select. (please forgive my Informix background) As to the topic with setuid triggers and others, I think setuid procedures would be sufficient. These are implemented in Informix with the following simple syntax: create dba procedure .... Andreas
Andreas wrote: > > Since the copy statement is behaving differently than the normal select > stuff, > I think we should eighter introduce a new permission (name it copy or dump) > or include the copy into the rewrite system. > > I would vote for the first and implement a new command: > unload to <filename> [delimiter '|'] <select statement>; -- and > load from <filename> [delimiter '|'] <insert statement>; > that does behave like the select. (please forgive my Informix > background) Since the copy command checks for ACL_RD or ACL_WR on the relation a user can use copy on any table, where he has propper permissions. And triggers are fired too on COPY FROM (thanks for the push - found another place relevant for setuid triggers). The only things not working for copy are rewrite rules. But I think we should restrict rules to the view handling in the future and move forward by implementing a pure and really powerful procedural language. I vote for leaving copy as it is and take a look if we can fire triggers on SCAN which then could modify tuples instead of using rules at all. If the time an ON SCAN trigger is fired is just before the evaluation of any query qualification and put down into the heap access methods, anything should be fine except for performance maybe (but as I usually say: don't force it - use a bigger hammer). > > As to the topic with setuid triggers and others, I think setuid procedures > would be sufficient. > These are implemented in Informix with the following simple syntax: > create dba procedure .... Looks good. Alternatively/additionally something like ALTER FUNCTION funcname (argtypes) { SETUID | NOSETUID } might be useful. Parser etc. steps could mostly be copied from the DROP FUNCTION ... statement. And we have an unused boolean attribute proistrusted in the pg_proc struct. If we rename that to proissetuid anything is fine. As I said I already fiddled around with that and got setuid functions and triggers working (don't ask for code - usually I make a copy of the source tree, hack in totally ugly things until I know how to do it and throw anything away to be sure only development not hacking get's into PostgreSQL). Until later, Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
> > Since the copy statement is behaving differently than the normal select > stuff, > I think we should eighter introduce a new permission (name it copy or dump) > or include the copy into the rewrite system. > > I would vote for the first and implement a new command: > unload to <filename> [delimiter '|'] <select statement>; -- and > load from <filename> [delimiter '|'] <insert statement>; > that does behave like the select. (please forgive my Informix > background) Yes, I agree the Informix way of having load/unload, and having a SELECT capability so you can dump any data/join you want, not just a single table. Do I have votes to put this on the TODO list? > > As to the topic with setuid triggers and others, I think setuid procedures > would be sufficient. > These are implemented in Informix with the following simple syntax: > create dba procedure .... > > Andreas > > -- Bruce Momjian maillist@candle.pha.pa.us
On Fri, 20 Feb 1998, Bruce Momjian wrote: > > > > Since the copy statement is behaving differently than the normal select > > stuff, > > I think we should eighter introduce a new permission (name it copy or dump) > > or include the copy into the rewrite system. > > > > I would vote for the first and implement a new command: > > unload to <filename> [delimiter '|'] <select statement>; -- and > > load from <filename> [delimiter '|'] <insert statement>; > > that does behave like the select. (please forgive my Informix > > background) > > Yes, I agree the Informix way of having load/unload, and having a SELECT > capability so you can dump any data/join you want, not just a single > table. Do I have votes to put this on the TODO list? I'm not quite sure what we are voting on here...is it to implement permissions on a copy, like we do on 'select/delete/insert/etc'? If so, count me in...
> > On Fri, 20 Feb 1998, Bruce Momjian wrote: > > > > > > > Since the copy statement is behaving differently than the normal select > > > stuff, > > > I think we should eighter introduce a new permission (name it copy or dump) > > > or include the copy into the rewrite system. > > > > > > I would vote for the first and implement a new command: > > > unload to <filename> [delimiter '|'] <select statement>; -- and > > > load from <filename> [delimiter '|'] <insert statement>; > > > that does behave like the select. (please forgive my Informix > > > background) > > > > Yes, I agree the Informix way of having load/unload, and having a SELECT > > capability so you can dump any data/join you want, not just a single > > table. Do I have votes to put this on the TODO list? > > I'm not quite sure what we are voting on here...is it to implement > permissions on a copy, like we do on 'select/delete/insert/etc'? > > If so, count me in... Two things. First was a separate COPY priviledge, which I vote against. I see no real value to it, except to work around the problem that COPY doesn't use rules. Second, there was the idea of making copy allow a real select statement and not just a table name. If we do that, all goes through the executor, and you get view and rules working properly. May have some performance penalty, though it probabably will be minor. -- Bruce Momjian maillist@candle.pha.pa.us
On Fri, 20 Feb 1998, Bruce Momjian wrote: > Two things. First was a separate COPY priviledge, which I vote against. > I see no real value to it, except to work around the problem that COPY > doesn't use rules. Okay, I may be totally out in left field here (ie. unrelated), but what stops a user from doing a 'COPY out' on a table that they don't have SELECT privileges on? Kind of negates 'REVOKE ALL...', no? > Second, there was the idea of making copy allow a real select statement > and not just a table name. If we do that, all goes through the > executor, and you get view and rules working properly. May have some > performance penalty, though it probabably will be minor. This sounds reasonable...
> > On Fri, 20 Feb 1998, Bruce Momjian wrote: > > > Two things. First was a separate COPY priviledge, which I vote against. > > I see no real value to it, except to work around the problem that COPY > > doesn't use rules. > > Okay, I may be totally out in left field here (ie. unrelated), but > what stops a user from doing a 'COPY out' on a table that they don't have > SELECT privileges on? Kind of negates 'REVOKE ALL...', no? Yes I think a separate COPY permission makes no sense. > > > Second, there was the idea of making copy allow a real select statement > > and not just a table name. If we do that, all goes through the > > executor, and you get view and rules working properly. May have some > > performance penalty, though it probabably will be minor. > > This sounds reasonable... > > > -- Bruce Momjian maillist@candle.pha.pa.us
either-or or just a select, so it is forced to go through the executor? If we do that, what about reload the database.. it seems like copy should just dump the table as-is and not do funny tricks that make it impossible to COPY FROM... On Fri, 20 February 1998, at 12:10:29, The Hermit Hacker wrote: > > Second, there was the idea of making copy allow a real select statement > > and not just a table name. If we do that, all goes through the > > executor, and you get view and rules working properly. May have some > > performance penalty, though it probabably will be minor. > > This sounds reasonable... > >
> either-or or just a select, so it is forced to go through the > executor? If we do that, what about reload the database.. it seems > like copy should just dump the table as-is and not do funny tricks > that make it impossible to COPY FROM... Good point! Currently COPY FROM does fire insert triggers. Pg_dump uses that and if there are triggers a restore from a pg_dump output with triggers active might not recreate the database dumped. This does not only lead to triggers, constraints are also object to this because reference checks implemented by triggers or constraints must fail if the data isn't dumped by pg_dump in the right order of tables. For constraints it may be possible (if not yet done) for pg_dump, to analyze them and dump the tables in the right order. But pg_dump cannot know what a trigger checks or what it inserts/updates/deletes if fired. So we need at least a switch for the COPY command restricted to superusers or the DB owner telling COPY to suppress trigger firing. Then have a look at pg_dump if it analyzes constraints. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
Jan Wieck wrote: > > > either-or or just a select, so it is forced to go through the > > executor? If we do that, what about reload the database.. it seems > > like copy should just dump the table as-is and not do funny tricks > > that make it impossible to COPY FROM... > > Good point! > > Currently COPY FROM does fire insert triggers. Pg_dump uses > that and if there are triggers a restore from a pg_dump > output with triggers active might not recreate the database > dumped. This does not only lead to triggers, constraints are > also object to this because reference checks implemented by > triggers or constraints must fail if the data isn't dumped by > pg_dump in the right order of tables. For constraints it may > be possible (if not yet done) for pg_dump, to analyze them > and dump the tables in the right order. But pg_dump cannot > know what a trigger checks or what it inserts/updates/deletes > if fired. > > So we need at least a switch for the COPY command restricted > to superusers or the DB owner telling COPY to suppress ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Table owber ? > trigger firing. Then have a look at pg_dump if it analyzes > constraints. 1. usage: pg_dump [options] [dbname] -a dump out only the data, no schema -d dump data as proper insert strings ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Not only COPY should be satisfied. 2. pg_dump put triggers at the end of output -> triggers will not be fired on restoring. 3. As for referential constraints, it's better for pg_dump to create all tables without any of these, save data and then just use ALTER TABLE to add constraints. Vadim
Bruce Momjian wrote: > > Yes, I agree the Informix way of having load/unload, and having a SELECT > capability so you can dump any data/join you want, not just a single > table. Do I have votes to put this on the TODO list? I guess this means that it would be possible to load (for example) a file with 2 columns into a table with 3 columns? Like this: load "my2columnsfile" insert into mytable (mycol13, mycol1); Another thing which would be nice is to be able to select only some of the columns from the file, like this: load myfile(1,2,5) insert into mytable (x, y, z); This would be very useful (for me, at least). The 'raw' COPY should be restricted to superuser only, so that no user can bypass views, triggers and so on. /* m */