Chuleta de PSQL

Aunque te hayas pegado a menudo con una base de datos postgresql, normalmente, se tocan de uvas a peras. Es difícil acordarse de todo, y por ello, hoy os dejo mi chuleta personal cuando no me acuerdo de algo.

Ficheros de configuración

/var/lib/postgresql/11/main'		# use data in another directory
/etc/postgresql/11/main/postgresql.conf' #default conf file
/etc/postgresql/11/main/pg_hba.conf'	# host-based authentication file

Conectarse desde cliente psql

Para hacer login en la BD postgres, utilizaremos la siguiente sintaxis:

psql -h [HOST] -U [user] [BD]

Ejemplo:

psql -h localhost -U user_name db_name

Visualizar ROLES

\du

Resultado:

                                Lista de roles
  Nombre de rol  |                   Atributos                    | Miembro de
-----------------+------------------------------------------------+------------
 postgres        | Superusuario, Crear rol, Crear BD, Replicación | {}
 xxxxxxxxx       | Superusuario                                   | {}
 yyyyyyyyyyy     |                                                | {}

Crear un GRUPO

CREATE ROLE nombre [ [ WITH ] opción [ ... ] ]

Donde opción puede ser:

  SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT límite_conexiones
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'contraseña'
| VALID UNTIL 'fecha_hora'
| IN ROLE nombre_de_rol [, ...]
| IN GROUP nombre_de_rol [, ...]
| ROLE nombre_de_rol [, ...]
| ADMIN nombre_de_rol [, ...]
| USER nombre_de_rol [, ...]
| SYSID uid

Ejemplo:

CREATE GROUP dev WITH LOGIN NOCREATEDB NOSUPERUSER NOCREATEROLE;

Dar permisos de conexión sobre una BD

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Para más info de GRANT sobre postgres, visitar:
Postgres GRANT

Ejemplo:

GRANT CONNECT ON DATABASE tellmegen TO dev;

Mostrar todos los SCHEMA disponibles

select schema_name from information_schema.schemata;

Resultado:

    schema_name
--------------------
 isisaudit
 isiscommand
 isissecurity
 isissessionlogger
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 information_schema
 public

Dar permisos de SELECT, INSERT… sobre SCHEMA

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Ejemplo:1

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dev;

Crear usuario y agregarlo a grupo

Utilizaremos la misma sintaxis que para crear un grupo. Ejemplo:

CREATE ROLE user_name WITH LOGIN NOCREATEDB NOSUPERUSER NOCREATEROLE CONNECTION LIMIT 1 PASSWORD 'password' IN GROUP dev;

Es interesante establecer CONNECTION LIMIT 1, para evitar suplantación de identidad del usuario

Cambiar de grupo al usuario

Eliminar usuario del grupo:

REVOKE user_name FROM group_name;

Modificar el fichero de conexión pg_hba.conf

Para permitir el acceso del usuario, hay que modificar el fichero pg_hba.conf. Esta configuración no funciona para usuarios, así que para permitir el acceso, habrá que añadir a cada usuario de manera individual.

/var/lib/pgsql/9.3/data/pg_hba.conf

o o para postgres11:

/etc/postgresql/11/main/pg_hba.conf

La sintaxis del fichero debe de llevar alguna de las siguientes formas:

# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

Ejemplo para login local:

local database_name user_name peer

Ejemplo para login remoto / pgadmin:

host database_name user_name ip/32 trust

Reiniciar siempre el servicio de postgres para aplicar cambios:

service postgresql-9.3.service restart

---

Problema resuelto!