PDF de programación - Manual básico de PostgreSQL

MANUAL BÁSICO DE POSTGRESQL



ESCUELA COLOMBIANA DE INGENIERÍA

JULIO GARAVITO

LABORATORIO DE INFORMÁTICA

BOGOTÁ D. C.

2007-2





TABLA DE CONTENIDO



2.1.1
2.1.2

INTRODUCCIÓN .................................................................................................. 3
1.
AUTENTICACIÓN EN POSTGRESQL ...................................................... 4
1.1 Autenticación de Contraseña ...................................................................... 4
2.
ADMINISTRACIÓN DE POSTGRESQL ..................................................... 5
2.1 Gestión de Usuarios ................................................................................... 5
CREATE USER ............................................................................................... 5
createuser .......................................................................................................... 7
2.2 Modificación de Usuarios ............................................................................ 8
2.3 Eliminación de Usuarios.............................................................................. 8
2.4 Manejo de Grupos ...................................................................................... 9
OTRAS FUNCIONES BÁSICAS DE PostgreSQL ..................................... 10
3.
TIPOS DE DATOS PostgreSQL ............................................................... 13
4.
5.
TIPOS NUMÉRICOS PARA PostgreSQL ................................................. 14


INTRODUCCIÓN



En este manual se encuentras las funciones básicas de PostgreSQL, como la
autentificación ante PostgreSQL, el manejo de contraseñas, el manejo de
usuarios (creación, modificación y eliminación) y sus privilegios, el manejo de
grupos, (creación, eliminación), algunas otras funciones de PostgreSQL, como lo
son los triggers, la creación, modificación y eliminación de tablas, creación y
eliminación de bases de datos, los tipos de datos y los tipos numéricos.

Para cada sentencia o comando se muestra su sintaxis, y se parte del hecho de
que la persona que haga uso de este manual, tiene conocimientos básicos,
sobre el lenguaje SQL.

1. AUTENTICACIÓN EN POSTGRESQL



PostgreSQL es un Sistema de Gestión de Bases de Datos Objeto-Relacionales.
Comenzó como un proyecto denominado Ingres en la Universidad Berkeley de
California. Ingres fue desarrollado comercialmente más tarde por la Relational
Technologies / Ingres Corporation.

A partir de PostgreSQL 7.1.x, los accesos de clientes basados en máquina
(host) se encuentran especificados en el archivo pg_hba.conf. El archivo
pg_hba.conf le permite establecer el tipo de autenticación basasda en máquina a
ser usada. Esta autenticación es realizada antes de que PostgreSQL estblezca
una conexión a la base de datos en cuestión, donde los permisis de usuarios
serían relevantes.

El archivo pg_hba.conf está localizado en el directorio de datos de PostgreSQL
(p.ej., /usr/local/pgsql/data/), y es instalado automáticamente con la ejecución del
comando initdb cuando PostgreSQL es instalado.


1.1 Autenticación de Contraseña



Las contraseñas de usuario son almacenadas en un texto plano en la tabla de
sistema pg_shadow, pero sólo los superusuarios de PostgreSQL tienen permiso
para ver la tabla pg_shadow y esta tabla además es accesible desde cualquier
base de datos.

La estructura de la tabla es:


Columna

usename

usesysid

usecreatedb

usetrace

usesuper

usecatupd

passwd

valuntil

Tipo

name

integer

boolean

boolean

boolean

boolean

text

abstime

En dado caso que la contraseña no sea definida, por defecto el sistema asignara
NULL.

2. ADMINISTRACIÓN DE POSTGRESQL


PostgreSQL almacena los datos de usuarios así como también los datos de los
grupos dentro de sus propios catálogos de sistema. De esta manera, cualquier
conexión a PostgreSQL debe ser realizada con un usuario específico, y
cualquier usuario puede pertenecer a uno o más grupos definidos.

La tabla de usuarios en PostgreSQL controla los permisos de acceso y quién
está autorizado a realizar acciones en el sistema, al igual qué las acciones
puede realizar. Los grupos existen como un mecanismo para simplificar la
ubicación de estos permisos. Tanto las tablas de usuarios como de grupos
existen como objetos globales de base de datos, por consiguiente no están
agregadas a ninguna base de datos en particular.


2.1 Gestión de Usuarios

Cada usuario tiene un ID de sistema interno en PostgreSQL (llamado sysid),
así como una contraseña. El ID es utilizado para asociar objetos en una base
de datos con su propietario

PostgreSQL crea por defecto a un superusuario llamado postgres. Todos los
demás superusuarios pueden ser creados por éste, o por cualquier otro
superusuario creado posteriormente.

PostgreSQL proporciona dos métodos para la creación de usuarios de bases
de datos. Cada uno de ellos requiere autenticación como superusuario.

