gistfile1.clj

(defn postgresql-script
  "Execute a postgresql script.
Options for how this script should be run:
   :as-user username       - Run this script having sudoed to this (system) user. Default: postgres"
  [request sql-script & {:keys [as-user]
			 :as options
			 :or {as-user "postgres"}}]
  (-> request
      (exec-script/exec-checked-script
       "PostgreSQL temp command file"
       (var psql_commands (file/make-temp-file "postgresql")))
      (remote-file/remote-file
       (stevedore/script @psql_commands)
       :no-versioning true
       :literal true
       :content sql-script)
      ;; Don't check result, as some postgres commands are meant to fail.
      ;; (Ex: Create db on a db that already exists, it does nothing, so fail is fine).
      (exec-script/exec-script
       ("{\n" sudo "-u" ~as-user psql "-f" @psql_commands "\n}"))
      (remote-file/remote-file
       (stevedore/script @psql_commands)
       :action :delete)))

(defn create-database
  "Create a database if it does not exist.

   You can specify database parameters by including a keyed parameter called
   :db-parameters, which indicates a vector of strings or keywords that will get
   translated in order to the options to the create database command. Passes on
   key/value arguments it does not understand to postgresql-script.

   Example: (create-database \"my-database\" :db-parameters [:encoding \"'LATIN1'\"])"
  [request db-name & rest]
  (let [{:keys [db-parameters] :as options} rest
	db-parameters-str (str/join " " (map as-str db-parameters))]
    (apply postgresql-script
	   request
	   (format "CREATE DATABASE %s %s;" db-name db-parameters-str)
	   rest)))

(defn create-role
  "Create a postgres role.

   You can specify user parameters by including a keyed parameter called
   :user-parameters, which indicates a vector of strings or keywords that will get
   translated in order to the options to the create user command. Passes on key/value
   arguments to postgresql-script.

   Example (create-role \"myuser\" :user-parameters [:encrypted :password \"'mypasswd'\"])"
  [request username & rest]
  (let [{:keys [user-parameters] :as options} rest
	user-parameters-str (str/join " " (map as-str user-parameters))]
    (apply postgresql-script
	   request
	   (format "CREATE ROLE \"%s\" %s;" username user-parameters-str)
	   rest)))

;;;;;;;;;;;;;;;

(defn postgres
  "PostgreSQL server configuration."
  [request]
  (-> request
      (postgres/postgres pg-version)
      (postgres/hba-conf :records [[:local :all :postgres :ident]])
      (postgres/postgresql-conf
       :options {:listen_address ["10.0.1.14", "localhost"]
		 :data_directory (format "/var/lib/postgresql/%s/main" pg-version)
		 :hba_file (format "/etc/postgresql/%s/main/pg_hba.conf" pg-version)
		 :ident_file (format "/etc/postgresql/%s/main/pg_ident.conf" pg-version)
		 :ssl "true"
		 :shared_buffers "24MB"})
      (postgres/create-role "davidsantiago-web"
			    :user-parameters [:login :inherit :nosuperuser :nocreatedb :nocreaterole
					      :encrypted :password "'myL33tP4ssw0rd'"])
      (postgres/create-database "davidsantiago"
				:db-parameters [:owner "\"davidsantiago-web\""])))