machineghost's questions

Estou tentando configurar o PostgreSQL em um novo computador (Linux Mint). Tudo funciona perfeitamente até eu tentar acessar o banco de dados. Quando o faço, como meu próprio usuário, recebo o seguinte erro (esperado):

$ psql 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "me" does not exist

Isso faz sentido, porque no Linux há um postgresusuário separado com acesso ao banco de dados. Tentei usá-lo:

$ sudo su postgres
[sudo] password for me:
$ psql
Password for user postgres:  psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

Nesse ponto, fiquei confuso: o postgresusuário deveria ter acesso sem senha a todos os bancos de dados PostgreSQL. Além disso, tentei configurar meu pg_hba.confarquivo para permitir acesso local a todos os usuários:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Entretanto, mesmo depois de reiniciar o PostgreSQL (com /etc/init.d/postgresql reload), ainda fui solicitado a fornecer uma senha de banco de dados para o usuário postgres.

Alguém pode me ajudar a entender:

1. Por que estou sendo solicitado a digitar uma senha quando tento acessar meu banco de dados como postgresusuário (em uma nova instalação)?
2. Por que ainda recebo a solicitação mesmo depois de definir conexões locais IPv4/IPv6 como trust?
3. Como posso corrigir meu postgresusuário para que fique igual a outras máquinas Linux (ou seja, não tenha nenhuma senha de banco de dados, mas ainda consiga acessar todos os bancos de dados)?

O PostgreSQL tem uma OR REPLACEvariante " " (como em " CREATE OR REPLACE ____") para tabelas, funções, visualizações, regras, etc. No entanto, ele não tem uma para tipos.

Eu poderia ir registrar um bug, mas imagino que essa foi uma decisão deliberada dos mantenedores. Minha pergunta é: por quê?

Em outras palavras, o que há de diferente nos tipos que os faz não precisar de um OR REPLACE(mesmo que tabelas, funções, etc. precisem)?

PS Acabei de descobrir que ele tem um DROP TYPE IF EXISTS... o que só me confunde mais. Realmente parece que CREATE TYPEfoi escolhido para não ter um IF EXISTS, e eu simplesmente não vejo o porquê.

No PostgreSQL, a RETURNSpalavra-chave exige que você especifique o tipo exato que a função retorna. Isso pode ser um incômodo quando esse tipo é o resultado de uma consulta com vários JOINs, pois você acaba tendo que criar manualmente um TABLEtipo com cada coluna de cada tabela JOIN-ed. Se você se juntou employeescom profilesand jobs, agora você tem que listar todas as três colunas da tabela:

RETURNS TABLE(
  -- employees field #1
  -- employees field #2
  -- employees field etc.
  -- ... profiles fields
  -- ... jobs fields
)

Existe algum tipo de atalho para evitar isso e apenas dizer ao PostgreSQL "retorne o que a consulta retornar; não se preocupe com o tipo"? Eu descobri que o PostgreSQL tem um anytipo ... mas ele não parece deixar você usá-lo em uma RETURNScláusula.

Caso contrário, existe alguma maneira de criar tipos combinando outros tipos (por exemplo, o JOIN_TABLE(employees, profiles, jobs)tipo) ou criar tipos a partir de tabelas existentes (por exemplo, TABLE(...employees, integer job_id))?

Estou tentando escrever uma função recursiva do PostgreSQL que basicamente diz:

1. Pegue um ID de grupo
2. Se houver um item naquele grupo, devolva-o
3. Caso contrário, encontre os subgrupos do grupo
4. Repita a função em cada um dos subgrupos

A função recebe um ID de grupo e o título de um item a ser encontrado, e o que tenho até agora para o corpo é:

RETURN QUERY SELECT -- ... return children with title, if any
-- If there were none, look for grandchildren (and so on)
IF NOT FOUND THEN
   -- Loop through the sub-group IDs
   FOR sub_group_id IN
     SELECT -- ... sub-groups of the provided group
   LOOP
     -- Recursion time ... stuck here

O que eu quero expressar é:

1. Correr SELECT * FROM this_function(sub_group_id, title);
2. Se retornar > 0 resultados, retorne esses resultados
3. Se isso não acontecer, continue em loop

Mas se eu apenas disser:

LOOP
  RETURN QUERY SELECT * FROM this_function(sub_group_id, title);

não tem o efeito desejado. Alguém pode me ajudar a expressar o acima no PostgreSQL?

Versão curta:

Eu recebo:

erro: falha na conexão com o servidor no soquete "/var/run/postgresql/.s.PGSQL.5432": FATAL: Falha na autenticação de peer para o usuário "admin"

quando tento createdb --username admin --password mynewdb... mas criei o usuário "admin" no Linux e no PostgreSQL, então não entendo porque não posso usá-los.

Versão longa:

Estou tentando criar um usuário "admin" para executar o SQL em uma nova máquina Ubuntu/AWS. Eu criei o usuário com

$ sudo useradd -g admin admin

Então eu defino a senha deles usando:

$ sudo passwd admin

Eu sei que a senha foi definida corretamente, porque posso alternar para o usuário "admin" digitando essa senha, com:

$ su - admin

Também criei uma função de banco de dados para o usuário, com:

CREATE ROLE admin WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'samepassword';

e posso ver que o usuário foi criado com \du(de dentro psql):

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

Além disso, posso confirmar, a partir do meu pg_hba.confarquivo, que o Postgres deve usar a senha "peer" (ou seja, sistema) para esse usuário:

local   all             all                                     peer

E reiniciei o Postgres, só por segurança:

sudo service postgresql restart

No entanto, apesar de todos os itens acima, quando tento criar um novo banco de dados com esse usuário "admin", usando createdb, recebo minha senha duas vezes e falha:

$ createdb --username admin --password mynewdb
Password: 
Password: 
createdb: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  Peer authentication failed for user "admin"

Eu tentei ler outras respostas SO para este erro, mas todas elas sugerem coisas que eu tentei (ou seja, alterar pg_hba.conf, reiniciar o Postgres, etc.) Eu adoraria ouvi-los.

Se eu tiver uma função no PostgreSQL, posso validar seus argumentos (por exemplo, um usernameargumento) assim:

IF LENGTH(username) < 4 THEN
    RAISE EXCEPTION 'Usernames must contain at least four characters';
END IF;

O que não consigo descobrir é como fazer a mesma coisa com os resultados de uma consulta SELECT, ou seja. algo como:

IF LENGTH(SELECT * FROM USERS WHERE username=username) > 1 THEN
    RAISE EXCEPTION 'Username already exists';
END IF;

(E sim, eu sei que o PostgreSQL me dará um erro sem essa linha, mas quero ter uma mensagem de erro mais amigável.)

É possível algo assim no (Postgre)SQL e, se sim, alguém pode me indicar a sintaxe (ou um exemplo dela) que eu possa ver?