On Sun, Dec 4, 2016 at 4:24 PM, Paul Ramsey <pram...@cleverelephant.ca>
wrote:
> When you create the student user, remove their create privs in public.
> Then create a scratch schema and grant them privs there.
> Finally, alter the student user so that the scratch schema appears FIRST
> in their search path. This will cause unqualified CREATE statements to
> create in the scratch schema.
> For full separation, give each student their own login and set the search
> path to
>
> "$user", public
>
> That way each student gets their own private scratch area, and it is used
> by default for their creates.
>
> P
>
>
Paul,
I've been avoiding giving each student an individual login role, but it
might be worth it to consider for a future term.
I've followed your (and Charles') advice to:
REVOKE CREATE ON SCHEMA public FROM public;
ALTER ROLE gus_faculty
SET search_path = scratch,public,tiger;
It also occurred to me that I don't want anyone changing data in
spatial_ref_sys. I think I should revoke everything *except* SELECT and
REFERENCES, and make this the default for new objects created in public
schema:
ALTER DEFAULT PRIVILEGES IN SCHEMA scratch
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER ON TABLES
FROM public;
Please let me know if this is inadvisable or violates accepted practice.
Best,
--Lee
