Como gerenciar DEFAULT PRIVILEGES para USERs em um DATABASE vs SCHEMA?

O Postgres 14 adiciona os papéis predefinidos pg_read_all_data e pg_write_all_data, que permitem atalhos para os propósitos implícitos nos nomes.

Onde posso encontrar um guia decente, tutorial ou série de vídeos sobre isso?

Você encontrará tudo no manual. Links abaixo.
Concedido, o assunto não é trivial e às vezes confuso. Aqui está uma receita para o caso de uso:

Receita

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).

Como superusuário postgres:

CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

Se você deseja um administrador mais poderoso que também possa gerenciar bancos de dados e funções, adicione os atributos de função CREATEDBeCREATEROLE acima.

Conceda cada função ao próximo nível superior, para que todos os níveis "herdem" pelo menos o conjunto de privilégios do próximo nível inferior (em cascata):

GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

Estou nomeando o esquema schma(o hostdbque não seria confuso). Escolha qualquer nome. Opcionalmente , torne schma_admino proprietário do esquema:

CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional 

Para and drop and alterver notas abaixo.

O Postgres 14 adiciona as funções pré-definidas, sem login pg_read_all_datae pg_write_all_datapara fornecer acesso somente leitura/gravação a todos os objetos em todos os esquemas. Isso vai além do que está sendo perguntado aqui, mas pode ser útil. Ver:

• PostgreSQL: Dê todas as permissões a um usuário em um banco de dados PostgreSQL

À 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.

As vistas são especiais.

O manual:

... (mas observe que ALL TABLESé considerado incluir visualizações e tabelas estrangeiras).

E para visualizações atualizáveis :

Observe que o usuário que executa a inserção, atualização ou exclusão na exibição deve ter o privilégio de inserção, atualização ou exclusão correspondente na exibição. Além disso, o proprietário da exibição deve ter os privilégios relevantes nas relações de base subjacentes, mas o usuário que executa a atualização não precisa de nenhuma permissão nas relações de base subjacentes (consulte a Seção 38.5 ).

Os gatilhos também são especiais. Você precisa do TRIGGERprivilégio na mesa e:

• Quais são os privilégios necessários para executar uma função de gatilho no PostgreSQL 8.4?

Anotações importantes

Propriedade

Se você deseja permitir schma_admin(sozinho) eliminar e alterar tabelas, faça com que a função possua todos os objetos. A documentação:

O direito de descartar um objeto ou alterar sua definição de qualquer forma não é tratado como um privilégio concedível; é inerente ao proprietário e não pode ser concedida ou revogada. (No entanto, um efeito semelhante pode ser obtido concedendo ou revogando a associação na função que possui o objeto; veja abaixo.) O proprietário implicitamente também tem todas as opções de concessão para o objeto.

ALTER TABLE some_tbl OWNER TO schma_admin;

Ou crie todos os objetos com a funçãoschma_adminpara começar, então você não precisa definir o proprietário explicitamente. Ele também simplifica os privilégios padrão, que você só precisa definir para uma função:

Objetos pré-existentes

Os privilégios padrão se aplicam apenas a objetos recém-criados e apenas à função específica com a qual foram criados. Você também vai querer adaptar as permissões para objetos existentes :

• Permissão negada para relação <table>

O mesmo se aplica se você criar objetos com uma função que não tenha DEFAULT PRIVILEGESdefinido, como o superusuário postgres. Reatribua a propriedade schma_admine defina os privilégios manualmente - ou defina também (enquanto estiver conectado ao banco de dados correto!) DEFAULT PRIVILEGES:postgres

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

Privilégios padrão

Você estava perdendo um aspecto importante do ALTER DEFAULT PRIVILEGEScomando. Aplica-se à função atual, a menos que especificado de outra forma:

• Não é possível alterar os privilégios padrão

Os privilégios padrão se aplicam apenas ao banco de dados atual. Assim você não mexe com outros bancos de dados no cluster de banco de dados. A documentação:

para todos os objetos criados no banco de dados atual

Você também pode querer definir privilégios padrão para FUNCTIONSe TYPES(não apenas TABLESe SEQUENCES), mas esses podem não ser necessários.

Privilégios padrão paraPUBLIC

Os privilégios padrão concedidos a PUBLICsão rudimentares e superestimados por alguns. A documentação:

O PostgreSQL concede privilégios padrão em alguns tipos de objetos para PUBLIC. Nenhum privilégio é concedido PUBLICpor padrão em tabelas, colunas, esquemas ou tablespaces. Para outros tipos, os privilégios padrão concedidos PUBLICsão os seguintes: CONNECTe CREATE TEMP TABLEpara bancos de dados; EXECUTEprivilégio para funções; e USAGE privilégio para idiomas.

Minha ênfase em negrito. Normalmente, este comando (incluído na parte superior) é tudo o que você precisa:

REVOKE ALL ON DATABASE hostdb FROM public;

Em particular, nenhum privilégio padrão é concedido PUBLICpara novos esquemas. Pode ser confuso que o esquema padrão chamado "public" comece com ALLprivilégios para PUBLIC. Esse é apenas um recurso de conveniência para facilitar o início com bancos de dados recém-criados. Não afeta outros esquemas de forma alguma. Você pode revogar esses privilégios no banco de dados template1de modelo e todos os bancos de dados recém-criados neste cluster iniciarão sem eles:

\connect template1
REVOKE ALL ON SCHEMA public FROM public;

O privilégioTEMP

Como revogamos todos os privilégios hostdbde PUBLIC, usuários comuns não podem criar tabelas temporárias, a menos que permitamos explicitamente. Você pode ou não querer adicionar isso:

GRANT TEMP ON DATABASE hostdb TO schma_mgr;

search_path

Não se esqueça de definir o search_path. Se você tiver apenas um banco de dados no cluster, basta definir o padrão global em postgresql.conf. Caso contrário (mais provável) defina-o como propriedade do banco de dados, ou apenas para funções envolvidas ou mesmo a combinação de ambos. Detalhes:

• Como o search_path influencia a resolução do identificador e o “esquema atual”

Você pode querer configurá-lo schma, publicse você usar o esquema público também, ou mesmo (menos provável) $user, schma, public...

Uma alternativa seria usar o esquema padrão "público", que deve funcionar com as configurações padrão, a search_pathmenos que você altere isso. Lembre-se de revogar privilégios para PUBLICneste caso.

Relacionado

• Conceder privilégios para um banco de dados específico no PostgreSQL
• PostgreSQL - o usuário do banco de dados só deve ter permissão para chamar funções