Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Mar 14, 2008 at 1:30 PM, Erik Jones <erik@xxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux