Re: something smells bad

I'm haveing a very hard time with these views, rules, and I don't know if
it's a programing problem, or it's that I don't understand the docs. I have
this:

webunl=> \d
List of relations
Name | Type | Owner
-----------------------------+----------+--------
admin_view | view | martin
admin_view_categ | view | martin
admin_view_docente | view | martin
admin_view_facultades | view | martin
admin_view_materias | view | martin
admin_view_modal | view | martin
area | table | martin
area_id_area_seq | sequence | martin
carrera | table | martin
carrera_id_curso_seq | sequence | martin
categ | table | martin
categ_id_categ_seq | sequence | martin
docentes | table | martin
docentes_id_docente_seq | sequence | martin
facultad | table | martin
facultad_id_fac_seq | sequence | martin
inscripcion | table | martin
log_carrera | table | martin
log_carrera_id_log_seq | sequence | martin
materias | table | martin
materias_id_mat_seq | sequence | martin
modalidad | table | martin
nexo_categ | table | martin
nexo_facultad | table | martin
niveles | table | martin
niveles_id_nivel_seq | sequence | martin
resol | table | martin
sub_area | table | martin
sub_area_id_subarea_seq | sequence | martin
tipo_cursado | table | martin
tipo_cursado_id_cursado_seq | sequence | martin
tipo_modal | table | martin
tipo_modal_id_mod_seq | sequence | martin
(33 rows)

webunl=> INSERT INTO admin_view
webunl->
(titulo,subarea,descripcion,matricula,nivel,requisitos,duracion,cupos,numero,year,fecha,fecha_ini,fecha_fin,lugar,informes
,director,carrera)
webunl-> VALUES ('y este el tiutulo',14,'descripcion',0,1,'Y bueno, los
requisitos
son.....',12,0,34534,2000,'6/6/2000','10/10/2001','30/12/2001','Donde
webunl'> mas.......','Para mas info, limpie','Quien, yo?','Esta es una
carrera');
INSERT 240131 1
webunl=> \c webunl - mariana
You are now connected to database webunl.
\c: extra argument 'mariana' ignored
webunl=> \c webunl mariana
You are now connected to database webunl as user mariana.
webunl=> INSERT INTO admin_view
webunl->
(titulo,subarea,descripcion,matricula,nivel,requisitos,duracion,cupos,numero,year,fecha,fecha_ini,fecha_fin,lugar,informes
,director,carrera)
webunl-> VALUES ('y este el tiutulo',14,'descripcion',0,1,'Y bueno, los
requisitos
son.....',12,0,34534,2000,'6/6/2000','10/10/2001','30/12/2001','Donde
webunl'> mas.......','Para mas info, limpie','Quien, yo?','Esta es una
carrera');
ERROR: carrera_id_curso_seq.nextval: you don't have permissions to set
sequence carrera_id_curso_seq
webunl=> select version();
version
------------------------------------------------------------------
PostgreSQL 7.1.2 on sparc-sun-solaris2.8, compiled by GCC 2.95.2
(1 row)

The rules are OK as for what I see, but the other user insert even though the
user mariana has ALL permissions on the views (all the views) through her
group (webunl_admin)

webunl=> select * from pg_group;
groname | grosysid | grolist
--------------+----------+------------------
webunl_admin | 500 | {1015,1005,1014}
(1 row)

webunl=> select * from pg_user;
usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
passwd | valuntil
----------+----------+-------------+----------+----------+-----------+----------+----------
postgres | 1013 | t | t | t | t |
******** |
webmail | 33 | t | f | t | t |
******** |
todo | 1004 | f | f | f | f |
******** |
aroman | 1014 | t | f | f | f |
******** |
mariana | 1015 | f | f | f | f |
******** |
martin | 1005 | f | f | f | f |
******** |
(6 rows)

What's wrong with this? As you can see, mariana belongs to the webunl_admin
group, and I (martin) have granted ALL permissions to that group.
Any ideas?

Saludos... :-)

--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

On Jue 07 Jun 2001 00:58, you wrote:
> 1) Please don't crosspost to both hackers and general
>
> On Wed, 6 Jun 2001, [iso-8859-1] Martín Marqués wrote:
> > ERROR: carrera_id_curso_seq.nextval: you don't have permissions to set
> > sequence carrera_id_curso_seq
>
> Because of the way postgres works, you need to grant write permission on
> the carrera_id_curso_seq for your inserts to succeed.

This doesn't sound logical. The user from the group granted can insert data,
but not in a SERIAL field?
This is not what the "RULES and permissions" documentation says.

More info:

webunl=> \dp
Access permissions for database "webunl"
Relation | Access permissions
-----------------------------+------------------------------------------------
admin_view | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_categ | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_docente | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_facultades | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_materias | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_modal | {"=r","martin=arwR","group webunl_admin=arwR"}
area | {"=r","martin=arwR"}
area_id_area_seq |
carrera | {"=r","martin=arwR"}
carrera_id_curso_seq |
categ | {"=r","martin=arwR"}
categ_id_categ_seq |
docentes | {"=r","martin=arwR"}
docentes_id_docente_seq |
facultad | {"=r","martin=arwR"}
facultad_id_fac_seq |
inscripcion | {"=r","martin=arwR"}
log_carrera | {"=","martin=arwR"}
log_carrera_id_log_seq |
materias | {"=r","martin=arwR"}
materias_id_mat_seq |
modalidad | {"=r","martin=arwR"}
nexo_categ | {"=r","martin=arwR"}
nexo_facultad | {"=r","martin=arwR"}
niveles | {"=r","martin=arwR"}
niveles_id_nivel_seq |
resol | {"=r","martin=arwR"}
sub_area | {"=r","martin=arwR"}
sub_area_id_subarea_seq |
tipo_cursado | {"=r","martin=arwR"}
tipo_cursado_id_cursado_seq |
tipo_modal | {"=r","martin=arwR"}
tipo_modal_id_mod_seq |

As you can see, the permissions look OK.

Saludos... :-)

--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

On Jue 07 Jun 2001 01:14, Alex Pilosov wrote:
> On Wed, 6 Jun 2001, [iso-8859-1] Martín Marqués wrote:
> > On Jue 07 Jun 2001 00:58, you wrote:
> > > 1) Please don't crosspost to both hackers and general
>
> (plonk myself)

Sorry, I sent it to both because it's what I usually see on things that seem
to not work on Postgres.

> > > On Wed, 6 Jun 2001, [iso-8859-1] Martín Marqués wrote:
> > > > ERROR: carrera_id_curso_seq.nextval: you don't have permissions to
> > > > set sequence carrera_id_curso_seq
> > >
> > > Because of the way postgres works, you need to grant write permission
> > > on the carrera_id_curso_seq for your inserts to succeed.
> >
> > This doesn't sound logical. The user from the group granted can insert
> > data, but not in a SERIAL field?
> > This is not what the "RULES and permissions" documentation says.
>
> I guess the documentation has to be changed then.
>
> Postgres sequences are really non-transparent, and you have to be aware of
> that. Such as, when you drop the table, sequence won't get dropped,
> permissions are separate on table and sequence, and various other things.

I am aware of this.

> > area_id_area_seq |
> > carrera_id_curso_seq |
> > categ_id_categ_seq |
> > docentes_id_docente_seq |
> > facultad_id_fac_seq |
> > log_carrera_id_log_seq |
> > materias_id_mat_seq |
> > niveles_id_nivel_seq |
> >
> > As you can see, the permissions look OK.
>
> You must have permissions on _seq as well as on the underlying table...

OK, now I'm more then astonished!
Why was I able to insert as martin then?
Isn't it true (as the docs say) that when I execute a query over a view with
rules, the rules (querys in the DO of the RULE) are executed with permssions
of the owner of the rule (or the view? Any way, martin is owner of both) and
not of the user that executed the query?

I am totally puzzeled! %-P

Saludos... :-)

--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

1) Please don't crosspost to both hackers and general

On Wed, 6 Jun 2001, [iso-8859-1] Martn Marqus wrote:

> ERROR: carrera_id_curso_seq.nextval: you don't have permissions to set
> sequence carrera_id_curso_seq
Because of the way postgres works, you need to grant write permission on
the carrera_id_curso_seq for your inserts to succeed.

> The rules are OK as for what I see, but the other user insert even though the
> user mariana has ALL permissions on the views (all the views) through her
> group (webunl_admin)
>
>
> webunl=> select * from pg_group;
> groname | grosysid | grolist
> --------------+----------+------------------
> webunl_admin | 500 | {1015,1005,1014}
> (1 row)

On Wed, 6 Jun 2001, [iso-8859-1] Martn Marqus wrote:

> On Jue 07 Jun 2001 00:58, you wrote:
> > 1) Please don't crosspost to both hackers and general
> >
(plonk myself)

> > On Wed, 6 Jun 2001, [iso-8859-1] Martn Marqus wrote:
> > > ERROR: carrera_id_curso_seq.nextval: you don't have permissions to set
> > > sequence carrera_id_curso_seq
> >
> > Because of the way postgres works, you need to grant write permission on
> > the carrera_id_curso_seq for your inserts to succeed.
>
> This doesn't sound logical. The user from the group granted can insert data,
> but not in a SERIAL field?
> This is not what the "RULES and permissions" documentation says.
I guess the documentation has to be changed then.

Postgres sequences are really non-transparent, and you have to be aware of
that. Such as, when you drop the table, sequence won't get dropped,
permissions are separate on table and sequence, and various other things.

> area_id_area_seq |
> carrera_id_curso_seq |
> categ_id_categ_seq |
> docentes_id_docente_seq |
> facultad_id_fac_seq |
> log_carrera_id_log_seq |
> materias_id_mat_seq |
> niveles_id_nivel_seq |
>
> As you can see, the permissions look OK.
You must have permissions on _seq as well as on the underlying table...

On Wed, 6 Jun 2001, [iso-8859-1] Martn Marqus wrote:

> OK, now I'm more then astonished!
> Why was I able to insert as martin then?
> Isn't it true (as the docs say) that when I execute a query over a view with
> rules, the rules (querys in the DO of the RULE) are executed with permssions
> of the owner of the rule (or the view? Any way, martin is owner of both) and
> not of the user that executed the query?

No. With both views and rules, the actions are executed as the user who
executed the query. I don't know if there are plans to allow the 'execute
as owner' for rules, right now this option only exists for the triggers.

Sending this to the Hackers list because I think that there may be a bug.

On Jue 07 Jun 2001 02:27, Alex Pilosov wrote:
> On Wed, 6 Jun 2001, [iso-8859-1] Martín Marqués wrote:
> > OK, now I'm more then astonished!
> > Why was I able to insert as martin then?
> > Isn't it true (as the docs say) that when I execute a query over a view
> > with rules, the rules (querys in the DO of the RULE) are executed with
> > permssions of the owner of the rule (or the view? Any way, martin is
> > owner of both) and not of the user that executed the query?
>
> No. With both views and rules, the actions are executed as the user who
> executed the query. I don't know if there are plans to allow the 'execute
> as owner' for rules, right now this option only exists for the triggers.

Well, after todays tests, I have to say that rules are executed with owner
privileges, and not users.
This is the output:

webunl=> \dp

Access permissions for database "webunl"
Relation | Access permissions
-----------------------------+------------------------------------------------
admin_view | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_categ | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_docente | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_facultades | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_materias | {"=r","martin=arwR","group webunl_admin=arwR"}
admin_view_modal | {"=r","martin=arwR","group webunl_admin=arwR"}
area | {"=r","martin=arwR"}
area_id_area_seq |
carrera | {"=r","martin=arwR"}
carrera_id_curso_seq | {"=","martin=arwR"}
categ | {"=r","martin=arwR"}
categ_id_categ_seq |
docentes | {"=r","martin=arwR"}
docentes_id_docente_seq | {"=","martin=arwR"}
facultad | {"=r","martin=arwR"}
facultad_id_fac_seq |
inscripcion | {"=r","martin=arwR"}
log_carrera | {"=","martin=arwR"}
log_carrera_id_log_seq | {"=","martin=arwR"}
materias | {"=r","martin=arwR"}
materias_id_mat_seq | {"=","martin=arwR"}
modalidad | {"=r","martin=arwR"}
nexo_categ | {"=r","martin=arwR"}
nexo_facultad | {"=r","martin=arwR"}
niveles | {"=r","martin=arwR"}
niveles_id_nivel_seq |
resol | {"=r","martin=arwR"}
sub_area | {"=r","martin=arwR"}
sub_area_id_subarea_seq |
tipo_cursado | {"=r","martin=arwR"}
tipo_cursado_id_cursado_seq |
tipo_modal | {"=r","martin=arwR"}
tipo_modal_id_mod_seq |
(33 rows)