Los métodos son:

• A través del uso del comando SQL CREATE USER.
• Un programa de línea de comandos llamado createuser



2.1.1 CREATE USER



La sintaxis para CREATE USER es:

CREATE USER nombre_usuario
[ WITH [ SYSID uid ]
[ PASSWORD 'password' ] ]
[ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCREATEUSER ]
[ IN GROUP groupname [, ...] ]
[ VALID UNTIL 'abstime' ]



A continuación se describe cada una de las partes de la sintaxis de CREATE
USER:


• SYSID uid


Especifica que el ID que va a definirse debe establecerse al valor de uid.
Si se omite, un razonable y único valor numérico por defecto es escogido.


• PASSWORD 'password'


Establece la nueva contraseña del usuario. Si no se especifica, la
contraseña por defecto es NULL.


• CREATEDB | NOCREATEDB


Usando la palabra clave CREATEDB se le garantiza al nuevo usuario el
privilegio de crear nuevas bases de datos, así como el de destruir las de
su propiedad. Usando NOCREATEDB se deniega este permiso (que es lo
que ocurre por defecto).

• CREATEUSER | NOCREATEUSER


Certifica el privilegio de crear nuevos usuarios. Si un usuario tiene los
privilegios de crear a otros usuarios tendrá además todos los privilegios,
en todas las bases de datos (incluyendo los permisos para crear una base
de
especificado NOCREATEDB).
NOCREATEUSER explícitamente fuerza a la situación por defecto, que
deniega el privilegio.

IN GROUP nombre_grupo [, ...]

aunque

datos,

se

haya

•

Añade al nuevo usuario al grupo llamado nombre_grupo. Pueden ser
especificados múltiples nombres de grupo, separándolos mediante
comas. El o los grupos deben existir para que funcione la condición.


• VALID UNTIL 'abstime'

Establece que la contraseña del usuario expirará el abstime, el cual debe
ser un formato reconocible de fecha/hora (timestamp). Tras esa fecha, la
contraseña se resetea, y la expiración se hace efectiva.


• VALID UNTIL 'infinity'

Establece validez permanente para la contraseña del usuario.



2.1.2 createuser


El script createuser es ejecutado directamente desde la línea de comandos, y
puede operar de dos formas.

Si se utiliza sin argumentos, él interactivamente le pedirá el nombre de
usuario y cada uno de los privilegios que se le van a asignar. Alternamente,
puede optar por especificar las opciones y el nombre del usuario a ser creado
en la misma línea de comandos.

La sintaxis de createuser es:


createuser [ opciones ] [ nombre_usuario ]


•


•


•


•


•


•


•



El nombre_usuario en la sintaxis representa el nombre del usuario que va a
crear. Reemplace opciones con una o más de las siguientes:

•

-d, -createdb Equivalente a la palabra clave CREATEDB. Permite al
nuevo usuario crear bases de datos.

-D, -no-createdb Equivalente a
la palabra clave NOCREATEDB
Explícitamente indica que el nuevo usuario no puede crear bases de
datos.

-a, -adduser Equivalente a la palabra clave CREATEUSER Perimte al
nuevo usuario la creación de otros usuarios, y asigna el status de
superusurario al usuario.

-A, -no-adduser Equivalente a la palabra clave NOCREATEUSER.
Explícitamente indica que el nuevo usuario no es superusuario.

-i SYSID, -sysid=SYSID Establece el nuevo ID de sistema del usuario a
SYSID.

-P, -pwprompt Resulta en una petición de introducción de contraseña,
permitiéndole establecer la contraseña del nuevo usuario.

-h NOMBRE_MAQUINA, -host=NOMBRE_MAQUINA Especifica desde
qué NOMBRE_MAQUINA se conectará, además de la local (localhost), o
la máquina definida por la variable de entorno PGHOST.

-p PUERTO, -port=PUERTO Especifica que la conexión de base de
datos se realizará por el puerto PUERTO, en vez de por el puerto por
defecto.

-U NOMBRE_USUARIO, -username=NOMBRE_USUARIO Especifica
que NOMBRE_USUARIO será el usuario que conecte a PostgreSQL (por
defecto se conecta usando el nombre de usuario del sistema).

-W, -password Resulta en una petición de contraseña para el usuario
que conecta, lo cual ocurre automáticamente si el archivo pg_hba.conf
está configurado para no confiar en la máquina solicitante.

•


•



2.2 Modificación de Usuarios

Los usuarios existentes sólo pueden ser modificados por superusuarios
PostgreSQL mediante el comando SQL ALTER USER, donde su sintaxis es:


ALTER USER nombre_usuario
[ WITH PASSWORD 'password' ]
[ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCR