CAPTCHA con PHP.

Un CAPTCHA fácil y sencillo de implementar.

CAPTCHA

Introducción:

Siempre me ha llamado la atención los CAPTCHA (“Completely Automated Public Turing test to tell Computers and Humans Apart“) que hay en muchas páginas web y que permiten diferenciar consultas hechas por humanos de las hechas por “arañas” que pululan por internet (son también conocidas como “robots”). La diferencia esencial en este caso con respecto a la prueba de Turing es que se trata de convencer a una máquina que es un humano, osea el inverso de la prueba.

En el idioma inglés existe la filosofía K.I.S.S. y la practico frecuentemente ya que los sistemas complejos tienden al caos de manera natural. Es por ello que buscando por internet (precisamente ayudado por las “arañas” que pretendo combatir, ¡qué ironía!) he encontrado el siguiente artículo que me deslumbró por su simplicidad más al cabo de una hora de estudio observo los elementos implícitos sin los cuales NO funciona. Recomiendo vayan y lean dicho artículo y regresen cuando lo hayan asimilado, yo por aquí les espero. 😎


El artículo data del año 2007 pero considero que no ha perdido vigencia alguna, si administran una página web bancaria o son partidarios del último grito de la moda, recomiendo desistan de seguir leyendo esto. 😉

Pues bien manos a la obra.

Requisitos previos.

Utilizo una máquina virtual con Debian Wheezy con la cual estudio el programa para base de datos PostgreSQL a la cual le he instalado también un servidor Apache con PHP: este tipo de configuración es llamado LAMP por sus siglas en inglés, sólo que en aquí en realidad es un LAPP: Linux, Apache, PostgreSQL y PHP. Hay muchísimos tutoriales para instalar este tipo de servidores por ello partimos de la base que ustedes cuentan con uno en funcionamiento y para pruebas (no utilicen un servidor “en producción” hasta tanto no estén completamente seguros de este código). Importante recalcar que no utilizaremos funciones de bases de datos en esta entrada, pero muy frecuentemente las CAPTCHA se utilizan en este tipo de ambiente, por ejemplo, registrar usuarios en una lista de correo electrónico (tremenda tentación para los “robots spam”), así que allí encaja perfectamente.

Configuración adicional.

Al servidor LAPP anteriormente descrito debemos instalarle unas librerías para el trabajo gráfico bajo el lenguaje PHP. Aquí está muy bien descrito a lo que me refiero, haciendo la salvedad que dichas librerías NO SIEMPRE están instaladas por defecto.

En todo caso debemos crear una carpeta bajo la raíz web del servidor Apache con el nombre “captcha” y donde alojaremos el siguiente archivo “info.php”:


1
2
3
4
5
<?php
// Archivo "info.php":
// Muestra toda la información, por defecto INFO_ALL
phpinfo();
?>
y luego iremos a nuestro navegador web preferido a la dirección IP que le hayamos asignado en nuestra red de área local. En mi caso dicha dirección es 192.168.1.27 en la carpeta “captcha”:
captcha01En dicha página debemos buscar si están instalados los siguientes valores:
captcha02Dado el caso que no se encuentren instaladas dichas librerías debemos recurrir a la línea de comandos y escribir lo siguiente:
sudo apt-get install php5-gd
y verán algo más o menos parecido a esto (en mi caso no necesité el comando “sudo” porque ya había ganado acceso como root con el comando “su”):
captcha_003
Dichas librerías son necesarias para el trabajo con imágenes jpg, png y gif, entre otros formatos y funciones.

Imagen de fondo para el CAPTCHA:

Necesitaremos un imagen tipo gif con unas dimensiones de 100×30 píxeles con colores adecuados teniendo en cuenta que las letras serán negras buscaremos colores como azul o verde o el que gusten pero no de color negro. Yo elegí la siguiente imagen (aunque al final agregaremos varios elementos para dificutarles la tarea de lectura a los “robots” con técnicas OCR ): bgcaptcha.gifComo ven es una imagen con degradado que busca confundir al OCR pero con legibilidad al ser humano; no obstante usar este mismo fondo siempre y con la ayuda de la “fuerza bruta” se puede descifrar al poco tiempo -pero de eso nos encargaremos luego, de “complicarlo” para tratar de eludir la lectura-. La imagen la guardaremos en la carpeta de trabajo en el servidor web bajo el nombre “bgcaptcha.gif” (nemotécnico ‘BackGroundCaptcha.gif’=’bgcaptcha.gif’).

Para bajar archivos por líneas de comando

recomiendo usar “wget”, por ejemplo:

“wget    http://www.e.com/imagen_a_bajar.gif      nombre_imagen.gif”

Creación del archivo “captcha.php”.

Dicho archivo se encargará de tomar la imagen anterior y, aleatoriamente, “escribirle” letras encimas, almacenarlas en una variable (que leeremos con luego con el método POST) y mostrarla al navegador; para ello utilizaremos el siguiente código que explicaremos línea por línea:

<?php
  //Archivo 'captcha.php'
  session_start();
  $_SESSION['tmptxt'] = randomText(7);
  $captcha = imagecreatefromgif("bgcaptcha.gif");
  $colText = imagecolorallocate($captcha, 0, 0, 0);
  imagestring($captcha, 5, 16, 7, $_SESSION['tmptxt'], $colText);
  header("Content-type: image/gif");
  imagegif($captcha);
  imagedestroy($captcha);
?>
  • Para que el servidor sepa dónde comienza y dónde termina el lenguaje PHP debemos encerrarlo todo con los siguientes comandos: “<?php (…) ?>” y donde van los paréntesis y puntos suspensivos ubicaremos nuestro código a ejecutar. Todo lo que esté fuera de estos demarcadores será considerado lenguaje HTML e interpretado como tal.
  • Cada final de sentencia le será indicado al servidor con un punto y coma “;”.
  • Los comentarios, que son importantísimos para nosotros los seres humanos, para las máquinas carecen de importancia y se identifican con “//” y la derecha el texto explicativo.
  • La función session_start()permite crear o recuperar un identificador único que servirá para que el servidor pueda atender varios clientes al mismo tiempo sin confundir las respuestas de cada usuario. No debemos preocuparnos mucho ya que todo es automatizado, si acaso dedicaremos una sentencia “if-then-else” por si acaso devuelve el valor “falso”, es decir, no se pudo iniciar sesión (todo el mundo da por sentado que devuelve “verdadero”). Importante, muy importante, el comprender sobre el cómo PHP compara dos variables y/o valores para devolver “verdadero” o “falso”, merece su estudio de 10 minutos.
  • La función “$_SESSION[]” permite asignar un valor a una variable en la sesión iniciada en el punto anterior y es un variable de arreglo global. El texto, que será aleatorio, lo proporcionará la función “randomText()” que escribiremos luego.
  • La función “imagecreatefromgif()” nos permitirá “cargar en memoria” la imagen que destinamos como fondo del CAPTCHA y devolverá un “identificador de imagen” representado en una variable que usaremos para agregar el texto que identificará e introducirá el usuario, ser humano. De nuevo digo que deberíamos destinar un “if-then-else” dado el caso la función devuelva “falso”.
  • El comando anterior nos permitió establecer el “lienzo” donde vamos a escribir las letras aleatorias; pues el comando “imagecolorallocate()” nos permite fijar el color con que las “pintaremos”: “0, 0, 0” corresponde al color negro en la codificación de valores “RGB”, “Red Green Blue” y cuyos valores van del 0 al 255 cada uno y nos permite usar +16 millones de colores. Luego echaremos mano de esta función para confundir aún más a los “robots”, por ahora nos conformaremos con el color negro. Es de hacer notar que para esta función CERO es “falso” y cualquier otro valor es “verdadero”, si usted considera esto una tontería le invito a leer la disertación sobre el tema, está avisado o avisada.
  • La funcíon “imagestring()” dibuja una cadena de texto en nuestro “lienzo”, la imagen gif seleccionada. Los parámetros de esta función son: (image, font, x, y, text, color)  y los detallo a continuación:
    1. image: la que cargamos en memoria con la función imagecreatefromgif() y que llamamos $captcha.
    2. font: numeradas del 1 al 5 y es la fuente nativa predeterminada en la librería GD e incluso nos permite cargar nuestras propias fuentes pero debemos cargarlas y compilarlas de acuerdo a la arquitectura de nuestro servidor. Si se entusiasman a realizar esto último deberán cargar dicha fuente “compilada” con la función imageloadfont() de acuerdo a unos valores binarios. Más interesante hallo utilizar la función imagettftext() NO SIN ANTES VERIFICAR el phpinfo() devuelva que el ambiente del servidor lo soporte (si quieren saber más sobre fuentes True Type en GD visiten este enlace): captcha_004
    3. Coordenadas X e Y: tomadas a partir de la esquina superior izquierda, pónganse de cabeza para que las entiendan (ahhh me recuerdo de la materia Geometría Analítica, ¡qué belleza para Autocad! ¡Y dibujábamos por comandos escritos en papel fuera del laboratorio de computación!).
    4. text: en este caso lo que ya tenemos almacenado en la variable global de sesión “$_SESSION[‘tmptxt’]”.
    5. color: el que establecimos a negro con la función “imagecolorallocate()”.
  • La función “header()” permite que nuestro servidor se ciña a las normas del lenguaje HTML que consiste en “notificarle” al navegador web (en formato NO html) que le será enviado un flujo de datos, por defecto “application/octet-stream”, pero que nosotros utilizaremos para indicarle que es una imagen gif “Content-type: image/gif”. Si desean conocer más acerca del nacimiento de la web y sus normas de funcionamiento de la mano del mismísimo Tim Berners-Lee hagan click en este enlace.
  • Por último la función “imagegif()”  instruye a nuestro servidor que le envie la imagen al navegador del usuario, el CAPTCHA que queremos interprete el ser humano.
  • Una función que considero importante y que yo agrego al código mostrado originalmente -y del que desconozco la autoría- es “imagedestroy()” a fin de liberar la memoria utilizada por la variable “$captcha”. Aunque toda la memoria se libera cuando el usuario cierra su navegador o cuando nosotros mismos invocamos “session_destroy()” nunca está demás liberar “trabajo” apenas sea posible.

Creación de la función “randomText()”.

Ahora explicaré la función que devuelve una cadena de texto de manera aleatoria pero con dos mejoras al código fuente original, el cual es el siguiente:

