=> CREATE ROLE employee LOGIN PASSWORD 'employee';
ERROR: role "employee" already exists
=> CREATE ROLE buyer LOGIN PASSWORD 'buyer';
CREATE ROLE
Настройки по умолчанию разрешают подключение с локального адреса по паролю. Нас это устраивает.
У роли public надо отозвать лишние привилегии.
=> REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA bookstore FROM public;
REVOKE
=> REVOKE CONNECT ON DATABASE bookstore FROM public;
REVOKE
=> ALTER FUNCTION get_catalog(text,text,boolean) SECURITY DEFINER;
ALTER FUNCTION
=> ALTER FUNCTION update_catalog() SECURITY DEFINER;
ALTER FUNCTION
=> ALTER FUNCTION add_author(text,text,text) SECURITY DEFINER;
ALTER FUNCTION
=> ALTER FUNCTION add_book(text,integer[]) SECURITY DEFINER;
ALTER FUNCTION
=> ALTER FUNCTION buy_book(integer) SECURITY DEFINER;
ALTER FUNCTION
=> ALTER FUNCTION book_name(integer,text,integer) SECURITY DEFINER;
ALTER FUNCTION
=> ALTER FUNCTION authors(books) SECURITY DEFINER;
ALTER FUNCTION
Покупатель должен иметь доступ к поиску книг и их покупке.
=> GRANT CONNECT ON DATABASE bookstore TO buyer;
GRANT
=> GRANT USAGE ON SCHEMA bookstore TO buyer;
GRANT
=> GRANT EXECUTE ON FUNCTION get_catalog(text,text,boolean) TO buyer;
GRANT
=> GRANT EXECUTE ON FUNCTION buy_book(integer) TO buyer;
GRANT
Сотрудник должен иметь доступ к просмотру и добавлению книг и авторов, а также к каталогу для заказа книг.
=> GRANT CONNECT ON DATABASE bookstore TO employee;
GRANT
=> GRANT USAGE ON SCHEMA bookstore TO employee;
GRANT
=> GRANT SELECT,UPDATE(onhand_qty) ON catalog_v TO employee;
GRANT
=> GRANT SELECT ON authors_v TO employee;
GRANT
=> GRANT EXECUTE ON FUNCTION book_name(integer,text,integer) TO employee;
GRANT
=> GRANT EXECUTE ON FUNCTION authors(books) TO employee;
GRANT
=> GRANT EXECUTE ON FUNCTION author_name(text,text,text) TO employee;
GRANT
=> GRANT EXECUTE ON FUNCTION add_book(text,integer[]) TO employee;
GRANT
=> GRANT EXECUTE ON FUNCTION add_author(text,text,text) TO employee;
GRANT