The database cookbook has been deprecated

The database cookbook has been deprecated and is no longer being maintained by its authors. Use of the database cookbook is no longer recommended.

RSS

database (52) Versions 5.1.2 Follow171

provides LWRPs for common database tasks

Berkshelf/Librarian

Policyfile

Knife

cookbook 'database', '= 5.1.2'

cookbook 'database', '= 5.1.2', :supermarket

knife cookbook site install database

knife cookbook site download database

README

Dependencies

Changelog

Quality

Database Cookbook

The main highlight of this cookbook is the database and database_user resources for managing databases and database users in a RDBMS. Providers for MySQL, PostgreSQL and SQL Server are also provided, see usage documentation below.

Requirements

Platforms

Debian / Ubuntu derivatives
RHEL derivatives
Fedora

Chef

Chef 11+

Cookbooks

postgresql

Resources/Providers

These resources aim to expose an abstraction layer for interacting with different RDBMS in a general way. Currently the cookbook ships with providers for MySQL, PostgreSQL and SQL Server. Please see specific usage in the Example sections below. The providers use specific Ruby gems installed under Chef's Ruby environment to execute commands and carry out actions. These gems will need to be installed before the providers can operate correctly. Specific notes for each RDBS flavor:

MySQL: leverages the mysql2 gem, which can be installed with the mysql2_chef_gem resource prior to use (available on the Supermarket). You must depend on the mysql2_chef_gem cookbook, then use a mysql2_chef_gem resource to install it. The resource allows the user to select MySQL client library versions, as well as optionally select MariaDB libraries.
PostgreSQL: leverages the pg gem which is installed as part of the postgresql::ruby recipe. You must declare include_recipe "database::postgresql" to include this.
SQL Server: leverages the tiny_tds gem which is installed as part of the sql_server::client recipe.
SQLite: leverages the sqlite3 gem which is installed as part of the database::sqlite recipe. You must declare include_recipe "database::sqlite" to include this.

database

Manage databases in a RDBMS. Use the proper shortcut resource depending on your RDBMS: mysql_database, postgresql_database, sql_server_database or sqlite_database.

Actions

:create: create a named database
:drop: drop a named database
:query: execute an arbitrary query against a named database

Attribute Parameters

database_name: name attribute. Name of the database to interact with
connection: hash of connection info. valid keys include :host, :port, :username, and :password
- only for MySQL DB*: :flags (see Mysql2::Client@@default_query_options[:connect_flags])
- only for PostgreSQL: :database (overwrites parameter database_name)
- not used for SQLlite
sql: string of sql or a block that executes to a string of sql, which will be executed against the database. used by :query action only

* The database cookbook uses the mysql2 gem.

"The value of host may be either a host name or an IP address. If host is NULL or the string "127.0.0.1", a connection to the local host is assumed. For Windows, the client connects using a shared-memory connection, if the server has shared-memory connections enabled. Otherwise, TCP/IP is used. For a host value of "." on Windows, the client connects using a named pipe, if the server has named-pipe connections enabled. If named-pipe connections are not enabled, an error occurs."

If you specify a :socket key and are using the mysql_service resource to set up the MySQL service, you'll need to specify the path in the form /var/run/mysql-<instance name>/mysqld.sock.

Providers

Chef::Provider::Database::Mysql: shortcut resource mysql_database
Chef::Provider::Database::Postgresql: shortcut resource postgresql_database
Chef::Provider::Database::SqlServer: shortcut resource sql_server_database
Chef::Provider::Database::Sqlite: shortcut resource sqlite_database

Examples

# Create a mysql database
mysql_database 'wordpress-cust01' do
  connection(
    :host     => '127.0.0.1',
    :username => 'root',
    :password => node['wordpress-cust01']['mysql']['initial_root_password']
  )
  action :create
end

# Create a mysql database on a named mysql instance
mysql_database 'oracle_rools' do
  connection(
    :host     => '127.0.0.1',
    :username => 'root',
    :socket   => "/var/run/mysql-#{instance-name}/mysqld.sock"
    :password => node['mysql']['server_root_password']
  )
  action :create
end

# Create a sql server database
sql_server_database 'mr_softie' do
  connection(
    :host     => '127.0.0.1',
    :port     => node['sql_server']['port'],
    :username => 'sa',
    :password => node['sql_server']['server_sa_password'],
    :options  => { 'ANSI_NULLS' => 'ON', 'QUOTED_IDENTIFIER' => 'OFF' }
  )
  action :create