webunl=> GRANT ALL ON
carrera_id_curso_seq,docentes_id_docente_seq,log_carrera_id_log_seq,materias_id_mat_seq
TO GROUP webunl_admin;
CHANGE
webunl=> \c webunl mariana
You are now connected to database webunl as user mariana.
webunl=> INSERT INTO admin_view
webunl->
(titulo,subarea,descripcion,matricula,nivel,requisitos,duracion,cupos,numero,year,fecha,fecha_ini,fecha_fin,lugar,informes
,director,carrera)
webunl-> VALUES ('y este el tiutulo',14,'descripcion',0,1,'Y bueno, los
requisitos
son.....',12,0,34534,2000,'6/6/2000','10/10/2001','30/12/2001','Donde
webunl'> mas.......','Para mas info, limpie','Quien, yo?','Esta es una
carrera');
INSERT 240135 1
webunl=>

Now, mariana is a member of the webunl_admin group, and before the GRANT to
all those sequences, the query (INSERT) shes executing here gave an error
when trying to do a carrera_id_curso_seq:next (is this the right sintax?).

Saludos.... :-)

--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

On Thu, 7 Jun 2001, [iso-8859-1] Martn Marqus wrote:

> On Jue 07 Jun 2001 02:27, Alex Pilosov wrote:
> > On Wed, 6 Jun 2001, [iso-8859-1] Martn Marqus wrote:
> > > OK, now I'm more then astonished!
> > > Why was I able to insert as martin then?
> > > Isn't it true (as the docs say) that when I execute a query over a view
> > > with rules, the rules (querys in the DO of the RULE) are executed with
> > > permssions of the owner of the rule (or the view? Any way, martin is
> > > owner of both) and not of the user that executed the query?
> >
> > No. With both views and rules, the actions are executed as the user who
> > executed the query. I don't know if there are plans to allow the 'execute
> > as owner' for rules, right now this option only exists for the triggers.
>
> Well, after todays tests, I have to say that rules are executed with owner
> privileges, and not users.
> This is the output:
*mutter* I was mistaken, indeed, views are executed with permissions of
owner of the view.

-alex

> Sending this to the Hackers list because I think that there may be a bug.

Did you include the smell in the email message? :-)

