Re: Extensions and privileges in public schema

Поиск
Список
Период
Сортировка
От Lee Hachadoorian
Тема Re: Extensions and privileges in public schema
Дата
Msg-id CANnCtnJZ=ovvBNxbs9MKViNLyi0O8vAUJ-B-RJPssLtfOZXmgQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Extensions and privileges in public schema  (Paul Ramsey <pramsey@cleverelephant.ca>)
Список pgsql-general
Дерево обсуждения


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

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: INSERT - UPDATE throughput oscillating and SSD activity after stopping the client
Следующее
От: Tom DalPozzo
Дата:
Сообщение: Re: INSERT - UPDATE throughput oscillating and SSD activity after stopping the client