function randomText($length) {
  $pattern = "23456789abcdefghijkmnpqrstuvwxyz";
  for($i=0;$i<$length;$i++) {
    $key .= $pattern{mt_rand(0,32)};
  }
  return $key;
}
  • Las funciones en PHP se declaran con la palabra clave reservada “function” y los argumentos se pasan entre paréntesis separados por comas.
  • Todo el cuerpo de la función esta demarcado por inicio y fin de corchetes “{ … }”.
  • La función toma como parámetro la variable “$lenght” la cual toma el valor de “7” al hacer el llamado de la siguiente manera: “randomText(7)”. Por ende nuestro CAPTCHA tendrá 7 caracteres, recuerden que estamos limitados al espacio del “bgcaptcha.gif”.
  • En la primera variable “$pattern” establecemos los caracteres que queremos mostrar al usuario, no repetidos, y aquí va la primera mejora que les dije antes: NO incluyo el número uno (“1”), ni la letra ele (“l”) ni tampoco el número cero (“0”) ni la letra o (“O”) porque se trata de ponersela difícil al “robot”, no al ser humano, quien puede confundir dichos signos.
  • Utilizamos un ciclo “for” similar al usado en lenguaje C, es decir, un valor de inicio, una condición que se evalúa en cada ciclo y un incremento en cada ciclo. Así establecemos que al escribir “for ( $i=0; $i<$length; $i++ ) {…}” estamos ordenando ejecutar un ciclo basado en la variable “$i” que comienza desde cero y que mientras sea menor que la longitud de caracteres de nuestro CAPTCHA se incremente en una unidad para cada ciclo, “$i++”.
  • Dentro de los corchetes del ciclo “for” anterior colocamos la variable “$key” que se autoconcatena en cada rizo “.=” con una letra aleatoria del patrón elegido y que llevamos asignado en la variable $pattern.
  • En el lenguaje PHP (e igualmente en Python) cada cadena de caracteres es considerada de manera implícita una matriz de una sola fila, esto nos permite “imprimir” en el navegador del usuario una letra del patrón, por ejemplo “echo $pattern{10}” devolverá “b”.
  • ¿Cómo escogemos una letra cualquiera del patrón? Aquí es donde viene la segunda mejora que les comenté: en vez de usar la función “rand()” utilizamos la función “mt_rand()” la cual, aparte de ser más rápida nos permite utilizar números más grandes (de ser necesario). Dicha mejora la propone un usuario llamado “caos30” en los comentarios de la entrada del blog en la cual se inspira este tema. Esta función tiene dos argumentos que nos permiten acotar el número devuelto: para este caso es (0, 32) que es precisamente el largo del patrón que escogimos (el abecedario inglés 26 caracteres más los diez dígitos numéricos son 36 menos 4 que eliminé para evitar confusiones a los humanos).
  • Es necesario el comando “return” acompañado de la variable que debe devolver la función, en nuestro caso es un tipo “cadena de texto”.

Guardado del archivo “captcha.php”.

Ya suficientemente explicado línea por línea el código unimos ambos algoritmos y con el editor de texto nano (o el que ustedes prefieran) guardamos en la carpeta “captcha” que hicimos en “\var\www” (donde generalmente Apache guarda el sitio web):

<?php
  //Archivo 'captcha.php'
  function randomText($length) {
    $pattern = "23456789abcdefghijkmnpqrstuvwxyz";
    for($i=0;$i<$length;$i++) {
      $key .= $pattern{mt_rand(0,32)};
    }
    return $key;
  }
  session_start();
  $_SESSION['tmptxt'] = randomText(7);
  $captcha = imagecreatefromgif("bgcaptcha.gif");
  $colText = imagecolorallocate($captcha, 0, 0, 0);
  imagestring($captcha, 5, 16, 7, $_SESSION['tmptxt'], $colText);
  header("Content-type: image/gif");
  imagegif($captcha);
  imagedestroy($captcha);
?>

Antes de continuar nos aseguramos que la CAPTCHA se ejecute y muestre correctamente en nuestro navegador conectado a nuestro servidor LAPP de pruebas:

captcha_005

Cada vez que pulsamos CTRL+F5 nos mostrará una CAPTCHA diferente cada vez y asigna a la variable “$_SESSION[‘tmptxt’]” el valor deberá comparar con el valor introducido por el usuario humano. El siguiente paso es hacer la interfaz para la presentación e introducción de los datos.

Formulario web “captchademo.php”.

El uso básico de sesiones bajo PHP está explicado en detalle en este enlace. No obstante buscando la simplicidad para la comprensión de todos ustedes, amables y pacientes lectores, haremos un archivo php para mostrar la CAPTCHA e introducir la respuesta del usuario y otro archivo php donde compararemos la respuesta; a este archivo lo llamaremos “captchaanswer.php” y esta explicado más adelante.

Sin más pretensiones de seguridad ni estética escribiremos el siguiente código:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>CAPTCHA con PHP</title>
<meta name="description" content="CAPTCHA con PHP: ejemplo para demostrar la creacion de Captcha con PHP." />
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
 <tr>
   <td align="center">
     <strong>CAPTCHA con PHP </strong><br>
     Ingresar el texto mostrado en la imagen: <br>
     <form action="captchaanswer.php" method="post">
       <img src="captcha.php" width="100" height="30" vspace="3"><br>
       <input name="tmptxt" type="text" size="30"><br>
       <input name="btget" type="submit" class="boton" value="Enviar">
       <input name="action" type="hidden" value="checkdata">
     </form>
   </td>
 </tr>
