Postgres con Django

change_own.sh

#!/bin/bash
#
# The MIT License
#
# Copyright 2014 Jakub Jirutka <[email protected]>.
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.
 
# Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto
 
 
usage() {
	cat <<- EOF
		usage: $0 options
		
		This script changes ownership for all tables, views, sequences and functions in
		a database schema and also owner of the schema itself.
		
		Note: If you want to change the ownership of all objects, in the specified database,
		owned by a database role, then you can simply use command "REASSIGN OWNED".
		
		OPTIONS:
		   -h    Show this message
		   -d    Database name
		   -o    New owner name
		   -s    Schema (defaults to public)
	EOF
}
 
pgexec() {
	local cmd=$1
	psql --no-psqlrc --no-align --tuples-only --record-separator=\0 --quiet \
		--command="$cmd" "$DB_NAME"
}
 
pgexec_echo() {
	local cmd=$1
	psql --no-psqlrc --no-align --tuples-only --record-separator=\0 --quiet \
		--echo-queries --command="$cmd" "$DB_NAME"
}
 
 
DB_NAME=''
NEW_OWNER=''
SCHEMA='public'
while getopts "hd:o:s:" OPTION; do
	case $OPTION in
		h)
			usage
			exit 1
			;;
		d)
			DB_NAME=$OPTARG
			;;
		o)
			NEW_OWNER=$OPTARG
			;;
		s)
			SCHEMA=$OPTARG
			;;
	esac
done
 
if [[ -z "$DB_NAME" ]] || [[ -z "$NEW_OWNER" ]]; then
	usage
	exit 1
fi
 
# Using the NULL byte as the separator as its the only character disallowed from PG table names.
IFS=\0
 
# Change owner of schema itself.
pgexec_echo "ALTER SCHEMA \"${SCHEMA}\" OWNER TO \"${NEW_OWNER}\";"
 
# Change owner of tables and views.
for tbl in $(pgexec "SELECT table_name FROM information_schema.tables WHERE table_schema = '${SCHEMA}';") \
		   $(pgexec "SELECT table_name FROM information_schema.views WHERE table_schema = '${SCHEMA}';"); do
	pgexec_echo "ALTER TABLE \"${SCHEMA}\".\"${tbl}\" OWNER TO ${NEW_OWNER};"
done
 
# Change owner of sequences.
for seq in $(pgexec "SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = '${SCHEMA}';"); do
	pgexec_echo "ALTER SEQUENCE \"${SCHEMA}\".\"${seq}\" OWNER TO ${NEW_OWNER};"
done
 
# Change owner of functions and procedures.
for func in $(pgexec "SELECT quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' \
					  FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace \
					  WHERE n.nspname = '${SCHEMA}';"); do
	pgexec_echo "ALTER FUNCTION \"${SCHEMA}\".${func} OWNER TO ${NEW_OWNER};"
done
 
# Revert separator back to default.
unset IFS

Utiles Postgres

# Útiles para el uso de Postgres con Django

#Instalación en Ubuntu, sin tener activado entornos virtuales
sudo apt-get update
sudo apt-get install libpq-dev python-dev
sudo apt-get install postgresql postgresql-contrib

#Para cualquier uso de postgres cambiar al usuario postgres
sudo su - postgres

#Antes de hacer nada comprobar que el collation está en UTF8 para no tener problemas luego con la codificación de caracteres
#Podemos hacerlo manualmente o crear un script con el siguiente contenido y ejecutarlo
#Sacado de: https://gist.github.com/ffmike/877447 y https://gist.github.com/fgrehm/5436485
#Tablas de condificación de postgres: http://ingdesistemasvzla.blogspot.com.es/2011/02/cambiar-encoding-de-utf-8-latin1-en.html
if ! $(psql template1 -c 'SHOW SERVER_ENCODING' | grep -q UTF8); then
  psql postgres -c "update pg_database set datallowconn = TRUE where datname = 'template0';"
  psql template0 -c "update pg_database set datistemplate = FALSE where datname = 'template1';"
  psql template0 -c "drop database template1;"
  psql template0 -c "create database template1 with owner=postgres encoding='UTF-8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;"
  psql template0 -c "create database template1 with template = template0 encoding = 'UTF8';"
  psql template0 -c "update pg_database set datistemplate = TRUE where datname = 'template1';"
  psql template1 -c "update pg_database set datallowconn = FALSE where datname = 'template0';"
fi

#Crear base de datos
createdb nombre_de_la_bd

#Crear usuario, no darle permisos para crear ni borrar bases de datos
createuser -P

#Dar privilegios a la base de datos al usuario creado y bd creada
psql
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

#Dar privilegios a un usuario concreto para usar la bd, tablas y sequencias
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

#Comandos útiles
\q Salir
\l Listar las bases de datos
\du Listar los roles activos
ALTER USER Postgres WITH PASSWORD '<newpassword>';

#Para Django, estando el entorno virtual activado, instalar el driver para postgres
pip install psycopg2

#Configurar los settings
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mydb',
        # The following settings are not used with sqlite3:
        'USER': 'myuser',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '',
    }
}

#Hacer un backup
# https://www.digitalocean.com/community/tutorials/how-to-backup-postgresql-databases-on-an-ubuntu-vps
sudo su - postgres
pg_dump postgres > postgres_db.bak
pg_dump -h remote_host -p remote_port name_of_database > name_of_backup_file
pg_dump -U user_name -h remote_host -p remote_port name_of_database > name_of_backup_file
#Backup sin permisos ni roles
pg_dump --no-privileges --no-owner --no-reconnect db > file.bck
pg_dump --no-privileges --no-owner --no-reconnect db > $( date '+%Y-%m-%d_%H-%M-%S' ).bck


#Restaurar
psql empty_database < backup_file
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump 

#Si hemos hecho un empaqutado con pg_dump tenemos que restaurar con
pg_restore -d dbname filename

#Connectarse a una base de datos
\connect nombredelabasededatos

#Habilitar el acceso desde fuera
#Editar el archivo pg_hba.conf
sudo nano /etc/postgresql/9.3/main/pg_hba.conf
#Añadir una línea al final
host all all  0.0.0.0/0 md5

#Editar el archivo postgresql.conf
sudo nano /etc/postgresql/0.3/main/postgresql.conf
listen_addresses = '*'