end

# create a postgresql database
postgresql_database 'mr_softie' do
  connection(
    :host      => '127.0.0.1',
    :port      => 5432,
    :username  => 'postgres',
    :password  => node['postgresql']['password']['postgres']
  )
  action :create
end

# create a postgresql database with additional parameters
postgresql_database 'mr_softie' do
  connection(
    :host     => '127.0.0.1',
    :port     => 5432,
    :username => 'postgres',
    :password => node['postgresql']['password']['postgres']
  )
  template 'DEFAULT'
  encoding 'DEFAULT'
  tablespace 'DEFAULT'
  connection_limit '-1'
  owner 'postgres'
  action :create
end

# Externalize conection info in a ruby hash
mysql_connection_info = {
  :host     => '127.0.0.1',
  :username => 'root',
  :password => node['mysql']['server_root_password']
}

sql_server_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['sql_server']['port'],
  :username => 'sa',
  :password => node['sql_server']['server_sa_password']
}

postgresql_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['postgresql']['config']['port'],
  :username => 'postgres',
  :password => node['postgresql']['password']['postgres']
}

# Same create commands, connection info as an external hash
mysql_database 'foo' do
  connection mysql_connection_info
  action :create
end

sql_server_database 'foo' do
  connection sql_server_connection_info
  action     :create
end

postgresql_database 'foo' do
  connection postgresql_connection_info
  action     :create
end

# Create database, set provider in resource parameter
database 'bar' do
  connection mysql_connection_info
  provider   Chef::Provider::Database::Mysql
  action     :create
end

database 'bar' do
  connection sql_server_connection_info
  provider   Chef::Provider::Database::SqlServer
  action     :create
end

database 'bar' do
  connection postgresql_connection_info
  provider   Chef::Provider::Database::Postgresql
  action     :create
end



# Drop a database
mysql_database 'baz' do
  connection mysql_connection_info
  action    :drop
end



# Query a database
mysql_database 'flush the privileges' do
  connection mysql_connection_info
  sql        'flush privileges'
  action     :query
end


# Query a database from a sql script on disk
mysql_database 'run script' do
  connection mysql_connection_info
  sql { ::File.open('/path/to/sql_script.sql').read }
  action :query
end



# Vacuum a postgres database
postgresql_database 'vacuum databases' do
  connection      postgresql_connection_info
  database_name 'template1'
  sql 'VACUUM FULL VERBOSE ANALYZE'
  action :query
end

# Create, Insert, Query a SQLite database
# Note that inserting anything in to the database will create it automaticly.
sqlite_database 'mr_softie' do
  database_name '/path/to/database.db3'
  sql "sql command"
  action :query
end

# Delete the database, will remove the file
sqlite_database 'mr_softie' do
  database_name '/path/to/database.db3'
  action :drop
end

database_user

Manage users and user privileges in a RDBMS. Use the proper shortcut resource depending on your RDBMS: mysql_database_user, postgresql_database_user, or sql_server_database_user.

Actions

:create: create a user
:drop: drop a user
:grant: manipulate user privileges on database objects

Attribute Parameters

username: name attribute. Name of the database user
password: password for the user account
database_name: Name of the database to interact with
connection: hash of connection info. valid keys include :host, :port, :username, :password
privileges: array of database privileges to grant user. used by the :grant action. default is :all
host: host where user connections are allowed from. used by MySQL provider only. default is '127.0.0.1'
table: table to grant privileges on. used by :grant action and MySQL provider only. default is '*' (all tables)
require_ssl: true or false to force SSL connections to be used for user

Providers

Chef::Provider::Database::MysqlUser: shortcut resource mysql_database_user
Chef::Provider::Database::PostgresqlUser: shortcut resource postgresql_database_user
Chef::Provider::Database::SqlServerUser: shortcut resourcesql_server_database_user

Examples

# create connection info as an external ruby hash
mysql_connection_info = {
  :host     => '127.0.0.1',
  :username => 'root',
  :password => node['mysql']['server_root_password']
}

postgresql_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['postgresql']['config']['port'],
  :username => 'postgres',
  :password => node['postgresql']['password']['postgres']
}

sql_server_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['sql_server']['port'],
  :username => 'sa',
  :password => node['sql_server']['server_sa_password']
}

# Create a mysql user but grant no privileges
mysql_database_user 'disenfranchised' do
  connection mysql_connection_info
  password   'super_secret'
  action     :create
