"""
PostgreSQL users and databases
==============================
This module provides tools for creating PostgreSQL users and databases.
"""
from fabric.api import cd, hide, sudo, settings
def _run_as_pg(command):
"""
Run command as 'postgres' user
"""
with cd('~postgres'):
return sudo(command, user='postgres')
[docs]def user_exists(name):
"""
Check if a PostgreSQL user exists.
"""
with settings(hide('running', 'stdout', 'stderr', 'warnings'),
warn_only=True):
res = _run_as_pg('''psql -t -A -c "SELECT COUNT(*) FROM pg_user WHERE usename = '%(name)s';"''' % locals())
return (res == "1")
[docs]def create_user(name, password, superuser=False, createdb=False,
createrole=False, inherit=True, login=True,
connection_limit=None, encrypted_password=False):
"""
Create a PostgreSQL user.
Example::
import fabtools
# Create DB user if it does not exist
if not fabtools.postgres.user_exists('dbuser'):
fabtools.postgres.create_user('dbuser', password='somerandomstring')
# Create DB user with custom options
fabtools.postgres.create_user('dbuser2', password='s3cr3t',
createdb=True, createrole=True, connection_limit=20)
"""
options = [
'SUPERUSER' if superuser else 'NOSUPERUSER',
'CREATEDB' if createdb else 'NOCREATEDB',
'CREATEROLE' if createrole else 'NOCREATEROLE',
'INHERIT' if inherit else 'NOINHERIT',
'LOGIN' if login else 'NOLOGIN',
]
if connection_limit is not None:
options.append('CONNECTION LIMIT %d' % connection_limit)
password_type = 'ENCRYPTED' if encrypted_password else 'UNENCRYPTED'
options.append("%s PASSWORD '%s'" % (password_type, password))
options = ' '.join(options)
_run_as_pg('''psql -c "CREATE USER %(name)s %(options)s;"''' % locals())
def drop_user(name):
"""
Drop a PostgreSQL user.
Example::
import fabtools
# Remove DB user if it exists
if fabtools.postgres.user_exists('dbuser'):
fabtools.postgres.drop_user('dbuser')
"""
_run_as_pg('''psql -c "DROP USER %(name)s;"''' % locals())
[docs]def database_exists(name):
"""
Check if a PostgreSQL database exists.
"""
with settings(hide('running', 'stdout', 'stderr', 'warnings'),
warn_only=True):
return _run_as_pg('''psql -d %(name)s -c ""''' % locals()).succeeded
[docs]def create_database(name, owner, template='template0', encoding='UTF8',
locale='en_US.UTF-8'):
"""
Create a PostgreSQL database.
Example::
import fabtools
# Create DB if it does not exist
if not fabtools.postgres.database_exists('myapp'):
fabtools.postgres.create_database('myapp', owner='dbuser')
"""
_run_as_pg('''createdb --owner %(owner)s --template %(template)s \
--encoding=%(encoding)s --lc-ctype=%(locale)s \
--lc-collate=%(locale)s %(name)s''' % locals())
def drop_database(name):
"""
Delete a PostgreSQL database.
Example::
import fabtools
# Remove DB if it exists
if fabtools.postgres.database_exists('myapp'):
fabtools.postgres.drop_database('myapp')
"""
_run_as_pg('''dropdb %(name)s''' % locals())
def create_schema(name, database, owner=None):
"""
Create a schema within a database.
"""
if owner:
_run_as_pg('''psql %(database)s -c "CREATE SCHEMA %(name)s AUTHORIZATION %(owner)s"''' % locals())
else:
_run_as_pg('''psql %(database)s -c "CREATE SCHEMA %(name)s"''' % locals())