Re: [EXAMPLE] Overly zealous security of schemas...

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