Eu quero migrar um aplicativo bastante simples, interno e orientado a banco de dados do SQLite3 para o PostgreSQL 9.3 e apertar as permissões no banco de dados à medida que eu for.
O aplicativo atualmente consiste em um comando para atualizar os dados; e um para consultá-lo. Naturalmente, também precisarei manter o banco de dados de outras maneiras (criar novas tabelas, visualizações, gatilhos, etc).
Embora este aplicativo seja o único hospedado no servidor a princípio, prefiro assumir que ele pode ser hospedado em um servidor com outros bancos de dados no futuro, em vez de ter que embaralhar mais tarde se isso for necessário em o futuro.
Eu acho que esses seriam um conjunto de requisitos bastante comum, mas estou tendo problemas para encontrar um tutorial simples explicando como configurar um novo banco de dados no PostgreSQL, com esse tipo de separação usuário/privilégio. As referências são extensas sobre grupos, usuários, funções, bancos de dados, esquemas e domínio; mas acho-os confusos.
Aqui está o que eu tentei até agora (de dentro psqlcomo 'postgres'):
CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;
\connect hostdb
CREATE SCHEMA hostdb;
CREATE USER hostdb_admin WITH PASSWORD 'youwish';
CREATE USER hostdb_mgr WITH PASSWORD 'youwish2';
CREATE USER hostdb_usr WITH PASSWORD 'youwish3';
GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin;
GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;
Mas eu não estou recebendo a semântica pretendida. Eu quero tê-lo configurado para que apenas as hostdb_admintabelas possam criar (e descartar e alterar); o hostdb_mgrpode ler, inserir, atualizar e excluir em todas as tabelas por padrão; e hostdb_usrsó pode ler todas as tabelas (e visualizações).
Quando tentei isso, descobri que era capaz de criar tabelas hostdbcomo qualquer um desses usuários; mas, para cada usuário, eu só poderia ler ou modificar tabelas criadas por esse usuário - a menos que eu use um GRANT.
Eu estou supondo que há algo faltando entre CREATE DATABASEe CREATE SCHEMA, algo para aplicar SCHEMAao DATABASE?
(À medida que as coisas ficarem mais avançadas, também terei perguntas para aplicar restrições semelhantes em TRIGGERS, procedimentos armazenados VIEWSe talvez outros objetos).
Onde posso encontrar um guia decente, tutorial ou série de vídeos sobre isso?
