Re: Extensions and privileges in public schema

On Sun, Dec 4, 2016 at 4:24 PM, Paul Ramsey <pramsey(at)cleverelephant(dot)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