Re: Making a schema "read-only" (was Unexpected message in grant/revoke script)

On Fri, Mar 14, 2008 at 1:30 PM, Erik Jones <erik(at)myemma(dot)com> wrote:
>
>
> On Mar 14, 2008, at 3:22 PM, Webb Sprague wrote:
>
> >>> Start with
> >>> revoke all on schema public from public
> >>> and then grant only what you want.
> >
> > Oh -- to grant select permissions on all the tables in the public
> > schema, do I have to do it table-by-table? I know I can write a loop
> > an use information_schema if necessary, but if I don't have to I would
> > like to know.
> >
> > Thx
>
> Typically what's done is to do that for one user "group" and then make
> all of your actual login users members of that group.

Oh -- I guess overlapping groups would work, but wouldn't I still have
to give select permissions to this collective role by going
table-by-table? And I want a separate role for each person, so that
they can't stomp all over each others data.

And now I have a new problem -- what could be wrong with the alter
schema owner to line below:

revoke all on schema public from public;
create or replace function new_student (text) returns void as $$
declare
t_name text;
begin
-- personal schema
execute 'create role ' || $1 || ' LOGIN';
execute 'create schema ' || $1;
execute 'alter schema ' || $1 || ' owner to ' || $1; -- THIS
THROWS AN ERROR see below
execute 'grant all on schema ' || $1 || ' to ' || $1 || '
with grant option';

for t_name in select table_name from information_schema.tables
where table_schema = 'public' order by table_name loop
raise notice 'granting select to %s on %s', $1, t_name;
execute 'grant select on ' || t_name || ' to ' || $1;
end loop;

end;
$$ language plpgsql ;

oregon=# select new_student('foobarbar');
ERROR: unrecognized node type: 1651470182
CONTEXT: SQL statement "alter schema foobarbar owner to foobarbar"
PL/pgSQL function "new_student" line 7 at EXECUTE statement

Thanks again for helping me understand this most tedious of database stuff....
w