>
> On Jue 07 Jun 2001 02:27, Alex Pilosov wrote:
> > On Wed, 6 Jun 2001, [iso-8859-1] Mart?n Marqu?s wrote:
> > > OK, now I'm more then astonished!
> > > Why was I able to insert as martin then?
> > > Isn't it true (as the docs say) that when I execute a query over a view
> > > with rules, the rules (querys in the DO of the RULE) are executed with
> > > permssions of the owner of the rule (or the view? Any way, martin is
> > > owner of both) and not of the user that executed the query?
> >
> > No. With both views and rules, the actions are executed as the user who
> > executed the query. I don't know if there are plans to allow the 'execute
> > as owner' for rules, right now this option only exists for the triggers.
>
> Well, after todays tests, I have to say that rules are executed with owner
> privileges, and not users.
> This is the output:
>
> webunl=> \dp
>
> Access permissions for database "webunl"
> Relation | Access permissions
> -----------------------------+------------------------------------------------
> admin_view | {"=r","martin=arwR","group webunl_admin=arwR"}
> admin_view_categ | {"=r","martin=arwR","group webunl_admin=arwR"}
> admin_view_docente | {"=r","martin=arwR","group webunl_admin=arwR"}
> admin_view_facultades | {"=r","martin=arwR","group webunl_admin=arwR"}
> admin_view_materias | {"=r","martin=arwR","group webunl_admin=arwR"}
> admin_view_modal | {"=r","martin=arwR","group webunl_admin=arwR"}
> area | {"=r","martin=arwR"}
> area_id_area_seq |
> carrera | {"=r","martin=arwR"}
> carrera_id_curso_seq | {"=","martin=arwR"}
> categ | {"=r","martin=arwR"}
> categ_id_categ_seq |
> docentes | {"=r","martin=arwR"}
> docentes_id_docente_seq | {"=","martin=arwR"}
> facultad | {"=r","martin=arwR"}
> facultad_id_fac_seq |
> inscripcion | {"=r","martin=arwR"}
> log_carrera | {"=","martin=arwR"}
> log_carrera_id_log_seq | {"=","martin=arwR"}
> materias | {"=r","martin=arwR"}
> materias_id_mat_seq | {"=","martin=arwR"}
> modalidad | {"=r","martin=arwR"}
> nexo_categ | {"=r","martin=arwR"}
> nexo_facultad | {"=r","martin=arwR"}
> niveles | {"=r","martin=arwR"}
> niveles_id_nivel_seq |
> resol | {"=r","martin=arwR"}
> sub_area | {"=r","martin=arwR"}
> sub_area_id_subarea_seq |
> tipo_cursado | {"=r","martin=arwR"}
> tipo_cursado_id_cursado_seq |
> tipo_modal | {"=r","martin=arwR"}
> tipo_modal_id_mod_seq |
> (33 rows)
>
> webunl=> GRANT ALL ON
> carrera_id_curso_seq,docentes_id_docente_seq,log_carrera_id_log_seq,materias_id_mat_seq
> TO GROUP webunl_admin;
> CHANGE
> webunl=> \c webunl mariana
> You are now connected to database webunl as user mariana.
> webunl=> INSERT INTO admin_view
> webunl->
> (titulo,subarea,descripcion,matricula,nivel,requisitos,duracion,cupos,numero,year,fecha,fecha_ini,fecha_fin,lugar,informes
> ,director,carrera)
> webunl-> VALUES ('y este el tiutulo',14,'descripcion',0,1,'Y bueno, los
> requisitos
> son.....',12,0,34534,2000,'6/6/2000','10/10/2001','30/12/2001','Donde
> webunl'> mas.......','Para mas info, limpie','Quien, yo?','Esta es una
> carrera');
> INSERT 240135 1
> webunl=>
>
> Now, mariana is a member of the webunl_admin group, and before the GRANT to
> all those sequences, the query (INSERT) shes executing here gave an error
> when trying to do a carrera_id_curso_seq:next (is this the right sintax?).
>
> Saludos.... :-)
>
> --
> Cualquiera administra un NT.
> Ese es el problema, que cualquiera administre.
> -----------------------------------------------------------------
> Martin Marques | mmarques(at)unl(dot)edu(dot)ar
> Programador, Administrador | Centro de Telematica
> Universidad Nacional
> del Litoral
> -----------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On Lun 11 Jun 2001 07:28, you wrote:
> > Sending this to the Hackers list because I think that there may be a bug.
>
> Did you include the smell in the email message? :-)

Ohhh, I don't have already the device to send odors on the net. ;-P

Anyway, I would like to know if I'm wrong. I had to change privileges on the
sequences to make this work. I'm not happy with the solution (mine) because I
thought that using views and rules would be enough, which was almost.
Sequences got out. :-)

Saludos... :-)

--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

Bruce Momjian wrote:
>
> > Sending this to the Hackers list because I think that there may be a bug.
>
> Did you include the smell in the email message? :-)
>
What sort of card do I need to read, I mean smell this?

Does it help that this machine runs Win95, thereby already stinking
somewhat?

I wish IBM would hurry and release the StinkPad. :^)