end

# Do the same but pass the provider to the database resource
database_user 'disenfranchised' do
  connection mysql_connection_info
  password   'super_secret'
  provider   Chef::Provider::Database::MysqlUser
  action     :create
end

# Create a postgresql user but grant no privileges
postgresql_database_user 'disenfranchised' do
  connection postgresql_connection_info
  password   'super_secret'
  action     :create
end

# Do the same but pass the provider to the database resource
database_user 'disenfranchised' do
  connection postgresql_connection_info
  password   'super_secret'
  provider   Chef::Provider::Database::PostgresqlUser
  action     :create
end

# Create a sql server user but grant no privileges
sql_server_database_user 'disenfranchised' do
  connection sql_server_connection_info
  password   'super_secret'
  action     :create
end

# Drop a mysql user
mysql_database_user 'foo_user' do
  connection mysql_connection_info
  action     :drop
end

# Bulk drop sql server users
%w(disenfranchised foo_user).each do |user|
  sql_server_database_user user do
    connection sql_server_connection_info
    action     :drop
  end
end

# Grant SELECT, UPDATE, and INSERT privileges to all tables in foo db from all hosts
mysql_database_user 'foo_user' do
  connection    mysql_connection_info
  password      'super_secret'
  database_name 'foo'
  host          '%'
  privileges    [:select,:update,:insert]
  action        :grant
end

# The same as above but utilizing hased password string instead of
# plain text one
mysql_database_user 'foo_user' do
  connection    mysql_connection_info
  password      mysql_hashed_password('*664E8D709A6EBADFC68361EBE82CF77F10211E52')
  database_name 'foo'
  host          '%'
  privileges    [:select,:update,:insert]
  action        :grant
end

# Grant all privileges on all databases/tables from 127.0.0.1
mysql_database_user 'super_user' do
  connection mysql_connection_info
  password   'super_secret'
  action     :grant
end

# Grant all privileges on all tables in foo db
postgresql_database_user 'foo_user' do
  connection    postgresql_connection_info
  database_name 'foo'
  privileges    [:all]
  action        :grant
end

# grant select,update,insert privileges to all tables in foo db
sql_server_database_user 'foo_user' do
  connection    sql_server_connection_info
  password      'super_secret'
  database_name 'foo'
  privileges    [:select,:update,:insert]
  action        :grant
end

License & Authors

Author: Cookbook Engineering Team (cookbooks@chef.io)

Copyright: 2009-2015, Chef Software, Inc.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

Dependent cookbooks

postgresql >= 1.0.0

Contingent cookbooks

Database cookbook CHANGELOG

This file is used to list changes made in each version of the database cookbook.

v5.1.2 (2016-04-21)

Fix for Unknown column 'mysql_native_password' error
Fix 'already initialized constant' warnings while testing

v5.1.1 (2016-04-07)

Fixed password resource not functioning on Postgresql
Removed unused templates leftover from the DB -> EBS backup recipes
Add better logging when the providers are used and the gems are not present

v5.1.0 (2016-04-06)

Added a new resource for Sqlite DBs. See the readme for details
Updated :create and :grant action in the mysql_database_user provider to update the user password if it changes
Added the ability to pass options to the sql_server connection
Added a mysql_hashed_password method to the mysql_database_user's password property to allow passing hashed passwords to the resource. See the readme for an example

v5.0.1 (2016-03-29)

Resolve error in the user provider under MySQL due to a Rubocop fix

v5.0.0 (2016-03-23)

Removed Chef 10 compatibility checks
Resolved the following error in the sql_server_user provider: undefined local variable or method sql_role
Added a timeout attribute to the database provider on sql_server, which defaults to 120 seconds
Fixed the :revoke action for the database_user provider on mysql
Removed duplicate documentation from the readme
Updated the mysql_user provider to compare passwords so passwords can be updated
Updated documentation for the connection hash in the database provider
Removed duplicate amazon supports property from the metadata
Removed documentation for ebs recipes that are no longer part of this cookbook
Updated testing deps and removed the large number of Guard deps
Fixed intgration tests to be in the correct location so they run as expected
Added .foodcritic file to ignore FC023
Removed the cloud testing Kitchen file and Gem dependencies
Added integration testing with Kitchen Docker in Travis CI

v4.0.9 (2015-09-07)