</table>
</body>
</html>

Al navegar nuestro servidor de prueba podrán ver algo parecido a esto:

captcha_006Todo el código anterior es puro lenguaje HTML y mencionaré brevemente línea por línea y su cierre correspondiente (nota: los términos que usaré para describir lo que sucede probablemente hiera la sensibilidad académica de algún lector, no obstante recuerdenme y escríbanme si estoy equivocado) :

  • Etiquetas “<html>…</html>”: le indica a nuestro servidor dónde comienza y dónde termina el lenguaje HTML, veremos en una entrada futura que esto es esencialmente útil en nuestros “scripts php”.
  • Etiquetas “<head>…</head>”: contiene información prioritaria que nuestro servidor tendrá que darle tratamiento especial, leer próximo punto.
  • Etiquetas “<meta (…) />”: estas etiquetas contienen datos que describen datos y están categorizados. La categoría “Content-Type” la describimos anteriormente pero en esta ocasión la usaremos para decirle al navegador del cliente que la vamos a enviar lenguaje HTML y que lo interprete como tal. Envuelve mayor complejidad una solicitud HTTP pues incluye códigos numéricos de respuesta y aceptación pero con saber que esta información es una de las primeras que se envían es más que suficiente.
  • También en los metadatos podemos incluir la codificación de caracteres utilizados en la página web (algún día cuando usemos todo en 128 bits esto caerá en desuso) por medio del comando “UTF-8”.
  • Hay ciertas palabras claves reservadas en los metadatos y es lenguaje de alto nivel, el que usamos usted y yo, seres humanos. Uno de ellos es la palabra name=”description” que nos permite especificar a manera general el propósito principal de la página.
  • En este punto es útil preguntarnos ¿para qué es todo esto del encabezado? Para ello debemos recordar que hace 20 años el ancho de banda en internet era “oro en polvo” y el protocolo se diseñó para no cargar la página completa de un solo golpe sino que echaramos “un ojo” antes de descargar completo. Hoy en día se ha vuelto obsoleto (yo escucho una radio por internet, mientras corro una máquina virtual que actualiza su software desde varios repositorios y otra máquina virtual “baja” parches de seguridad mientras escribo estas líneas ‘en línea’ y además de tanto en tanto superviso servidores de datos remotos; el tráfico de datos es asombroso) pero aún sigue siendo útil para las “arañas” o buscadores como https://duckduckgo.com/  a fin de categorizar las páginas web visitadas y de las cuales hasta guardan una copia espejo, datos, datos y más datos; discos duros a reventar (alguien dijo alguna vez “lo que se sube a internet allí se queda”, yo lo creo fehacientemente 😯 ).
  • Con las etiquetas “<title>…</title>”, valga la redundancia, titulamos nuestra página (el “caption” de la ventana) y algunos navegadores agregan su propio nombre como pueden ver el ejemplo mostrado.
  • Dentro de las etiquetas “<body>…</body>” insertaremos la página web en sí, sólo que en este caso es un formulario para mostrar e introducir los datos.
  • Con “<table><tr><td>…</td></tr></table>” dibujaremos una tabla de un solo cuadro ayudados por el concepto de anidación de etiquetas ¿recuerdan el método de la “doble C” para dividir fracciones? Pues bueno es algo parecido a eso.
  • Con “<strong>…</strong>” podremos poner en negritas lo escrito entre las etiquetas.
  • Con “<br>” le decimos al navegador que termina una línea y comience una nueva.
  • He aquí lo más importante: “<form>…</form>”. Allí declaramos y establecemos los elementos del formulario en sí.
  • Con form <form action=”captchaanswer.php” method=”POST”> indicamos que las variables siguientes sean pasadas a otro “script php” con el método “POST” para que el usuario no visualize las variables(a diferencia del método “GET”).
  • He aquí la “magia”: al pasar la orden de visualizar la imagen CAPTCHA realmente le indicamos es que ejecute el “script”: <img src=”captcha.php” width=”100″ height=”30″ vspace=”3″> y que lo muestre como tal (con ayuda de “header()”).
  • Los demás elementos son para dibujar el cuadro de texto y el botón enviar, creo no merecen mayor explicación.
  • Las normas estéticas las agregaremos en una próxima entrada pero de manera normalizada, como el tema es largo y quiero escribirlo desde hace tiempo le dedicaré una entrada aparte.

Actualizado el lunes 22 de febrero de 2016.

Para prevenir que los servidores proxy instalados en redes de área local NO guarden en caché la imagen de nuestro CAPTCHA podemos hacer uso de generar diferentes nombres al “archivo” de imagen misma con, por ejemplo, la función uniquid de la siguiente manera:

<?php
  echo "<img src='";
  echo uniqid("captcha", true);
  echo ".php' width='100' height='30' vspace='3'><br>";
?>

Formulario web “captchaanswer.php”

Como ustedes pueden ver loq ue yo llamo “formularios web” o “páginas web” en este servidor LAPP son en realidad unos “scripts” en lenguaje PHP. El que nos ocupa ahora es el que se invoca cuando el usuario pulsa el botón “Enviar”:

