PostgreSQL - Comandos rápidos

Llegas y te piden que investigues una base PostreSQL ya! 👹... por lo que toca refrescar algunos comandos con psql

Que cuerno es?

PostgreSQL, también llamado Postgres, es un sistema de gestión de bases de datos relacional orientado a objetos y de código abierto, conocido por su robustez, características avanzadas y conformidad con los estándares SQL

 

A los bifes, COMANDOS  💻

Conexión por CLI

Para conectarnos desde línea de comandos podemos usar

psql -U postgres -p 5432 -h localhost

Donde

-U  usuario del motor (default superusuario postgres)

-p puerto (por default 5432)

-h desde donde me conecto, si estoy parado en el server es localhost

 

Si por un extraño motivo no esta dentro del path puedo ubicar el binario dentro de (linux):

/usr/lib/postgresql/

 

parado en postgres=#  puedo ejecutar

\duS	Listar todos los usuarios del motor, incluyendo los de sistemas
show config_file;	Muestra donde está el archivo de configuración (whitelist de conexión) *
\l	Listar todas las bases
\c	Seleccionar una Base
\dt	Listar todas las tablas de una Base
\q	Salir de psql
\d <table>	Detalle de los campos de una tabla
SELECT * FROM trabajadores LIMIT 5;	Hago un select de la tabla trabajadores con limite a 5 registros. Debo estar conectado a una base previamente.**

* Dentro del archivo de configuración se puede observar el "listen_addresses" que especifica desde que IPs se puede conectar al motor de BD.

** También se puede usar Order By para ordenar y Where para mostrar ciertas coincidencias.

Gestión de Usuarios

PostgreSQL crea automáticamente un usuario superusuario llamado postgres durante la instalación. El usuario postgres tiene privilegios completos para administrar la base de datos, incluyendo la creación de nuevas bases de datos, la gestión de usuarios y permisos, y la configuración de la instancia del servidor.

Los usuarios, grupos y roles son lo mismo en PostgreSQL, la única diferencia es que los usuarios tienen permiso para iniciar sesión de forma predeterminada

CREATE USER myuser WITH PASSWORD 'passwd';	Crear un Usuario CREATE USER y CREATE GROUP son en realidad alias de la instrucción CREATE ROLE. El User no tiene ningún permiso, a excepción de los permisos predeterminados disponibles para el rol public
DROP USER [IF EXISTS] user_name;	Elimina un usuario. Si pongo varios separados por comas borro varios al mismo tiempo.
GRANT readonly TO myuser;	Otorga a myuser los mismos permisos que el rol de solo lectura
REVOKE readwrite FROM user1;	Quitar el permiso de lectura y escritura (readwrite) de user1
REVOKE CREATE ON SCHEMA public FROM PUBLIC;	Revocar el permiso de creación predeterminado en el esquema public desde el rol public *
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;	Revoca la capacidad del rol público de conectarse a la base de datos Garantiza que los usuarios no puedan conectarse a la base de datos de forma predeterminada a menos que se conceda explícitamente este permiso. (no te olvides de conceder este permiso antes de aplicar a todos)
CREATE ROLE readonly;	Rol de solo lectura, sin permisos ni contraseña, no se puede utilizar para iniciar sesión
GRANT CONNECT ON DATABASE mydatabase TO readonly;	Concedemos permiso a este rol para conectarse a la base de datos de destino denominada “mydatabase”
GRANT USAGE ON SCHEMA myschema TO readonly;	Otorga acceso al uso de este rol a su esquema (myschema) Sin este paso, el rol readonly no puede realizar ninguna acción en los objetos de este esquema, incluso si se han concedido permisos para esos objetos.
GRANT SELECT ON TABLE mytable1, mytable2 TO readonly;	Otorgar acceso al rol readonly para ejecutar las consultas en las tablas requeridas
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;	Igual que anterior, pero en todas las tablas (versiones 9.0 o superiores)
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;	Para garantizar que también pueda acceder a nuevas tablas y vistas, deberemos ejecutar la siguiente instrucción para conceder permisos automáticamente:
Rol de lectura y escritura
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;	Permitir que este rol cree nuevos objetos como tablas de este esquema
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO readwrite;	Conceder acceso a las tablas
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;	conceder automáticamente permisos sobre tablas y vistas añadidas en el futuro
GRANT USAGE ON SEQUENCE myseq1, myseq2 TO readwrite;	roles de lectura y escritura, normalmente existe el requisito de utilizar secuencias también
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;	conceder permiso a todas las secuencias
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;	conceder permisos automáticamente a las secuencias añadidas en el futuro

*Cuando un usuario intenta crear una nueva tabla sin especificar el nombre del esquema, la tabla se crea en el esquema public. De forma predeterminada, todos los usuarios tienen acceso para crear objetos en el esquema public y esto se convierte en un problema si intentamos crear un usuario de solo lectura. Incluso si restringimos todos los privilegios, los permisos heredados a través del rol public permiten al usuario crear objetos en el esquema public.

Bonus Tracks, algunas ventajas respecto otros motores

• Conformidad con los Estándares SQL: PostgreSQL es altamente conforme con los estándares SQL, lo que garantiza una mayor compatibilidad y portabilidad para aplicaciones que dependen de SQL.
• Extensibilidad y Personalización: PostgreSQL permite a los usuarios definir sus propios tipos de datos, funciones, operadores y agregados, lo que ofrece una flexibilidad excepcional para personalizar la base de datos según las necesidades específicas.
• Soporte Avanzado de Transacciones: PostgreSQL tiene un fuerte soporte para transacciones ACID (Atomicidad, Consistencia, Aislamiento, Durabilidad), lo que garantiza la integridad de los datos incluso en situaciones de fallo.
• Sistema de Control de Concurrencia Multiversión (MVCC): PostgreSQL utiliza MVCC para gestionar la concurrencia de múltiples usuarios sin bloquear las operaciones de lectura, lo que mejora significativamente el rendimiento y la escalabilidad en entornos de alta concurrencia.
• Integridad de Datos: PostgreSQL ofrece características avanzadas de integridad de datos, como restricciones de claves foráneas, restricciones de unicidad, y validación de datos a través de reglas de verificación, lo que ayuda a mantener la consistencia de los datos.
• Funciones de Índice Avanzadas: Soporta una variedad de métodos de indexación, incluyendo B-tree, Hash, GiST, SP-GiST, GIN y BRIN, lo que permite optimizar consultas complejas y mejorar el rendimiento de las búsquedas.
• Soporte para Datos No Estructurados y JSON: PostgreSQL ofrece capacidades avanzadas para trabajar con datos no estructurados mediante el soporte nativo para JSON y JSONB, lo que permite almacenar y consultar datos semiestructurados de manera eficiente.
• Comunidad Activa y Amplia Documentación: tiene una comunidad activa que contribuye al desarrollo continuo y proporciona una amplia documentación y soporte comunitario.
• Funcionalidades Geoespaciales (PostGIS): PostgreSQL, con la extensión PostGIS, es altamente valorado para aplicaciones geoespaciales, ofreciendo capacidades avanzadas para el almacenamiento, consulta y manipulación de datos espaciales.

Referencias

• https://es.wikipedia.org/wiki/PostgreSQL
• https://alvaroperdiz.com/categories/bases-de-datos/
• https://www.qualoom.es/blog/administracion-usuarios-roles-postgresql/ 
• https://www.postgresql.org/docs/current/sql-grant.html
• https://www.commandprompt.com/education/how-to-deletedrop-a-user-in-postgresql/
• https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql