CREACIÓN, INGRESAR Y ELIMINAR UNA BASE DE DATOS EN POSTGRESQL.
Crear bases de datos.
Existen 2 formas para hacerlo:
Una es escribiendo el código y la otra seria tomando desde herramientas, tablas y demás de forma gráfica, según como se desee.
Una es escribiendo el código y la otra seria tomando desde herramientas, tablas y demás de forma gráfica, según como se desee.
Manera de consola
Sintaxis:
Create database nombre_base;
Manera Gráfica
Nos posicionamos del lado izquierdo/opción DATABASE donde daremos clic derecho seleccionando CREAR.
Figura 70. Crear una bases de dato con manera grafica.
Acceso a la base de datos
Para acceder a una base de datos de postgreSQL se puede de dos formas.
Para este ejemplo deberemos abrir pgAdmin e ingresar nuestra contraseña. Despliega las carpetas “servers", “PostgreSQL", y en “Databases", observamos las bases de datos creadas. Seleccionamos la carpeta a usar, damos clic en query tool, y listo, estamos dentro de la base de datos y podemos comenzar a crear las querys.
Figura 71. Ingresar a una base de datos.
Manera SQL
Deberemos tener SQL shell abierto, (este es instalado al mismo tiempo que PostgreSQL).
Damos enter y de esta forma irán apareciendo: el nombre del servidor, database, el número de puerto, y nombre de usuario, enseguida nos pedirá ingresar nuestra contraseña y lo hacemos.
Ahora si, para acceder a la base de datos previamente ya creada, pondremos el siguiente comando “\c" y el nombre de la base de datos, mandando así el mensaje que indique que ya estamos conectados a la base de datos y listo.
Figura 72. Usar una base de datos en consola.
Eliminación de base de datos
Manera SQL
De igual manera se puede hacer de 2 maneras:
Se selecciona la base de datos/ clic derecho y le damos en la opción CREATE SCRIPT.Y escribimos la sintaxis:
Se selecciona la base de datos/ clic derecho y le damos en la opción CREATE SCRIPT.Y escribimos la sintaxis:
Drop database nombre_base;
Figura 73. Borrar una base de datos.
Manera gráfica
Nos posicionamos en una base que ya se haya creado y damos click derecho, que desplegará un menú y daremos la opción delete/drop.
Figura 74. Borrar una base de datos.
TIPOS DE DATOS EN POSTGRESQL
En PostgreSQL disponemos de gran variedad de Data types (tipos de datos) nativos en PostgreSQL que podemos utilizar para almacenar nuestros valores. La variedad de tipos de datos variará según para lo que necesitemos, podemos decantarnos por los más corriente y usados, como los tipos numéricos, de cadenas, fechas, booleanos, etc. Pero, además de éstos data types típicos, disponemos de otros data types que no son tan corrientes y son utilizados solo para casos puntuales, estos son los tipos binarios, enumeradores, arrays, JSONs, geométricos, Network, email y muchos más.
Tipos de dato de propósito general
Figura 75. Tipos generales en PostgreSQL.
Tipos de dato numéricos
Figura 76. Tipo de datos numéricos de PostgreSQL..
Tipos de datos fecha-hora
Figura 77. Tipo de datos fecha/hora de PostgreSQL.
Tipos de datos geométricos
Figura 78. Tipos de datos geométricos de PostgreSQL
Tipos de datos de direcciones de red
Figura 79. Tipo de datos de direcciones de red
TABLAS EN POSTGRESQL
¿Qué es y como crear una tabla?
Una tabla es una estructura de datos que organiza los datos en columnas y filas; cada columna es un campo (o atributo) y cada fila, un registro. La intersección de una columna con una fila, contiene un dato específico, un solo valor.
Cada registro contiene un dato por cada columna de la tabla. Cada campo (columna) debe tener un nombre. El nombre del campo hace referencia a la información que almacenará. Cada campo (columna) también debe definir el tipo de dato que almacenará.
Al crear una tabla debemos resolver qué campos (columnas) tendrá y que tipo de datos almacenarán cada uno de ellos, es decir, su estructura.
La sintaxis básica para crear una tabla es la siguiente:
create table NOMBRETABLA(
NOMBRECAMPO1 TIPODEDATO,
...
NOMBRECAMPON TIPODEDATO);
Estructura de una tabla
El subcomando \d admite de manera opcional un nombre de relación (tabla, vista, índice, etc.) o patrón como parámetro. Si se especifica un patrón, \d lista todas las relaciones que coinciden con el mismo. Aunque si se especifica un nombre de relación, \d lista todas las columnas, junto con su tipo y atributos. De manera similar a DESCRIBE en MySQL.
Por ejemplo, si se desea examinar la estructura (listar las columnas) de la tabla “alumno“ dentro de la base de datos “escuela” se usará la siguiente instrucción:
escuela=# \d alumno
Crear y eliminar campos
En ocasiones después de crear una tabla queremos añadir un nuevo campo en la misma, ya sea por que olvidamos insertarlo cuando la creamos o por que el cliente nos pidió un cambio de último momento, para realizar dicha acción PostgreSQL nos ofrece la siguiente instrucción:
DB=# alter table nombre_tabla add campo_en_cuestión;
Asimismo, cuando creamos una tabla nos podemos llegar a equivocar al crear un campo, en dichas ocasiones PostgreSQL también nos ofrece una instrucción para eliminar ese campo que está incorrecto, esta instrucción es muy parecida a la que utilizamos para insertar un campo.
La sintaxis básica para eliminar un campo de una tabla es la siguiente:
DB=# alter table nombre_tabla drop campo_en_cuestión;
ÍNDICES, CLAVES PRIMARIAS, CLAVES PRIMARIAS, CLAVES FORÁNEAS, RELACIONES ENTRE TABLAS
Índices en PostgreSQL
Para facilitar la obtención de información de una tabla se utilizan índices.
El índice de una tabla desempeña la misma función que el índice de un libro: permite encontrar datos rápidamente; en el caso de las tablas, localiza registros.
Una tabla se indexa por un campo (o varios).
El objetivo de un índice es acelerar la recuperación de información.
Los índices se usan para varias operaciones:
- Para buscar registros rápidamente.
- Para recuperar registros de otras tablas empleando "join".
Es importante identificar el o los campos por los que sería útil crear un índice, aquellos campos por los cuales se realizan operaciones de búsqueda con frecuencia.
Tipos de índices
1) “Primary key": es el que definimos como clave primaria. Los valores indexados deben ser únicos y además no pueden ser nulos. PostgreSQL le da el nombre "PRIMARY". Una tabla solamente puede tener una clave primaria.
2) “Índex": crea un indice común, los valores no necesariamente son únicos y aceptan valores "null". Podemos darle un nombre, si no se lo damos, se coloca uno por defecto. "key" es sinónimo de "índex". Puede haber varios por tabla.
3) “Unique": crea un indice para los cuales los valores deben ser únicos y diferentes, aparece un mensaje de error si intentamos agregar un registro con un valor ya existente. Permite valores nulos y pueden definirse varios por tabla. Podemos darle un nombre, si no se lo damos, se coloca uno por defecto.
Tipos de índices (Drop y create)
Vamos a trabajar con la tabla "libros".
Un campo por el cual realizamos consultas frecuentemente es "editorial", indexar la tabla por ese campo sería útil.
Creamos un índice:
create index I_libros_editorial on libros(editorial);
Debemos definir un nombre para el índice (en este caso utilizamos como nomenclatura el carácter I, luego el nombre de la tabla y finalmente el o los nombres del campo por el cual creamos el índice. Luego de la palabra clave on indicamos el nombre de la tabla y entre paréntesis el nombre del campo o los campos por el cual se indexa.
Veamos otro tipo de índice llamado "único". Un índice único se crea con "create unique index", los valores deben ser únicos y diferentes, aparece un mensaje de error si intentamos agregar un registro con un valor ya existente. Permite valores nulos y pueden definirse varios por tabla.
create unique index I_libros_tituloeditorial on libros(titulo,editorial);
Para eliminar un índice usamos "drop index". Ejemplo:
drop index I_libros_editorial;
drop index I_libros_tituloeditorial;
Se elimina un índice con "drop index" seguido de su nombre.
Podemos eliminar los índices creados, pero no el creado automáticamente con la clave primaria.
Claves primarias en PostgreSQL
En el diseño de bases de datos relacionales, se llama clave primaria o clave principal a un campo o a una combinación de campos que identifica de forma única a cada fila de una tabla. Una clave primaria comprende de esta manera una columna o conjunto de columnas. No puede haber dos filas en una tabla que tengan la misma clave primaria.
Figura 80. Primary key en PostgreSQL
Claves foráneas en PostgreSQL
Con la restricción "foreign key" se define un campo (o varios) cuyos valores coinciden con la clave primaria de la misma tabla o de otra, es decir, se define una referencia a un campo con una restricción "primary key" o "unique" de la misma tabla o de otra.
La integridad referencial asegura que se mantengan las referencias entre las claves primarias y las externas. Por ejemplo, controla que si se agrega un código de editorial en la tabla "libros", tal código exista en la tabla "editoriales".
También controla que no pueda eliminarse un registro de una tabla ni modificar la clave primaria si una clave externa hace referencia al registro. Por ejemplo, que no se pueda eliminar o modificar un código de "editoriales" si existen libros con dicho código.
La siguiente es la sintaxis parcial general para agregar una restricción "foreign key":
alter table NOMBRETABLA1
add constraint NOMBRERESTRICCION
foreign key (CAMPOCLAVEFORANEA)
references NOMBRETABLA2 (CAMPOCLAVEPRIMARIA);
Agregar una llave foránea
Para agregar una restricción "foreign key" al campo "codigoeditorial" de "libros", tipeamos:
alter table libros
add constraint FK_libros_codigoeditorial
foreign key (codigoeditorial)
references editoriales(codigo);
En el ejemplo implementamos una restricción "foreign key" para asegurarnos que el código de la editorial de la tabla "libros" ("codigoeditorial") esté asociada con un código válido en la tabla "editoriales" ("codigo").
Relaciones entre tablas PostgreSQL
Cada join combina 2 tablas. Se pueden emplear varios join para enlazar varias tablas. Cada resultado de un join es una tabla que puede combinarse con otro join.
La librería almacena los datos de sus libros en tres tablas: libros, editoriales y autores.
En la tabla "libros" un campo "codigoautor" hace referencia al autor y un campo "codigoeditorial" referencia la editorial.
Para recuperar todos los datos de los libros empleamos la siguiente consulta:
select titulo,a.nombre,e.nombre
from autores as a
join libros as l
on codigoautor=a.codigo
join editoriales as e
on codigoeditorial=e.codigo;
Analicemos la consulta anterior. Indicamos el nombre de la tabla luego del "from" ("autores"), combinamos esa tabla con la tabla "libros" especificando con "on" el campo por el cual se combinarán; luego debemos hacer coincidir los valores para el enlace con la tabla "editoriales" enlazándolas por los campos correspondientes. Utilizamos alias para una sentencia más sencilla y comprensible.
Note que especificamos a qué tabla pertenecen los campos cuyo nombre se repiten en las tablas, esto es necesario para evitar confusiones y ambiguedades al momento de referenciar un campo.
Note que no aparecen los libros cuyo código de autor no se encuentra en "autores" y cuya editorial no existe en "editoriales", esto es porque realizamos una combinación interna.
Podemos combinar varios tipos de join en una misma sentencia:
select titulo,a.nombre,e.nombre
from autores as a
right join libros as l
on codigoautor=a.codigo
left join editoriales as e on codigoeditorial=e.codigo;
En la consulta anterior solicitamos el título, autor y editorial de todos los libros que encuentren o no coincidencia con "autores" ("right join") y a ese resultado lo combinamos con "editoriales", encuentren o no coincidencia.
Es posible realizar varias combinaciones para obtener información de varias tablas. Las tablas deben tener claves externas relacionadas con las tablas a combinar.
En consultas en las cuales empleamos varios "join" es importante tener en cuenta el orden de las tablas y los tipos de "join"; recuerde que la tabla resultado del primer join es la que se combina con el segundo join, no la segunda tabla nombrada. En el ejemplo anterior, el "left join" no se realiza entre las tablas "libros" y "editoriales" sino entre el resultado del "right join" y la tabla "editoriales".
FUNCIONES AGREGADAS
Count
SELECT Count(FechaEnvío & Transporte) AS Total FROM Pedidos;
Calcula el número de registros devueltos por una consulta. Su sintaxis es la siguiente:
Count(expr)
En donde expr contiene el nombre del campo que desea contar. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos incluso texto.
Aunque expr puede realizar un cálculo sobre un campo, Count simplemente cuenta el número de registros sin tener en cuenta qué valores se almacenan en los registros. La función Count no cuenta los registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un asterisco, Count calcula el número total de registros, incluyendo aquellos que contienen campos null. Count(*) es considerablemente más rápida que Count(Campo). No se debe poner el asterisco entre dobles comillas ('*').
SELECT Count(*) AS Total FROM Pedidos;
Si expr identifica a múltiples campos, la función Count cuenta un registro sólo si al menos uno de los campos no es Null. Si todos los campos especificados son Null, no se cuenta el registro. Hay que separar los nombres de los campos con ampersand (&).
SELECT Count(FechaEnvío & Transporte) AS Total FROM Pedidos;
Max,Min
Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es:
Min(expr)
Max(expr)
En donde expr es el campo sobre el que se desea realizar el cálculo. Expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).
SELECT Min(Gastos) AS ElMin FROM Pedidos WHERE Pais = 'España';
SELECT Max(Gastos) AS ElMax FROM Pedidos WHERE Pais = 'España';
AVG
Calcula la media aritmética de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente
Avg(expr)
En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.
SELECT Avg(Gastos) AS Promedio FROM Pedidos WHERE Gastos > 100;
IMPORTAR, EXPORTAR ARCHIVO DE DATOS DESDE POSTGRESQL
La respuesta es sencilla, el hecho de importar nuestros datos fuera de nuestra base de datos y almacenarlos de manera independiente es bueno, debido a que nos genera un respaldo en automático de la información abarcando desde la estructura de la base de datos hasta los registros en ella, por ende esta es una manera sencilla de salvaguardar nuestros archivos por si llegase a ocurrir una desgracia.
Realmente podemos importar todo tipo de archivos que cumplan con los requerimientos de la base de datos, es decir que cumplan las condiciones para poder insertar los datos de manera masiva, lo más recomendable para esto es siempre exportar datos con una extensión CSV.
¿Qué son los archivos CSV?
Los archivos CSV (del inglés comma-separated values) son un tipo de documento en formato abierto sencillo para representar datos en forma de tabla, en las que las columnas se separan por comas y las filas por saltos de línea.Los archivos CSV conforman una de las mejores formas de crear seed’s de información para nuestra aplicación. Importar y exportar CSV en PostgreSQL es muy fácil.
Importar y exportar base de datos
En PostgreSQL es muy conveniente en muchas ocasiones usar pg_dump para realizar la exportación de los datos de una base de datos, para luego importarlo a otra base de datos. O para realizar backups.
Figura 81. Crear tabla productos
Figura 82. Registros que se importar
copy productos (nombre_producto, precio, stock) from ‘C:/data/productos.csv’ delimiter ‘,’ csv header;
Lo que esta instrucción nos quiere decir es copia a la base de datos “productos” en los campos nombre_producto, precio, stock de la siguiente ruta ‘C:/data/productos.csv’ delimitado por comas con la extensión :CSV.
Esta es la instrucción para importar nuestros datos de un archivo de Excel a una base de datos y como se puede observar de una manera sencilla, en nuestra consola se ve de la siguiente manera:
Figura 83. Exportar una base de datos
Para exportar datos de PostgreSQL a Excel o al Bloc de Notas es muy sencillo, pues se utiliza la misma cláusula copy y sólo cambian unas cuantas cosillas.
Sintaxis:
copy productos (id_producto, nombre_producto, precio, stock) to ‘C:/data/productos.csv' delimiters ',' with CSV Header;
Figura 84. Importar archivos en PostgreSQL
Figura 85 Contenido del archivo que se importara.
Así de fácil es exportar datos de PostgreSQL a Excel o al Bloc de Notas.
RESPALDO E IMPORTACIÓN DE BASE DE DATOS DE POSTGRESQL
Respaldo de una base de datos
Para poder usar esta función el usuario debe de tener todos los privilegios como el super admin en el gestor de base de datos por lo tanto usamos la instrucción de poder ver el usuario
#su postgre
Ejecutamos el siguiente comando:
#pg_dump -u postgre db_basededatos > respaldo_basededatos.sql
db_basededatos: Es el nombre de la base de datos que se desea hacer un respaldo.
respaldo_basededatos.sql: Es el nombre que se le asignara al respaldo que se va a ejecutar este tiene que tener terminación de ".sql".
Ejemplo:
#pg_dumll -u postgre > misbasededatos_al.sql
Para restaurar el respaldo primero creamos la base donde se tiene que respaldar la base con la siguiente instrucción:
postgre=#create database mi_basededatos;
Y para salimos con la instrucción
postgre=#\q
Después ejecutemos lo siguiente:
#psql -h localhost -p 5432 -u postgre -f
"respaldo_basededados.sql" mi_basededatos
CONTROL DE CONCURRENCIA
Se refiere al hecho de que los Sistemas Administradores de Base de Datos permiten que muchas transacciones accedan a una misma Base de Datos a la vez. Cuando existen varios usuarios intentando modificar los datos al mismo tiempo, se necesita establecer algún tipo de control para que dichas modificaciones de un usuario no interfieran en las de los otros, a este sistema se le denomina control de concurrencia.
Comandos
Begin
Cuando utilizamos este comando el sistema permite que se ejecuten todas las sentencias SQL que necesitemos y las registra en un fichero.
Commit
Cuando ejecutamos este comando estamos confirmando que todas las sentencias son correctas. Hasta que no se ejecute el comando COMMIT, las sentencias no quedarán registradas. Por ejemplo, si cerramos la conexión antes de ejecutar este comando no se verá afectada ninguna de las relaciones de la base de datos.
Rollback
Con este comando podemos desechar las transacciones que se hayan ejecutado. Por lo tanto, después de haber realizado y confirmado una transacción, PostgreSQL nos permite anular dicha transacción de forma que no se modifique los datos de nuestra base de datos.
Problemas con el control de concurrencia
Existen tres formas en las que una transacción, aunque sea correcta por sí misma, puede producir una respuesta incorrectas y alguna otra transacción interfiere con ella en alguna forma. Consideremos que la transacción que interfiere también puede ser correcta; lo que produce el resultado incorrecto general es el intercalado sin control entre las operaciones de las dos transacciones correctas. Los tres problemas son:
- El problema de la Actualización Perdida
- El problema de la Dependencia No Confirmada
- El problema del Análisis Inconsistente
USUARIOS Y ROLES
Usuarios
En PostgreSQL, los roles son objetos globales que puede acceder a todas las bases de datos de cluster (contando con los privilegios adecuados). Los roles están completamente separados de los usuarios a nivel sistema operativo, aunque es conveniente mantener una correspondencia entre los mismos. A fin de inicializar un sistema de bases de datos, cada instalación fresca siempre contiene un rol predefinido. Este rol es siempre un superusuario, y tiene el mismo nombre (a menos que sea cambiado cuando se corre initdb) que el del usuario (a nivel sistema operativo) que inicializó el cluster de bases de datos. Como habrán notado en artículos anteriores, habitualmente se utiliza el nombre de usuario "Postgres" para dicho rol. Los roles determinan el conjunto de privilegios disponibles a un cliente conectado.
Figura 86. Mostrar los roles de las bases de datos en PostgreSQL
Creación de usuarios
La sintaxis para crear usuarios en PostgreSQL es la siguiente:
CREATE USER nombre_usuario WITH PASSWORD 'password-del-usuario';
GRANT ALL PRIVILEGES ON DATABASE nombre_basededatos;
\q
Figura 87. Crear un rol
Ahora vamos a conectarnos con este usuario:psql -d nombre_base_de_datos -U nombre_del_usuario
Figura 88 Usar el usuario rol
¿Qué rol posee un usuario?
Es posible listar usuarios/roles utilizando el subcomando \du (o \do)
Figura 89. Mostrar los roles creados
Para diferenciar usuarios de roles, basta notar que los roles no tienen login ("Cannot login"). Por ejemplo "lenny" es un usuario y "role_ro" un rol. Notar además que esta tabla indica a qué rol pertenece cada usuario. Por ejemplo "lenny" pertenece al rol "role_devel".
Roles de base de datos
Los roles de la base de datos están en un concepto completamente separados de los usuarios del sistema operativo. En la práctica, puede resultar conveniente mantener una correspondencia, pero no es necesario. Los roles de la base de datos son globales en una instalación de clúster de base de datos (y no por base de datos individual). Para crear un rol, use el comando:
Crear roles en PostgreSQL
PostgreSQL administra los permisos de acceso a la base de datos utilizando el concepto de roles . Se puede pensar en un rol como un usuario de base de datos o un grupo de usuarios de base de datos, dependiendo de cómo esté configurado el rol. Los roles pueden poseer objetos de base de datos (por ejemplo, tablas) y pueden asignar privilegios sobre esos objetos a otros roles para controlar quién tiene acceso a qué objetos. Además, es posible otorgar membresía en un rol a otro rol, permitiendo así que el rol de miembro use los privilegios asignados al rol del que es miembro.
El concepto de roles subsume los conceptos de "usuarios" y "grupos" . En las versiones de PostgreSQL anteriores a 8.1, los usuarios y los grupos eran tipos distintos de entidades, pero ahora solo hay roles. Cualquier rol puede actuar como usuario, grupo o ambos.
Name sigue las reglas para los identificadores SQL: sin adornos sin caracteres especiales o entre comillas dobles. (En la práctica, generalmente querrá agregar opciones adicionales, como LOGIN , al comando. Más detalles aparecen a continuación). Para eliminar un rol existente, use el comando análogo DROP ROLE
DROP ROLE nombre;
Para determinar el conjunto de roles existentes, examine el catálogo del sistema pg_roles , por ejemploSELECCIONE rolname FROM pg_roles;
Parámetros de roles
Name
El nombre del nuevo rol.
CREATEDB
NOCREATEDB: Estas cláusulas definen la capacidad de un rol para crear bases de datos. Si CREATEDB se especifica, el rol que se está definiendo podrá crear nuevas bases de datos. Especificar NOCREATEDB negará a un rol la capacidad de crear bases de datos. Si no se especifica, NOCREATEDB es el predeterminado.
CREATEROLE
NOCREATEROLE: Estas cláusulas determinan si a un rol se le permitirá crear nuevos roles (es decir, ejecutar CREATE ROLE). Un rol con CREATEROLE privilegios también puede alterar y eliminar otros roles. Si no se especifica, NOCREATEROLE es el predeterminado.
INHERIT
NOINHERIT: Estas cláusulas determinan si un rol " hereda " los privilegios de los roles de los que es miembro. Un rol con el INHERIT atributo puede usar automáticamente los privilegios de base de datos que se hayan otorgado a todos los roles de los que es miembro directa o indirectamente. Sin él INHERIT, la pertenencia a otro rol solo otorga la capacidad SET ROLE para ese otro rol; los privilegios del otro rol solo están disponibles después de haberlo hecho. Si no se especifica, INHERIT es el predeterminado.
LOGIN
NOLOGIN:
Estas cláusulas determinan si un rol puede iniciar sesión; es decir, si el rol se puede asignar como nombre de autorización de sesión inicial durante la conexión del cliente. Un rol que tiene el LOGIN atributo puede considerarse un usuario. Los roles sin este atributo son útiles para administrar los privilegios de la base de datos, pero no son usuarios en el sentido habitual de la palabra. Si no se especifica, NOLOGIN es el predeterminado, excepto cuando CREATE ROLE se invoca mediante su ortografía alternativa CREAR USUARIO .
REPLICATION
NOREPLICATION
Estas cláusulas determinan si un rol es un rol de replicación. Un rol debe tener este atributo (o ser un superusuario) para poder conectarse al servidor en modo de replicación (replicación física o lógica) y para poder crear o eliminar ranuras de replicación. Un rol que tiene el REPLICATION atributo es un rol muy privilegiado y solo debe usarse en roles que realmente se usan para la replicación. Si no se especifica, NOREPLICATION es el predeterminado. Debe ser un superusuario para crear un nuevo rol que tenga el REPLICATION atributo.
Ejemplos Cree un rol que pueda iniciar sesión, pero no le dé una contraseña: CREAR PAPEL jonathan LOGIN;
Cree un rol con una contraseña: CREAR USUARIO davide CON CONTRASEÑA 'jw8s0F4'; ( CREATE USERes lo mismo que CREATE ROLEexcepto que implica LOGIN.)
Cree un rol con una contraseña que sea válida hasta finales de 2004. Después de que haya marcado un segundo en 2005, la contraseña ya no es válida. CREAR PAPEL miriam CON LA CONTRASEÑA DE INICIO DE SESIÓN 'jw8s0F4' VÁLIDA HASTA '2005-01-01';
Cree un rol que pueda crear bases de datos y administrar roles: CREAR ROL admin CON CREATEDB CREATEROLE;
Modificar roles
CURRENT_USER Modifique el usuario actual en lugar de un rol identificado explícitamente
.SESSION_USER Modifique el usuario de la sesión actual en lugar de un rol identificado explícitamente.
new_name El nuevo nombre del rol.
database_name El nombre de la base de datos en la que se debe establecer la variable de configuración.
Ejemplos Cambiar la contraseña de un rol: ALTER ROL davide CON CONTRASEÑA 'hu8jmn3';
Eliminar la contraseña de un rol: ALTER ROL davide CON PASSWORD NULL;
Cambie la fecha de vencimiento de una contraseña, especificando que la contraseña debe vencer al mediodía del 4 de mayo de 2015 utilizando la zona horaria que está una hora antes de UTC : ALTER ROLE chris VÁLIDO HASTA '4 de mayo 12:00:00 2015 +1';
Hacer que una contraseña sea válida para siempre: ALTER ROLE fred VÁLIDO HASTA 'infinito'; Otorgue a un rol la capacidad de crear otros roles y nuevas bases de datos: ALTER PAPEL miriam CREATEROLE CREATEDB;
Asigne a un rol una configuración no predeterminada del parámetro maintenance_work_mem : ALTER ROLE worker_bee SET maintenance_work_mem = 100000; Asigne a un rol una configuración no predeterminada, específica de la base de datos del parámetro client_min_messages : ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
Fuentes:
Apuntes de clase(2020).