<?php
    session_start();
    if ($_POST['action'] == "checkdata") {
        if ($_SESSION['tmptxt'] == $_POST['tmptxt']) {
            echo "Bienvenido.";
        } else {
            echo "Intentalo nuevamente.";
        }
        exit;
    }
?>

De nuevo lo analizamos línea por línea obviando, por supuesto, las funciones y comandos que de nuevo encontremos y hayamos explicado antes:

  • Es necesario llamar a la función “session_start()”, recordemos dicha función también nos permite recuperar los datos de la sesión almacenados en la variable global.
  • La próxima acción es verificar que la variable ‘action’ esté establecida a ‘checkdata’  de no ser así pues simplemente finaliza y sale sin aviso alguno.
  • El punto decisivo es la comparación de la cadena aleatoria creada por el script “captcha.php” y compararla con la enviada por el usuario. De ser iguales emite el mensaje “BIENVENIDO” y aquí es donde podemos ubicar el código que nos interesa: la publicación de un comentario a una entrada de blog, la confirmación a una lista de correos, etc.

Conclusiones.

Esto es sólo un abreboca de lo que podemos realizar con PHP en cuanto a gráficos se refiere, estoy consciente que para las normas actuales es un CAPTCHA bastante débil pero lo podemos mejorar hasta llevarlo a un nivel básico. Para mi caso particular lo voy a implementar de una vez en un “servidor de producción” y que trabaje de una vez.

<Eso es todo, por ahora>.

PostgreSQL 9.1: su uso con psql.

Y seguimos aprovechando el receso de Carnaval para continuar nuestro estudio del poderoso motor de base de datos relacional PostgreSQL 9.1.

Nuestra miniserie comienza acá y en la última entrada tocamos el tema de las consultas con uniones las cuales utilizaremos aquí también pero con ayuda del programa psql y tal como lo prometimos vamos a trabajar en profundidad por línea de comandos.

Como recordarán ya habíamos utilizado anteriormente a psql para garantizarle al usuario adminsql acceso íntegro a todas las bases de datos en el servidor PostgreSQL que instalamos.

Breve pausa para hablar sobre seguridad básica.

Normalmente lo que hicimos en el párrafo anterior sólamente se hace si hay un solo usuario y una sola base de datos, un sistema pequeño, tal como era a finales del siglo pasado. Pero ahora con computadoras con procesadores multinúcleo (e incluso hasta computadoras con multiprocesador ) un servidor PostgreSQL puede, tranquilamente, atender muchos usuarios a la vez. Imaginemos que en nuestra Academia de Software Libre de ejemplo cada alumno debe acceder a este recurso único pero debemos de cuidar que cada quien trabaje en su propia base de datos y no toque las ajenas, ya sea por error o a propósito (que se ven casos así). Pues bien PostgreSQL soporta usuarios y grupos internamente, tal como si fuera una computadora con un sistema operativo moderno.

Un usuario pudiera ser cada alumno en clase con su base de datos propia para desarrollar su trabajo y aprobar el curso.

Un grupo, por ejemplo, pudiera ser un sección o curso en determinada aula de clase con derechos de sólo lectura sobre una base de datos perteneciente al instructor de clases y además independiente de otros instructores en otras seccionesde clase; eso nos facilita el trabajo al crear usuarios pues se le asigna al grupo y “hereda” la configuración.

Trabajando con psql.

Lo comentado en la sección anterior lo veremos ahora de forma práctica y de la manera más sencilla posible. En la máquina Debian que corre nuestro PostgresSQL seleccionamos “Aplicaciones->Accesorios->Terminal”:

postgresql_blog01Abrimos así una ventana terminal donde procederemos a ingresar el comando psql pero en esta oportunidad lo haremos acompañado de opciones adicionales:

psql --username=adminsql --password --dbname=GNU_academy

Dichas opciones son:

  • “–username=nombre_de_usuario” permite colocar a continuación el nombre del usuario que se va a conectar; la forma abreviada es “-U nombre_de_usuario” con un espacio entre ambos (y tened en cuenta las mayúsculas y minúsculas, tal cual se escriben).
  • “–password” para que nos pregunte la contraseña (“–no-password” será útil en trabajos de procesos por lotes); la forma abreviada es “-W” (notad que las formas largas son con doble guión y las cortas con un solo guión).
  • “–dbname=nombre_de_la_base_de_datos” nos conecta de una vez a donde vamos a trabajar; la forma abreviada es “-d nombre_de_la_base_de_datos” con un espacio entre ambos.

Por ahora ésas son las opciones que utilizaremos por la terminal interactiva pero en realidad psql tiene gran cantidad de agregados los cuales en otras entradas le daremos uso, pero no a todos. Si queréis echar un ojo a la lista completa, pincha aquí.

Una vez hayamos presionado intro y haber tecleado nuestra consabida contraseña al cabo de otro intro nos aparece el siguiente mensaje de error:

psql: FATAL: Peer authentication failed for user “adminsql”

Y no, no es porque hayamos ingresado mal la contraseña, que es harta sencilla sino por el asunto de seguridad que describí hace poco. Para que podamos conectarnos debemos crear un usuario llamado adminsql con su correspondiente contraseña en el sistema operativo Debian (o GNU/Linux que usemos). Esto no sería deseable, ya que los usuarios tendrían acceso al sistema operativo en sí, amén que también si son muchos usuarios recarga inncesariamente el sistema de archivos (que aunque tengamos poderosas computadoras siempre la frugalidad es bienvenida), y no, no necesitamos ése esquema de trabajo.