Fix bad attribute name with postgresql_database in the readme
Add flags attribute to the mysql provider
Add database attribute to the mysql provider
Use the correct database with the mssql provider
Updated testing.md and contributing.md to point to documentation in the new community_cookbook_documentation repo
Add oracle as a supported platform in the metadata
Add source_url and issues_url metadata
Add cookbook version and travis badges to the readme
Clarify the minimum required Chef version in the readme
Add a Travis config
Resolve several rubocop and foodcritic warnings
Update all platforms in the Kitchen config
Update development dependencies to the latest releases
Add a maintainers.md and maintainers.toml file
Add a chefignore file
Update list of ignored files in the gitignore

v4.0.8 (2015-08-03)

139 - Use a more reliable method of determining whether the
Postgres server accepts the REPLICATION attribute on user creation.

v4.0.7 (2015-07-27)

161 - Fixes multiple issues causing the MySQL database user creation to not be idempotent

v4.0.6 (2015-04-29)

126 - Use sql_query property instead of sql in the mysql provider for :query action

v4.0.5 (2015-04-08)

137/#138 - Removing log message containing password information

v4.0.4 (2015-04-07)

Using unescaped db name in field value

v4.0.3 (2015-02-22)

Unbreak postgresql_database_resource on older versions of PostgreSQL

v4.0.2 (2015-02-09)

Removing leftover mysql recipe that installs the mysql2_chef_gem.

v4.0.1 (2015-02-05)

Fixing merge conflicts with master on 4.0.0 attempted release

v4.0.0 (2015-02-05)

Decoupled mysql2_chef_gem cookbook.
Users must now install it themselves before utilizing mysql_database
or mysql_database_user resources.
Fixing various MilClass errors in mysql providers
Restoring missing :query action for mysql
Restoring grant_option support for mysql
Adding revoke action for mysql

v3.1.0 (2015-01-30)

Add support for postgresql_database_user privileges
Add postgresql_database_test cookbook to test/fixtures

v3.0.3 (2015-01-20)

Bugfix: bugfix: lack of node['mysql']['version'] causing NilClass error

v3.0.2 (2015-01-16)

Fix bug to allow grants on databases with special characters

v3.0.1 (2015-01-16)

Enabling ssl for provider_mysql_database_user

v3.0.0 (2015-01-15)

Removing out of scope recipes
porting to mysql2_chef_gem
adding test-kitchen suites for mysql

v2.3.1 (2014-12-13)

Locking mysql and mysql-chef_gem dependencies down in metadata.rb

v2.3.0 (2014-08-13)

[#62] Allow requiring SSL

v2.2.0 (2014-05-07)

[COOK-4626] Add windows users for SQL Server
[COOK-4627] Assigning sys_roles in SQL Server

v2.1.10 (2014-05-07)

[COOK-4614] - Update README to reflect gem installation via mysql-chef_gem

v2.1.8 (2014-04-23)

[COOK-4583] - Add ChefSpec matchers

v2.1.6 (2014-04-10)

[COOK-4538] Bump supported Chef version

v2.1.4 (2014-04-09)

[COOK-4529] Query action ignores MySQL errors

v2.1.2 (2014-04-01)

Depending on mysql-chef_gem cookbook

v2.1.0 (2014-03-31)

Updating mysql cookbook dependency
Enforcing rubocops

v2.0.0 (2014-02-25)

[COOK-3441] database_user password argument should not be required

v1.6.0

New Feature

COOK-4009 - Add PostgreSQL SCHEMA management capability

Improvement

COOK-3862 - Improve database cookbook documentation

v1.5.2

Improvement

COOK-3716 - Add ALTER SQL Server user roles

v1.5.0

Improvement

COOK-3546 - Add connection parameters :socket
COOK-1709 - Add 'grant_option' parameter

v1.4.0

Bug

[COOK-2074]: Regex in exists? check in sql_server_database resource should match for start and end of line
[COOK-2561]: mysql_database_user can't set global grants

Improvement

[COOK-2075]: Support the collation attribute in the database_sql_server provider

v1.3.12

[COOK-850] - postgresql_database_user doesn't have example

v1.3.10

[COOK-2117] - undefined variable grant_statement in mysql user provider

v1.3.8

[COOK-1896] - Escape command
[COOK-2047] - Chef::Provider::Database::MysqlUser action :grant improperly quotes username@host string
[COOK-2060] - Mysql::Error: Table '.' doesn't exist when privileges include SELECT and database/table attributes are nil
[COOK-2062] - Remove backticks from database name when using wildcard

v1.3.6

[COOK-1688] - fix typo in readme and add amazon linux to supported platforms

v1.3.4

[COOK-1561] - depend on mysql 1.3.0+ explicitly
depend on postgresql 1.0.0 explicitly

v1.3.2

Update the version for release (oops)

v1.3.0

[COOK-932] - Add mysql recipe to conveniently include mysql::ruby
[COOK-1228] - database resource should be able to execute scripts on disk
[COOK-1291] - make the snapshot retention policy less confusing
[COOK-1401] - Allow to specify the collation of new databases
[COOK-1534] - Add postgresql recipe to conveniently include postgresql::ruby

v1.2.0

[COOK-970] - workaround for disk [re]naming on ubuntu 11.04+
[COOK-1085] - check RUBY_VERSION and act accordingly for role
[COOK-749] - localhost should be a string in snapshot recipe

v1.1.4

[COOK-1062] - Databases: Postgres exists should close connection

v1.1.2

[COOK-975] - Change arg='DEFAULT' to arg=nil, :default => 'DEFAULT'
[COOK-964] - Add parentheses around connection hash in example

v1.1.0

[COOK-716] - providers for PostgreSQL

v1.0.0

[COOK-683] - added database and database_user resources
[COOK-684] - MySQL providers
[COOK-685] - SQL Server providers
refactored - database::master and database::snapshot recipes to leverage new resources

v0.99.1

Use Chef 0.10's node.chef_environment instead of node['app_environment'].

Foodcritic Metric

5.1.2 failed this metric

FC023: Prefer conditional attributes: /tmp/cook/dd2d9ce2b35eafa30cccfafc/database/libraries/provider_database_mysql.rb:46
FC023: Prefer conditional attributes: /tmp/cook/dd2d9ce2b35eafa30cccfafc/database/libraries/provider_database_mysql.rb:77
FC023: Prefer conditional attributes: /tmp/cook/dd2d9ce2b35eafa30cccfafc/database/libraries/provider_database_mysql_user.rb:48
FC023: Prefer conditional attributes: /tmp/cook/dd2d9ce2b35eafa30cccfafc/database/libraries/provider_database_mysql_user.rb:80
FC023: Prefer conditional attributes: /tmp/cook/dd2d9ce2b35eafa30cccfafc/database/libraries/provider_database_mysql_user.rb:181

Adoptable Cookbooks List

Supermarket Belongs to the Community

View our collection of guides, documentation, and articles

Select Badges

Select Supported Platforms

The database cookbook has been deprecated

Database Cookbook

Requirements

Platforms

Chef

Cookbooks

Resources/Providers

database

Actions

Attribute Parameters

Providers

Examples

database_user

Actions

Attribute Parameters

Providers

Examples

License & Authors

Dependent cookbooks

Contingent cookbooks

Database cookbook CHANGELOG

v5.1.2 (2016-04-21)

v5.1.1 (2016-04-07)

v5.1.0 (2016-04-06)

v5.0.1 (2016-03-29)

v5.0.0 (2016-03-23)

v4.0.9 (2015-09-07)

v4.0.8 (2015-08-03)

v4.0.7 (2015-07-27)

v4.0.6 (2015-04-29)

v4.0.5 (2015-04-08)

v4.0.4 (2015-04-07)

v4.0.3 (2015-02-22)

v4.0.2 (2015-02-09)

v4.0.1 (2015-02-05)

v4.0.0 (2015-02-05)

v3.1.0 (2015-01-30)

v3.0.3 (2015-01-20)

v3.0.2 (2015-01-16)

v3.0.1 (2015-01-16)

v3.0.0 (2015-01-15)

v2.3.1 (2014-12-13)

v2.3.0 (2014-08-13)

v2.2.0 (2014-05-07)

v2.1.10 (2014-05-07)

v2.1.8 (2014-04-23)

v2.1.6 (2014-04-10)

v2.1.4 (2014-04-09)

v2.1.2 (2014-04-01)

v2.1.0 (2014-03-31)

v2.0.0 (2014-02-25)

v1.6.0

New Feature

Improvement

v1.5.2

Improvement

v1.5.0

Improvement

v1.4.0

Bug

Improvement

v1.3.12

v1.3.10

v1.3.8

v1.3.6

v1.3.4

v1.3.2

v1.3.0

v1.2.0

v1.1.4

v1.1.2

v1.1.0

v1.0.0

v0.99.1

Details