Implementação de melhores práticas de funções do Postgres

Question

Gente,

Eu poderia usar sua ajuda para tornar meu design de controle de acesso do usuário do Postgres melhor e mais alinhado às práticas recomendadas. Estou ajudando a implementar um pequeno servidor Postgres de produção, mas não sou administrador do banco de dados, então sei o suficiente para ser perigoso.

Existe um servidor com uma instalação do Postgres v9.2. Essa instalação hospeda vários bancos de dados, cada um servindo um "cliente" diferente. Em outras palavras, customer1 não deve, não deve usar o banco de dados2 e assim por diante. Durante as operações normais, os bancos de dados são acessados por uma instância correspondente do CakePHP, todos co-localizados no mesmo servidor que o Postgres. Embora possa haver otimizações possíveis nessa implantação, estou mais interessado em funções do Psql.

Com base no que li, parece que três tipos de papéis fazem sentido:

• Superusuário postgres com senha não padrão
• Uma função de administrador que não possui privilégios de superusuário para manutenção de rotina, criação de banco de dados, backup, restauração. Deve ser capaz de fazer qualquer coisa com todos os bancos de dados do cliente.
• Funções do usuário com apenas a capacidade de usar o CRUD em seus respectivos bancos de dados. Mais direitos em seu próprio banco de dados poderiam ser tolerados se ele limpasse a implementação.

Implementar esse design é onde estou muito menos confiante. Propriedade de DB versus tabela e bem como quem deve herdar de quem é um pouco enlameado. Abaixo estão minhas bases de dados e meus usuários. Isso é informação suficiente para avaliar a implementação?

     Role name |                   Attributes                   |     Member of     
    -----------+------------------------------------------------+-------------------
     admin     | Create role, Create DB                         | {user1, user2}
     postgres  | Superuser, Create role, Create DB              | {}
     user1     |                                                | {}
     user2     |                                                | {}

    postgres=# \l
                                 List of databases
       Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
    -----------+----------+----------+---------+-------+-----------------------
     admin     | postgres | UTF8     | en_US   | en_US | =Tc/postgres         +
               |          |          |         |       | postgres=CTc/postgres+
               |          |          |         |       | admin=CTc/postgres
     postgres  | postgres | UTF8     | en_US   | en_US | 
     template0 | postgres | UTF8     | en_US   | en_US | =c/postgres          +
               |          |          |         |       | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US   | en_US | =c/postgres          +
               |          |          |         |       | postgres=CTc/postgres
     user1     | admin    | UTF8     | en_US   | en_US | =Tc/admin            +
               |          |          |         |       | admin=CTc/admin      +
               |          |          |         |       | user1=CTc/admin
     user2     | admin    | UTF8     | en_US   | en_US | =Tc/admin            +
               |          |          |         |       | admin=CTc/admin      +
               |          |          |         |       | user2=CTc/admin

Para evitar conexões externas e senhas, pg_hba.conf é assim:

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

    

Answer 1

Eu sei que essa é uma pergunta antiga, mas vou tentar respondê-la mesmo agora, já que fiz algumas pesquisas relacionadas a isso.

O que você está tentando fazer é chamado multilocação no nível do banco de dados. Isso pode ser conseguido de duas maneiras:

1. Em um único cluster de banco de dados, um pouco como o OP descreveu, no entanto, minha escolha pessoal seria esta:

   O usuário do
   • postgres usa a autenticação peer e não tem permissão para conexões de senha. A autenticação MD5, na minha opinião, é uma prática ruim. Se você tiver algum tipo de problema com a consistência do banco de dados ou com esse tipo de coisa, ainda poderá fazer o login se deixar que o postgres use a autenticação peer.
   • Cada cliente deve ter seu próprio esquema e não o banco de dados. Há múltiplas razões para isto:
     • Possuir o banco de dados inteiro concederia muitos privilégios.
     • Possuir apenas tabelas específicas traria problemas para os desenvolvedores e sempre exigiria que os administradores adicionassem permissões e outras coisas.
     • Assim, em uma configuração normal, cada um deles teria acesso para criar coisas dentro de seus esquemas, incluindo tabelas, visualizações, acionadores etc.
     • Todos eles usam a mesma string de conexão, exceto o nome de usuário. No postgres, por padrão, se você tiver um esquema com o nome do seu usuário, ele estará automaticamente em seu search_path.
   • Eu optaria por não ter um usuário administrador que possa acessar cada esquema, como medida de segurança. Você deve fazer backups descarregando cada esquema com seu próprio usuário ou usando a técnica PITR do PostgreSQL. Você ainda precisaria usar o usuário postgres para criar novos esquemas, eu usaria uma regra sudo e um script para isso.
   • Muitas boas práticas de segurança recomendam a eliminação do esquema padrão - lá vamos nós.
   • Esta solução é extremamente adequada se o banco de dados para cada cliente for pequeno e você tiver muitos clientes.
   • Se o seu aplicativo lida com multilocação, ele pode usar um único conjunto de conexões para todos os clientes. Naturalmente, isso elimina muitos dos aprimoramentos de segurança acima, mas pode ter benefícios de desempenho, especialmente quando você tem um grande número de clientes (se você tiver entre 500-1000 origens de dados separadas e usar pool de conexões, será bastante complicado). / li>

2. Cada cliente recebe seu próprio cluster de banco de dados. Esta é a minha solução preferida, especialmente porque geralmente trabalho com aplicativos que possuem grandes bancos de dados para cada cliente.

   • Este traz uma excelente separação de dados. Você pode usar volumes de armazenamento separados para cada cliente, alocar limitações de CPU e memória (usando o docker?).
   • Flexibilidade realmente boa sobre o que cada cliente precisa em sua instância. Eles podem ser semelhantes ou ter recursos distintos.
   • Muito fácil de escalar em ambas as direções (para cima e para fora).
   • Eu também uso IPs separados em locais onde cada cluster escuta conexões, fazendo com que o scale-out não precise de reconfiguração da fonte de dados.
   Os backups de PITR
   • são por cliente, por isso, será mais fácil restaurar um único cliente em comparação com a multilocação por esquema.
   • Em configurações complexas, cada cliente pode precisar de vários bancos de dados, esquemas, usuários e funções, etc., portanto, essa é uma solução muito melhor nesses casos.

Você também pode usar uma combinação das opções acima e usar o pgBouncer como um roteador.