Обсуждение: 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 */