Thread: 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@unl.edu.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] 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.
> 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)
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@unl.edu.ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
> 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@unl.edu.ar
> Programador, Administrador | Centro de Telematica
> Universidad Nacional
> del Litoral
> -----------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
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@unl.edu.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. :^)