Horton
Menu

Thread

  1. Permissions on copy

    Zeugswetter Andreas IZ5 <andreas.zeugswetter@telecom.at> — 1998-02-20T11:02:52Z

    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

  2. Re: [HACKERS] Permissions on copy

    Jan Wieck <jwieck@debis.com> — 1998-02-20T12:30:13Z

    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) #

  3. Re: [HACKERS] Permissions on copy

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-20T15:45:30Z

    > 
> 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

  4. Re: [HACKERS] Permissions on copy

    Marc Fournier <scrappy@hub.org> — 1998-02-20T16:56:47Z

    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...

  5. Re: [HACKERS] Permissions on copy

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-20T17:03:10Z

    > 
> 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

  6. Re: [HACKERS] Permissions on copy

    Marc Fournier <scrappy@hub.org> — 1998-02-20T17:10:29Z

    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...

  7. Re: [HACKERS] Permissions on copy

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-20T17:22:03Z

    > 
> 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

  8. Re: [HACKERS] Permissions on copy

    Brett McCormick <brett@work.chicken.org> — 1998-02-20T22:11:44Z

    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...
> 
>

  9. Re: [HACKERS] Permissions on copy

    Jan Wieck <jwieck@debis.com> — 1998-02-21T11:34:38Z

    > 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) #

  10. Re: [HACKERS] Permissions on copy

    Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-22T10:12:32Z

    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

  11. Re: [HACKERS] Permissions on copy

    Mattias Kregert <matti@algonet.se> — 1998-02-23T11:22:24Z

    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 */