Re: Extensions and privileges in public schema

[Lee Hachadoorian <Lee.Hachadoorian+L@xxxxxxxxx>]

On Sun, Dec 4, 2016 at 4:24 PM, Paul Ramsey <pramsey@xxxxxxxxxxxxxxxxx> 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