Erro 42P01: relação não existe

O que você tinha originalmente era uma sintaxe correta - para tabelas , não para esquemas . Como você não tinha uma tabela (apelidada de 'relação' na mensagem de erro), ela gerou o erro não encontrado.

Vejo que você já percebeu isso - acredito que não há melhor maneira de aprender do que corrigir nossos próprios erros ;)

Mas há algo mais. O que você está fazendo acima é demais por um lado e insuficiente por outro.

Executando o script, você

1. criar um esquema
2. crie um papel
3. grant SELECT em todas as tabelas no esquema criado em (1.) para este novo role_
4. e, finalmente, conceda todos os privilégios ( CREATEe USAGE) no novo esquema para a nova função

O problema está no ponto (3). Você concedeu privilégios em tabelas em replays- mas não há tabelas lá! Pode haver alguns no futuro, mas neste ponto o esquema está completamente vazio. Desta forma, o GRANTin (3.) não faz nada - desta forma você está fazendo muito.

Mas e as tabelas futuras?

Existe um comando para cobri-los: ALTER DEFAULT PRIVILEGES. Aplica-se não apenas às tabelas, mas:

Atualmente [a partir de 9.4] , apenas os privilégios para tabelas (incluindo visualizações e tabelas estrangeiras), sequências, funções e tipos (incluindo domínios) podem ser alterados.

Há também uma limitação importante:

Você pode alterar os privilégios padrão apenas para objetos que serão criados por você ou por funções das quais você é membro.

Isso significa que uma tabela criada por alice, que não é você nem uma função da qual você é membro (pode ser verificada, por exemplo, usando \duem psql), não terá os direitos de acesso prescritos. A cláusula opcional FOR ROLEé usada para especificar a função 'criador de tabela' da qual você é membro. Em muitos casos, isso implica que é uma boa ideia criar todos os objetos de banco de dados usando a mesma função - como mydatabase_owner.

Um pequeno exemplo para mostrar isso no trabalho:

CREATE ROLE test_owner; -- cannot log in
CREATE SCHEMA replays AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA replays TO test_owner;

SET ROLE TO test_owner; -- here we change the context, 
                        -- so that the next statement is issued as the owner role

ALTER DEFAULT PRIVILEGES IN SCHEMA replays GRANT SELECT ON TABLES TO alice;

CREATE TABLE replays.replayer (r_id serial PRIMARY KEY);

RESET ROLE; -- changing the context back to the original role

CREATE TABLE replays.replay_event (re_id serial PRIMARY KEY);

-- and now compare the two

\dp replays.replayer
                                   Access privileges
 Schema  │   Name   │ Type  │       Access privileges       │ Column access privileges 
─────────┼──────────┼───────┼───────────────────────────────┼──────────────────────────
 replays │ replayer │ table │ alice=r/test_owner           ↵│ 
         │          │       │ test_owner=arwdDxt/test_owner │ 

\dp replays.replay_event
                               Access privileges
 Schema  │     Name     │ Type  │ Access privileges │ Column access privileges 
─────────┼──────────────┼───────┼───────────────────┼──────────────────────────
 replays │ replay_event │ table │                   │ 

Como você pode ver, alicenão tem direitos explícitos sobre a última tabela. (Neste caso, ela ainda pode SELECTda mesa, sendo um membro do publicpseudorole, mas não quis atrapalhar o exemplo revogando os direitos de public.)

Falei com alguém que me ajudou a encontrar a resposta. A sintaxe correta é, para qualquer referência futura. A documentação menciona isso, embora possa ser fácil perder.

START TRANSACTION;
DROP SCHEMA IF EXISTS replays CASCADE;
CREATE SCHEMA replays;
CREATE ROLE admins WITH
    PASSWORD 'changeme';
GRANT SELECT ON ALL TABLES IN SCHEMA replays TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA replays TO admins;
COMMIT;