Re: [EXAMPLE] Overly zealous security of schemas... - Mailing list pgsql-hackers

> > > And actually, it looks like sequences have this same problem as
> > > well, only things are slightly worse there: you have to grant
> > > SELECT,UPDATE to a sequence to the public in order for those to
> > > work automagically. :-/
> > 
> > That's always been true though.
> 
> True, but while we're on the topic, I figured I'd give things a shot
> in the, could this be fixed dept.  Inserting into a view with a
> rule, the resulting query is run as the rule executor, not as the
> rule definer.  If that were somehow possible, then it'd remove the
> need to have a rule rewrite the (insert|update|delete|select) into a
> function call running at the privs of its definer and writing the
> functions that run at an elevated user.

Here's a little follow up on this post, here's an example of what I'm
trying to accomplish:


/* Begin example */
\c template1 pgsql
DROP DATABASE test;
CREATE DATABASE test WITH OWNER dba;

\c test dba
BEGIN;
CREATE SCHEMA s AUTHORIZATION dba;
CREATE TABLE s.f (i INT, PRIMARY KEY(i));
INSERT INTO s.f (i) VALUES (42);
CREATE TABLE s.t (i SERIAL, c INT, PRIMARY KEY(i));
ALTER TABLE s.t ADD FOREIGN KEY(c) REFERENCES s.f(i);

CREATE VIEW public.v AS SELECT c FROM s.t;

CREATE RULE t_ins AS       ON INSERT TO public.v DO INSTEAD       INSERT INTO s.t (c) VALUES (NEW.c);

REVOKE ALL ON SCHEMA s FROM PUBLIC;
GRANT SELECT ON public.v TO PUBLIC;
COMMIT;

\c test normal_user
INSERT INTO v VALUES (42);
/* End Example */

psql:test3.sql:30: ERROR:  s: permission denied

:-/ If you grant access to s, you get further along in the process:

-- As dba
GRANT USAGE ON SCHEMA s TO PUBLIC;

-- As normal_user
INSERT INTO v VALUES (42);
ERROR:  t_i_seq.nextval: you don't have permissions to set sequence t_i_seq

Still, :(.  So, if you grant access to the schema, and allow
SELECT,UPDATE on the sequence, then you're good to go:

-- As dba
GRANT SELECT,UPDATE ON s.t_i_seq TO PUBLIC;

-- As normal_user
INSERT INTO v VALUES (42);
INSERT 2126593 1

Whew.  Only problem is you have to know the name of all of the
sequences in use in the schema and open up access to the schema.  If
there was a way of executing a query generated by a RULE as the
definer, all of this would magically disappear... unless I'm missing
something.  It's possible to do the following to get around this
quirk, however it's really time consuming/error prone to do this with
tables with a large number of columns:

/* Begin */
\c test dba
CREATE FUNCTION public.t_ins(INT)   RETURNS BOOL   EXTERNAL SECURITY DEFINER   AS 'BEGIN       INSERT INTO s.t (c)
VALUES($1);       RETURN TRUE;
 
END;' LANGUAGE 'plpgsql';

CREATE OR REPLACE RULE t_ins AS       ON INSERT TO public.v DO INSTEAD       SELECT public.v_ins(NEW.c);
GRANT EXECUTE ON FUNCTION public.t_ins(INT) TO PUBLIC;
/* End */

This is the only way that I've been able to get around giving access
to the public to schema s and sequence s.t_i_seq.  Does this use case
make sense for why it'd be great to have:
    CREATE OR REPLACE RULE t_ins EXTERNAL SECURITY DEFINER ON ...

Some food for thought I suppose given it changes the context of an
insert rather dramatically:

test=> INSERT INTO v VALUES (42);v_ins
-------t
(1 row)


Does this make sense?  Do you think having a function + rule for every
view is the correct way to get around the perm barrier or would it be
more appropriate to have a rule run the resulting query at an elevated
priv? -sc

-- 
Sean Chittenden