All perguntas(dba)

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?

Há uma resposta longa e bastante elucidativa sobre as diferenças entre

• TIMESTAMP WITH TIME ZONE -vs-
• TIMESTAMP WITHOUT TIME ZONE

disponível no post SO Ignorando completamente os fusos horários em Rails e PostgreSQL .

O que eu gostaria de saber é: Existem casos de uso válidos para realmente usar TIMESTAMP WITHOUT TIME ZONEou deve ser considerado um antipadrão?

Atualmente em nosso banco de dados SQL Server 2012, estamos usando varcharo , e gostaríamos de mudar isso nvarchar. Eu gerei um script para fazer isso.

Minha pergunta é se há alguma diferença em como o SQL Server grava em varcharcolunas versus nvarcharcolunas? Temos vários procedimentos de back-end com os quais estou preocupado.

Editar:
Não tenho certeza se isso ajuda, mas as colunas não possuem índices, f/k ou restrições nelas.

Eu estou querendo saber por que um usuário recém-criado tem permissão para criar uma tabela depois de se conectar a um banco de dados. Eu tenho um banco de dados project2_core,:

postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           | 
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

Até agora tudo bem. Agora eu crio um usuário:

postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER

OK. Quando tento me conectar ao banco de dados, o usuário não tem permissão para fazer isso:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich: 
psql: FATAL:  permission denied for database "project2_core"
DETAIL:  User does not have CONNECT privilege.

Isto é o que eu esperava. Agora as coisas estranhas começam. Eu concedo ao usuário CONNECT:

postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich;
GRANT
postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           | 
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2+
               |              |           |             |             | dietrich=c/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

E sem mais concessões, o usuário tem permissão para criar uma tabela:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich: 
psql (9.2.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

project2_core=> create table adsf ();
CREATE TABLE
project2_core=> \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | adsf | table | dietrich
(1 row)

Eu esperava que o usuário não tivesse permissão para fazer nada antes que eu fizesse explicitamente GRANT USAGEno esquema e depois GRANT SELECTnas tabelas.

Onde está o meu erro? O que estou fazendo errado? Como posso conseguir o que quero (que um novo usuário não tenha permissão para fazer nada antes de conceder explicitamente a ele os direitos apropriados.

Estou perdido, e sua ajuda é muito apreciada :)

EDITAR Seguindo o conselho de @daniel-verite, agora revogo tudo imediatamente após criar o banco de dados. O usuário dietrich não tem mais permissão para criar uma tabela. Bom. MAS : Agora, também o proprietário do banco de dados, project2 , não tem permissão para criar uma tabela. Mesmo depois de emitir GRANT ALL PRIVILEGES ON DATABASE project2_core TO project2e GRANT ALL PRIVILEGES ON SCHEMA public TO project2, recebo um erro ERROR: nenhum esquema foi selecionado para criar em , e quando tento especificamente CREATE TABLE public.WHATEVER ();, recebo ERROR: permission denied for schema public . O que estou fazendo errado?

Houve uma pergunta bastante inócua sobre a adição de datas e horas no SQL Server que desencadeou um debate taxonômico bastante fascinante.

Então, como diferenciamos esses termos relacionados e como os usamos corretamente?

Fileira

Registro

Ao criar tabelas de várias junções para uso em análise, quando é preferível usar visualizações em vez de criar uma nova tabela?

Uma razão pela qual eu preferiria usar visualizações é que o esquema do banco de dados foi desenvolvido por nosso administrador de dentro do Ruby, e eu não estou familiarizado com o Ruby. Posso solicitar a criação de tabelas, mas requer uma etapa adicional e gostaria de mais flexibilidade ao desenvolver/testar novas junções.

Comecei a usar visualizações seguindo a resposta a uma pergunta relacionada no SO ( Quando usar R, quando usar SQL ). A resposta mais votada começa "faça as manipulações de dados em SQL até que os dados estejam em uma única tabela e, em seguida, faça o resto em R".

Comecei a usar visualizações, mas encontrei alguns problemas com visualizações:

1. as consultas são muito mais lentas
2. As visualizações não são despejadas da produção para o banco de dados de backup que uso para análise.

As visualizações são apropriadas para esse uso? Em caso afirmativo, devo esperar uma penalidade de desempenho? Existe uma maneira de acelerar as consultas nas visualizações?

Virei innodb_flush_log_at_trx_commit = 2e obtive uma velocidade de gravação muito rápida. Mas é seguro ser usado no site de produção?

Vindo de um histórico de MySQL, onde o desempenho do procedimento armazenado (artigo mais antigo) e a usabilidade são questionáveis, estou avaliando o PostgreSQL para um novo produto para minha empresa.

Uma das coisas que eu gostaria de fazer é mover um pouco da lógica da aplicação para stored procedures, então estou aqui pedindo DOs e DON'Ts (melhores práticas) no uso de funções no PostgreSQL (9.0), especificamente em relação às armadilhas de desempenho.

nota: esta pergunta foi atualizada para refletir que estamos usando MySQL atualmente, tendo feito isso, gostaria de ver como seria muito mais fácil se mudássemos para um banco de dados compatível com CTE.

Eu tenho uma tabela de auto-referência com uma chave primária ide uma chave estrangeira parent_id.

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| parent_id  | int(11)      | YES  |     | NULL    |                | 
| name       | varchar(255) | YES  |     | NULL    |                | 
| notes      | text         | YES  |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+

Dado um name, como posso consultar o pai de nível superior?

Dado um name, como posso consultar todos os idassociados a um registro de name = 'foo'?

context: Não sou dba, mas estou planejando pedir a um dba para implementar esse tipo de estrutura hierárquica e gostaria de testar algumas consultas. A motivação para isso é descrita por Kattge et al 2011 .

Aqui está um exemplo das relações entre os ids na tabela:

-- -----------------------------------------------------
-- Create a new database called 'testdb'
-- -----------------------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `testdb` ;

-- -----------------------------------------------------
-- Table `testdb`.`observations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Add Example Data Set
-- -----------------------------------------------------


INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10), 
   (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);

Eu tenho um processo que pega um monte de registros (1000's) e opera neles, e quando eu terminar, eu preciso marcar um grande número deles como processados. Posso indicar isso com uma grande lista de IDs. Estou tentando evitar o padrão "atualizações em um loop", então gostaria de encontrar uma maneira mais eficiente de enviar esse pacote de IDs para um proc armazenado do MS SQL Server 2008.

Proposta nº 1 - Parâmetros com valor de tabela. Eu posso definir um tipo de tabela com apenas um campo de ID e enviar uma tabela cheia de IDs para atualizar.

Proposta #2 - Parâmetro XML (varchar) com OPENXML() no corpo do proc.

Proposta nº 3 - Análise de lista. Prefiro evitar isso, se possível, pois parece pesado e propenso a erros.

Alguma preferência entre estes, ou alguma idéia que eu tenha perdido?