Es por ello que contamos con la opción peer por defecto, apoyada por el sistema operativo y contamos con la opción md5 que encripta la contraseña para mayor seguridad, así que sólo registraremos usuarios dentro de PostgreSQL. Para ello ganaremos acceso como superusuario y luego usaremos el programa editor de texto nano para editar la configuración. Por favor observen la imagen para que tengan el panorama completo:

postgresql_blog02

nano /etc/postgresql/9.1/main/pg_hba.conf

y buscaremos la sección “is for Unix domain sockets connections only” donde cambiaremos “peer” por “md5” tal como aparece en la siguiente figura:

postgresql_blog03lo siguiente será (recuerden, estamos usando nano) CTRL+X -> “y” -> intro osea guardar y salir en el editor de textos que hayan utilizado ustedes. Una vez hecho esto, necesitamos que el servidor tome la nueva configuración, en realidad el servicio o demonio debemos reiniciarlo, no la máquina en si:

service postgresql restart

y si hicimos bien nuestro trabajo veremos más o menos lo siguiente:

postgresql_blog04De nuevo introducimos el comando descrito para conectarnos a la base de datos pero esta vez si que tenemos éxito en nuestra tarea:

postgresql_blog05Y allí estamos, a la espera de la primera consulta, usaremos entonces la última de la entrada anterior:

SELECT students.name, phone_numbers.number
FROM students
INNER JOIN phone_numbers
ON students.id=phone_numbers.idstudent
WHERE LOWER(students.email) ~ ‘hotmail.com’;

¿Recuerdan la utilidad del punto y coma para indicar el final de la consulta? Acá nos viene como anillo al dedo pues estas terminales son un tanto incómodas al estar generalmente limitadas a 80 columnas, así que presionamos intro y notad que el prompt cambia de “=#” a “-=” para indicarnos que estamos en una sola sentencia a pesar que son varias lineas:

postgresql_blog06¡Y listo! hemos hecho nuestra primera consulta por terminal interactiva, ya sólo queda salir de psql y luego de nuestra terminal para ello bastará con teclear:

/quit

exit

En la próxima entrada seguiremos trabajando por línea de comandos para la creación de tablas e insertado de datos en las mismas y ampliaremos el estudio de consultas por uniones JOIN.

<Eso es todo, por ahora>.

PostgreSQL 9.1: su uso con phpPgAdmin.

En la entrada anterior  dejamos listo el servidor de base de datos PostgreSQL 9.1 y ahora vamos a utilizar software de administración basado en web phpPgAdmin para lo cual echamos mano de una cónsola con derechos (aquí utilizamos root de una buena vez) y luego usamos apt (también pueden utilizar  aptitude-install si gustan y lo tienen configurado):

apt-get install phppgadmin

Recordad que aunque nos referimos como “phpPgAdmin” para el apt debemos escribirlo todo en letras minúsculas para que pueda haber coincidencia con los repositorios, ojito con eso que en GNU/Linux es motivo de frustración en muchas personas (eso y el NUMLOCK encendido). La buena noticia es que, si no lo tenemos instalado, la orden de manera tácita instala el servidor web Apache y el lenguaje de programación PHP a nuestra máquina (los módulos necesarios sólamente, ni más ni menos):

Postgresql_2015-02-02 19:20:34

Una vez que pulsamos la letra “Y” y presionamos “Enter” (“S” e “Intro” para los que hablamos castellano):

Postgresql_2015-02-02 19:21:59 Postgresql_2015-02-02 19:22:08

Al finalizar la instalación podemos configurar el phpPgAdmin para que admita conexiones desde cualquier computadora, por seguridad viene sólo para sea administrado directa y únicamente por cónsola de la máquina, así que:

cd /etc/apache2.d

nano phppgadmin

Postgresql_2015-02-02 19:22:50

