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

>  I have the following function: <SNIP>

Now that I know how to write the function, my design flaws and lack of
understanding are more apparent...

... I was trying to give all logged in users read-only access to the
public schema, and full access to the schema that corresponds to their
username.  The idea is that they can grab data from public with a
select into, and if they need to further process it, they need to
store the derived table in their personal schema.

Is this possible?  Is it possible to do without granting/revoking on
each table one-by-one in public (I had incorrect syntax in the
function below trying to grant select to a schema)?

Also, I revoked what I thought was everything possible on the public
schema, but a user is still able to create a table in that schema --
could someone explain:

oregon=# revoke create on schema public from foobar cascade;
REVOKE
oregon=# revoke all on schema public from foobar cascade;
REVOKE
oregon=# commit;
COMMIT
oregon=# revoke all on schema public from foobar cascade;
REVOKE
oregon=# set role foobar;
SET
oregon=> create table public.foo (id int);
CREATE TABLE
oregon=> commit;
COMMIT
oregon=>

This is for a class -- all the students need access to the data, but I
don't want them to practice deletes on the example table...  I
apologize for the stupid questions, but I haven't ever had call to
dive into the weirdnessess of grant/ revoke before.

Thanks again!

>
>  create function new_student (text) returns text as $$
>         declare
>                 wtf integer := 1;
>         begin
>         execute 'create schema ' || $1;
>         execute 'create role ' || $1 || 'LOGIN';
>         execute 'revoke all on schema public from ' || $1;
>         execute 'grant select on schema public to ' || $1;
>         execute 'grant all on schema ' || $1 || ' to ' || $1 || '
>  with grant option';
>         return $1;
>  end;
>  $$ language plpgsql
>  ;
>
>  When I run this with select new_student('foobar'), I get the following
>  error message
>

"Webb Sprague" <webb.sprague@gmail.com> writes:
> Also, I revoked what I thought was everything possible on the public
> schema, but a user is still able to create a table in that schema --
> could someone explain:

> oregon=# revoke create on schema public from foobar cascade;
> REVOKE

You've got a conceptual error here: the above only does something if
you'd previously done an explicit "GRANT TO foobar".  You haven't,
so there's nothing to revoke.

The reason people can create stuff in public is that by default,
create on schema public is granted to PUBLIC, ie the world.

Start with
    revoke all on schema public from public
and then grant only what you want.

            regards, tom lane

On Fri, Mar 14, 2008 at 12:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Webb Sprague" <webb.sprague@gmail.com> writes:
>  > Also, I revoked what I thought was everything possible on the public
>  > schema, but a user is still able to create a table in that schema --
>  > could someone explain:
>
>  > oregon=# revoke create on schema public from foobar cascade;
>  > REVOKE
>
>  You've got a conceptual error here:

Not surprising...

<SNIP>

>  Start with
>         revoke all on schema public from public
>  and then grant only what you want.

I will give it a go, and thanks!

>
>                         regards, tom lane
>

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

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.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

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

On Mar 14, 2008, at 3:43 PM, Webb Sprague wrote:

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

Yes.  The best way to handle this is to grant permissions to those
groups when the tables are created rather than doing it retroactively,
although the latter isn't really that difficult.  Assuming you've
revoked privileges from public to the public schema but then want to
grant SELECT privileges to some group, using this function would work
nicely:

CREATE OR REPLACE FUNCTION grant_select_for_group_to_public(rolename
varchar, tablename varchar)
    RETURNS VOID AS $$
BEGIN
    EXECUTE 'GRANT SELECT TO ' || rolename || ' ON ' || tablename || ';';
END;

Then you can do:

SELECT grant_select_for_group_to_public('some_group_name', c.relname)
FROM pg_class c, pg_namespace n
WHERE c.relnamespace=n.oid
    AND n.nspname='public';

Hopefully from that you can see how to use this type of setup for
further specialization.

> 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

Not sure about the nodetype bit but I do know that the user execute
the ALTER SCHEMA .. OWNER TO .. must be a member of the new owner
role, either directly or indirectly.  Could this be your problem.
Btw, I typically do schema level creation and grants as superuser
which would let you specify the schema's owner when you create the
schema

CREATE SCHEMA foobar AUTHORIZATION foobar;

Also, you don't need to grant privileges on a schema to its owner.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Thanks to Eric and Tom, I think I have got it.  Here is the function
for adding a new student, who can select anything in public and can do
anything at all in their own schema.

revoke all on schema public from public;   -- done only once
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 authorization ' || $1 ;

        -- public schema
        execute 'revoke all on schema public from ' || $1;
        execute 'grant usage on schema public to ' || $1;
        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 ;
select new_student ('fobar'); --etc