> 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
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