Así descomentamos (eliminamos “# ” del inicio de línea) justo la que está debajo de “allow from 127.0.0. (..)”, veréis algo así como esto:

Postgresql_2015-02-02 19:29:38

Guardamos y salimos para luego reiniciar el servidor Apache y que acepte así la nueva configuración que necesitamos:

/etc/init.d/apache2 restart

Postgresql_2015-02-02 19:31:04

Ahora desde cualquier otra máquina en la misma red de área local (recordáis que les dije que anotaran ése valor) podemos proceder a verificar primero si el servidor web funciona:

Postgresql_2015-02-02 19:31:47Esta última captura de pantalla, tal y como se los describí en la entrada origen a este trabajo, es la máquina real que contiene a la máquina virtual en la cual está alojada la máquina virtual cuyo nuestro enrutador de red inalámbrico le asignó la dirección 192.168.1.27 y utilizamos Iceweasel que es el navegador web predeterminado en Debian. Luego procedemos a verificar si el phpPgAdmin está corriendo al introducir en la barra de direcciones el siguiente comando:

http://192.168.1.27/phppgadmin

Postgresql_2015-02-02 19:32:03Hacemos click en el panel izquierdo en donde dice “Servers->PostgreSQL” y nos aparece la pantalla para iniciar sesión:

Postgresql_2015-02-02 19:32:19Para nuestro caso el usuario que le colocamos: “adminsql” y contraseña “12345”, entramos y podemos observar que la única base de datos es postgres la cual es utilizada para almacenar valores del PostgreSQL y permitir su funcionamiento, la dejamos tranquilita, si queréis podeís ver sus valor sin modificar nada (por ahora):

Postgresql_2015-02-02 19:32:40Fijad la mirada en el enlace “Create database” allí mismo hacemos click para comenzar a trabajar (¡por fin!) en el proyecto en sí:

Postgresql_2015-02-02 19:37:56Le colocamos el nombre “GNU_academy” en el “encoding” seleccionamos “UTF8” para que acepte nuestros caracteres castellanos (“ñÑáÁéÉíÍóÓúUüÜ”) y no se preocupen por “Collation” que automáticamente toma la configuración regional del sistema.

Hasta aquí finaliza el tema de phpPgAdmin en la próxima entrada comenzaremos con la poderosa herramienta Visual Paradigm.

<Eso es todo, por ahora>.

Postgresql logo

PostgreSQL 9.1: primeros pasos.

Por motivos de trabajo y estudio hemos descuidado un poco nuestro blog pero antes que termine el mes de enero de 2015 traemos a caso de análisis el potente motor  para bases de datos PostgreSQL y aunque al momento de escribir esto ya van por la versión 9.4 (diciembre de 2014) utilizaremos para nuestro estudio la versión 9.1 (ojito con estos numeritos de la versión que lo usaremos bastante).

En esta entrada haremos un trabajo eminentemente práctico, las teorías y detalles serán tradadas en entradas posterios, dado el tamaño del tema en cuestión. Mi reconocimiento especial a @phenobarbital por su blog que sirve como preciada guía para este nuestro proceso de aprendizaje.

A manera de resumen enumeramos lo siguiente:

  1. Con VirtualBox haremos una máquina virtual Debian 32 bits para hacer allí las pruebas necesarias sin comprometer nuestra máquina real (uso y creación de máquinas virtuales merece una entrada completa aparte a futuro).
  2. Una vez instalada y configurada con sus repositorios instalaremos PostgreSQL y la configuraremos para aceptar conexiones de usuarios de la red de área local (no obstante los comandos los introduciremos directamente por cónsola, las sesiones ssh y Tmux merecen entrada aparte en el blog -a futuro-).
  3. Una vez tengamos el terreno abonado usaremos Visual Paradigm para linux 32 bits en modo demostrativo ya que con esta herramienta definiremos las tablas de una base de datos sencilla, manejo de inscripción de personas en cursos, haciendo una abstracción generalizada de dicho proceso.
  4. Instalaremos phpPgAdmin como herramienta para administrar la base de datos vía web, verificar las tablas e incluso agregar y/o modificar datos.
  5. Con datos agregados (y esperamos agregar unos cuantos millones de usuarios) realizaremos algunas sentencias SQL, uniones y consultas.

 

Pues bien, manos a la obra.


Instalación de PostgreSQL.

Con la máquina virtual corriendo  (512 megabytes RAM, 1 CPU y 1 NIC 100 mbps asignados virtualmente) abrimos una terminal y nos registramos como super usuario, recuerden que debemos tener configurados ya nuestros repositorios y una buena conexión a internet. Para nuestro caso es específico tenemos a la máquina virtual alojada en una red de área local cuyo enrutador asigna direcciones IP internas con DHCP basado en la dirección MAC de la tarjeta de red virtual que está en puente “bridge” con la máquina real así que al arrancar automáticamente ya la tenemos en la dirección 192.168.1.27 y con  5 mbps de velocidad al internet asignada a ella solita para no molestar a los demás usuarios, si no hacemos esto monopolizaremos al modem y no es la idea (de nuevo, todo esto merece una entrada aparte en nuestro blog, a futuro lo haremos).

Ejecutamos en la cónsola:

apt-get update (intro).

apt-get install postgresql-9.1

Recordando siempre que estamos como super usuario, aquí una captura de pantalla de los procesos que más o menos vereís sobre el proceso de instalación.

Postgresql_2015-01-30 07_36_26Postgresql_2015-01-30 07_36_35Postgresql_2015-01-30 07_37_28

Configuración de PostgreSQL.

Una vez finalizada la instalación es que comienza en realidad nuestro trabajo, verificamos si la instalación agregó un usuario llamado “postgres” con el comando:

cat /etc/shadow | egrep “postgres”

a lo cual devuelve algo parecido a esto (si está está agregado el usuario):

postgres:*:16465:0:99999:7:::

Postgresql_2015-01-30 08_30_29

Si es positivo procedemos a conectarnos como usuario “postgres”:

su postgres

y el indicador se convierte en “postgres@postgresql:/home/jimmy$” y esto quiere decir que estamos conectados como usuario “postgres” en la máquina “postgresql” y ubicados en la carpeta “home” del usuario “jimmy”. Si queremos ver cual es nuestra carpeta “home”, osea el “home” del usuario “postgre” escribimos:

echo $HOME

lo cual devuelve “/var/lib/postgresql”.

Postgresql_2015-01-30 08_42_26

La idea es crear un nuevo usuario utilizando la sencilla nomenclatura para nombres y contraseñas (que para propósitos didácticos es excelente PERO para la vida real NO cuidadito con dejar un servidor PostgreSQL configurado así 😯 ).

createuser -sPl adminsql

y nos pregunta contraseña a lo cual introducimos “12345” y confirmamos nuestra elección.

Postgresql_2015-01-30 09_00_52

Ahora si es que vamos a entrar al propio PostgreSQL, escribimos:

psql

lo cual nos devuelve algo parecido a esto (imagen):

Postgresql_2015-01-30 09_04_17

y acto seguido garantizamos que el usuario que acabamos de agregar tenga acceso libre a las bases de datos:

grant all on database postgres to adminsql;

y si nos acepta el comando nos devuelve “GRANT” y listo, salimos de la cónsola con

\quit

Postgresql_2015-01-30 09_08_48

Acceso remoto a PostgreSQL.

No obstante que estamos trabajando en cónsola directamente a la máquina virtual lo más probable es que tengamos que acceder a ella remotamente así que agregamos el usuario que acabamos de agregar al archivo siguiente:

nano /etc/postgresql/9.1/main/pg_hba.conf

y quiere decir que utilizamos el editor de texto “nano” para editar el archivo pg_hba.conf (o utilicen su editor de texto favorito, muy populares son “vi” y pueden instalar “vim” o “gedit”, el que gusten). Editamos el archivo donde especifican las direcciones IPv4:

#IPv4 local conections:

host   all   127.0.0.1/32   md5

host    all   192.168.1.0/24   md5

Teniendo cuidado de insertar sólo espacios en blanco entre las palabras (que si usaís la tecla TAB configurar para que la misma inserte espacios y no el caracter mismo tabulador) y recordemos hacer esto cada vez que agreguemos un usuario a la base de datos y así permitirle conectarse de manera remota en una red de área local con su respectiva submáscara de red (en notación CIDR:  “192.168.1.0/24”), me disculpan el error al colocar 255:

Postgresql_2015-01-30 09_18_58

Guardamos y salimos a la linea de comando para ejecutar:

nano /etc/postgresql/9.1/main/postgresql.conf

y modificamos y agregamos los siguientes datos (todo lo que esté escrito a la derecha del símbolo “#” son comentarios que no toma en cuenta el servidor PostgreSQL pero que para nosotros los humanos son importantes):


# – Connection settings –

listen_addresses = ‘*’     #valor por defecto ‘localhost’

max_connections = 50     # valor por defecto 100

# – Memory –

shared_buffers = 16MB   #valor por defecto 24 se requiere “fórmula” para hallar el mejor valor para nuestro servidor según nuestro hardware -nunca más del 40% de la memoria instalada o virtualizada-.

temp_buffers = 8MB   #8 por defecto la subimos a 16 pero eso depende de las consultes que pensemos ejecutar, hay que tantear este valor.

work-men = 16MB    #para las INSERT, DELETE para cada usuario por cada segundo

# – Background Writer –

bgwriter_delay = 500ms #cada medio segundo escribe al disco duro y así evitamos sobrecargar al hardware.

En las siguientes imágenes sólo falta el valor de acceso al disco duro descrito poco antes, observen que coloco una estrella para resaltar los valores que necesitan reiniciar al servidor PostgreSQL (dado el caso que tengamos usuarios conectados utilizamos reload en vez de restart):

Postgresql_2015-01-30 09_43_22


 

Observen escribir cuidadosamente cada uno de los valores antes de guardar (si yo tengo algún error o sugerencia COMENTAR esta entrada) así que si todo está correcto guardamos y salimos a la cónsola de entrada y una vez hecho esto procedemos a reiniciar el servidor de base de datos con la orden:

/etc/init.d/postgresql restart

y si hemos colocado bien las modificaciones devolverá lo siguiente (imagen):

Postgresql_2015-01-30 09_52_09

De no ser así volvemos sobre nuestros pasos hasta que se reinicie el servidor postgresql y avanzar hacia la siguiente etapa de configuración.

En este punto debemos advertir que tocaremos variables de configuración del sistem Debian por lo tanto debemos ser cuidadosos con lo que escribimos, vericar 3 veces lo ingresado; ya que estamos como usuario “postgres” debemos teclear “exit” y presionar la tecla intro y ganar acceso como “root” para así poder ejecutar en cónsola:

nano /etc/sysctl.conf

y agregamos al final los siguientes valores (que por ahora no sabemos qué significan pero en una futura entrada le dedicaremos su correspondiente espacio bien explicado):


kernel.sem = 100 32000 100 128
kernel.shmall = 3279547
kernel.shmmax = 289128448
kernel.shmmni = 8192
fs.file-max = 287573
vm.dirty_bytes = 67108864
vm.dirty_background_bytes = 134217728



 

Revisamos bien los valores guardamos y salimos para ejecutar:

sysctl -p

a lo cual nos devuelve por pantalla precisamente los guarismos que introdujimos:

Postgresql_2015-01-30 10_16_19

Acto seguido comprobamos que el servidor PostgreSQL acepte la nueva configuración pormedio de la orden que ya sabemos:

/etc/init.d/postgresql restart

y si todo va bien veremos lo siguiente:

Postgresql_2015-01-30 10_17_41

Ahora  vamos a instalar el Visual Paradigm 12  phpPgAdmin  desde el enlace que colocamos al inicio de esta entrada y para no resultar tediosa y larga esta entrada continuaremos en otra en el siguente enlace.

<Eso es todo, por ahora>.

Enlaces relacionados.

En castellano:

En inglés: