github.com/nathanielks/terraform@v0.6.1-0.20170509030759-13e1a62319dc/builtin/providers/postgresql/resource_postgresql_database.go (about) 1 package postgresql 2 3 import ( 4 "bytes" 5 "database/sql" 6 "errors" 7 "fmt" 8 "log" 9 "strings" 10 11 "github.com/hashicorp/errwrap" 12 "github.com/hashicorp/terraform/helper/schema" 13 "github.com/lib/pq" 14 ) 15 16 const ( 17 dbAllowConnsAttr = "allow_connections" 18 dbCTypeAttr = "lc_ctype" 19 dbCollationAttr = "lc_collate" 20 dbConnLimitAttr = "connection_limit" 21 dbEncodingAttr = "encoding" 22 dbIsTemplateAttr = "is_template" 23 dbNameAttr = "name" 24 dbOwnerAttr = "owner" 25 dbTablespaceAttr = "tablespace_name" 26 dbTemplateAttr = "template" 27 ) 28 29 func resourcePostgreSQLDatabase() *schema.Resource { 30 return &schema.Resource{ 31 Create: resourcePostgreSQLDatabaseCreate, 32 Read: resourcePostgreSQLDatabaseRead, 33 Update: resourcePostgreSQLDatabaseUpdate, 34 Delete: resourcePostgreSQLDatabaseDelete, 35 Exists: resourcePostgreSQLDatabaseExists, 36 Importer: &schema.ResourceImporter{ 37 State: schema.ImportStatePassthrough, 38 }, 39 40 Schema: map[string]*schema.Schema{ 41 dbNameAttr: { 42 Type: schema.TypeString, 43 Required: true, 44 Description: "The PostgreSQL database name to connect to", 45 }, 46 dbOwnerAttr: { 47 Type: schema.TypeString, 48 Optional: true, 49 Computed: true, 50 Description: "The ROLE which owns the database", 51 }, 52 dbTemplateAttr: { 53 Type: schema.TypeString, 54 Optional: true, 55 ForceNew: true, 56 Computed: true, 57 Description: "The name of the template from which to create the new database", 58 }, 59 dbEncodingAttr: { 60 Type: schema.TypeString, 61 Optional: true, 62 Computed: true, 63 ForceNew: true, 64 Description: "Character set encoding to use in the new database", 65 }, 66 dbCollationAttr: { 67 Type: schema.TypeString, 68 Optional: true, 69 Computed: true, 70 ForceNew: true, 71 Description: "Collation order (LC_COLLATE) to use in the new database", 72 }, 73 dbCTypeAttr: { 74 Type: schema.TypeString, 75 Optional: true, 76 Computed: true, 77 ForceNew: true, 78 Description: "Character classification (LC_CTYPE) to use in the new database", 79 }, 80 dbTablespaceAttr: { 81 Type: schema.TypeString, 82 Optional: true, 83 Computed: true, 84 Description: "The name of the tablespace that will be associated with the new database", 85 }, 86 dbConnLimitAttr: { 87 Type: schema.TypeInt, 88 Optional: true, 89 Default: -1, 90 Description: "How many concurrent connections can be made to this database", 91 ValidateFunc: validateConnLimit, 92 }, 93 dbAllowConnsAttr: { 94 Type: schema.TypeBool, 95 Optional: true, 96 Default: true, 97 Description: "If false then no one can connect to this database", 98 }, 99 dbIsTemplateAttr: { 100 Type: schema.TypeBool, 101 Optional: true, 102 Computed: true, 103 Description: "If true, then this database can be cloned by any user with CREATEDB privileges", 104 }, 105 }, 106 } 107 } 108 109 func resourcePostgreSQLDatabaseCreate(d *schema.ResourceData, meta interface{}) error { 110 c := meta.(*Client) 111 112 c.catalogLock.Lock() 113 defer c.catalogLock.Unlock() 114 115 conn, err := c.Connect() 116 if err != nil { 117 return errwrap.Wrapf("Error connecting to PostgreSQL: {{err}}", err) 118 } 119 defer conn.Close() 120 121 dbName := d.Get(dbNameAttr).(string) 122 b := bytes.NewBufferString("CREATE DATABASE ") 123 fmt.Fprint(b, pq.QuoteIdentifier(dbName)) 124 125 //needed in order to set the owner of the db if the connection user is not a superuser 126 err = grantRoleMembership(conn, d.Get(dbOwnerAttr).(string), c.username) 127 if err != nil { 128 return errwrap.Wrapf(fmt.Sprintf("Error granting role membership on database %s: {{err}}", dbName), err) 129 } 130 131 // Handle each option individually and stream results into the query 132 // buffer. 133 134 switch v, ok := d.GetOk(dbOwnerAttr); { 135 case ok: 136 fmt.Fprint(b, " OWNER ", pq.QuoteIdentifier(v.(string))) 137 default: 138 // No owner specified in the config, default to using 139 // the connecting username. 140 fmt.Fprint(b, " OWNER ", pq.QuoteIdentifier(c.username)) 141 } 142 143 switch v, ok := d.GetOk(dbTemplateAttr); { 144 case ok: 145 fmt.Fprint(b, " TEMPLATE ", pq.QuoteIdentifier(v.(string))) 146 case v.(string) == "", strings.ToUpper(v.(string)) != "DEFAULT": 147 fmt.Fprint(b, " TEMPLATE template0") 148 } 149 150 switch v, ok := d.GetOk(dbEncodingAttr); { 151 case ok: 152 fmt.Fprint(b, " ENCODING ", pq.QuoteIdentifier(v.(string))) 153 case v.(string) == "", strings.ToUpper(v.(string)) != "DEFAULT": 154 fmt.Fprint(b, ` ENCODING "UTF8"`) 155 } 156 157 switch v, ok := d.GetOk(dbCollationAttr); { 158 case ok: 159 fmt.Fprint(b, " LC_COLLATE ", pq.QuoteIdentifier(v.(string))) 160 case v.(string) == "", strings.ToUpper(v.(string)) != "DEFAULT": 161 fmt.Fprint(b, ` LC_COLLATE "C"`) 162 } 163 164 switch v, ok := d.GetOk(dbCTypeAttr); { 165 case ok: 166 fmt.Fprint(b, " LC_CTYPE ", pq.QuoteIdentifier(v.(string))) 167 case v.(string) == "", strings.ToUpper(v.(string)) != "DEFAULT": 168 fmt.Fprint(b, ` LC_CTYPE "C"`) 169 } 170 171 if v, ok := d.GetOk(dbTablespaceAttr); ok { 172 fmt.Fprint(b, " TABLESPACE ", pq.QuoteIdentifier(v.(string))) 173 } 174 175 { 176 val := d.Get(dbAllowConnsAttr).(bool) 177 fmt.Fprint(b, " ALLOW_CONNECTIONS ", val) 178 } 179 180 { 181 val := d.Get(dbConnLimitAttr).(int) 182 fmt.Fprint(b, " CONNECTION LIMIT ", val) 183 } 184 185 { 186 val := d.Get(dbIsTemplateAttr).(bool) 187 fmt.Fprint(b, " IS_TEMPLATE ", val) 188 } 189 190 query := b.String() 191 _, err = conn.Query(query) 192 if err != nil { 193 return errwrap.Wrapf(fmt.Sprintf("Error creating database %s: {{err}}", dbName), err) 194 } 195 196 d.SetId(dbName) 197 198 return resourcePostgreSQLDatabaseReadImpl(d, meta) 199 } 200 201 func resourcePostgreSQLDatabaseDelete(d *schema.ResourceData, meta interface{}) error { 202 c := meta.(*Client) 203 c.catalogLock.Lock() 204 defer c.catalogLock.Unlock() 205 206 conn, err := c.Connect() 207 if err != nil { 208 return errwrap.Wrapf("Error connecting to PostgreSQL: {{err}}", err) 209 } 210 defer conn.Close() 211 212 dbName := d.Get(dbNameAttr).(string) 213 214 if isTemplate := d.Get(dbIsTemplateAttr).(bool); isTemplate { 215 // Template databases must have this attribute cleared before 216 // they can be dropped. 217 if err := doSetDBIsTemplate(conn, dbName, false); err != nil { 218 return errwrap.Wrapf("Error updating database IS_TEMPLATE during DROP DATABASE: {{err}}", err) 219 } 220 } 221 222 if err := setDBIsTemplate(conn, d); err != nil { 223 return err 224 } 225 226 query := fmt.Sprintf("DROP DATABASE %s", pq.QuoteIdentifier(dbName)) 227 _, err = conn.Query(query) 228 if err != nil { 229 return errwrap.Wrapf("Error dropping database: {{err}}", err) 230 } 231 232 d.SetId("") 233 234 return nil 235 } 236 237 func resourcePostgreSQLDatabaseExists(d *schema.ResourceData, meta interface{}) (bool, error) { 238 c := meta.(*Client) 239 c.catalogLock.RLock() 240 defer c.catalogLock.RUnlock() 241 242 conn, err := c.Connect() 243 if err != nil { 244 return false, err 245 } 246 defer conn.Close() 247 248 var dbName string 249 err = conn.QueryRow("SELECT d.datname from pg_database d WHERE datname=$1", d.Id()).Scan(&dbName) 250 switch { 251 case err == sql.ErrNoRows: 252 return false, nil 253 case err != nil: 254 return false, err 255 } 256 257 return true, nil 258 } 259 260 func resourcePostgreSQLDatabaseRead(d *schema.ResourceData, meta interface{}) error { 261 c := meta.(*Client) 262 c.catalogLock.RLock() 263 defer c.catalogLock.RUnlock() 264 265 return resourcePostgreSQLDatabaseReadImpl(d, meta) 266 } 267 268 func resourcePostgreSQLDatabaseReadImpl(d *schema.ResourceData, meta interface{}) error { 269 c := meta.(*Client) 270 conn, err := c.Connect() 271 if err != nil { 272 return err 273 } 274 defer conn.Close() 275 276 dbId := d.Id() 277 var dbName, ownerName string 278 err = conn.QueryRow("SELECT d.datname, pg_catalog.pg_get_userbyid(d.datdba) from pg_database d WHERE datname=$1", dbId).Scan(&dbName, &ownerName) 279 switch { 280 case err == sql.ErrNoRows: 281 log.Printf("[WARN] PostgreSQL database (%s) not found", dbId) 282 d.SetId("") 283 return nil 284 case err != nil: 285 return errwrap.Wrapf("Error reading database: {{err}}", err) 286 } 287 288 var dbEncoding, dbCollation, dbCType, dbTablespaceName string 289 var dbConnLimit int 290 var dbAllowConns, dbIsTemplate bool 291 err = conn.QueryRow(`SELECT pg_catalog.pg_encoding_to_char(d.encoding), d.datcollate, d.datctype, ts.spcname, d.datconnlimit, d.datallowconn, d.datistemplate FROM pg_catalog.pg_database AS d, pg_catalog.pg_tablespace AS ts WHERE d.datname = $1 AND d.dattablespace = ts.oid`, dbId). 292 Scan( 293 &dbEncoding, &dbCollation, &dbCType, &dbTablespaceName, 294 &dbConnLimit, &dbAllowConns, &dbIsTemplate, 295 ) 296 switch { 297 case err == sql.ErrNoRows: 298 log.Printf("[WARN] PostgreSQL database (%s) not found", dbId) 299 d.SetId("") 300 return nil 301 case err != nil: 302 return errwrap.Wrapf("Error reading database: {{err}}", err) 303 default: 304 d.Set(dbNameAttr, dbName) 305 d.Set(dbOwnerAttr, ownerName) 306 d.Set(dbEncodingAttr, dbEncoding) 307 d.Set(dbCollationAttr, dbCollation) 308 d.Set(dbCTypeAttr, dbCType) 309 d.Set(dbTablespaceAttr, dbTablespaceName) 310 d.Set(dbConnLimitAttr, dbConnLimit) 311 d.Set(dbAllowConnsAttr, dbAllowConns) 312 d.Set(dbIsTemplateAttr, dbIsTemplate) 313 dbTemplate := d.Get(dbTemplateAttr).(string) 314 if dbTemplate == "" { 315 dbTemplate = "template0" 316 } 317 d.Set(dbTemplateAttr, dbTemplate) 318 return nil 319 } 320 } 321 322 func resourcePostgreSQLDatabaseUpdate(d *schema.ResourceData, meta interface{}) error { 323 c := meta.(*Client) 324 c.catalogLock.Lock() 325 defer c.catalogLock.Unlock() 326 327 conn, err := c.Connect() 328 if err != nil { 329 return err 330 } 331 defer conn.Close() 332 333 if err := setDBName(conn, d); err != nil { 334 return err 335 } 336 337 if err := setDBOwner(conn, d); err != nil { 338 return err 339 } 340 341 if err := setDBTablespace(conn, d); err != nil { 342 return err 343 } 344 345 if err := setDBConnLimit(conn, d); err != nil { 346 return err 347 } 348 349 if err := setDBAllowConns(conn, d); err != nil { 350 return err 351 } 352 353 if err := setDBIsTemplate(conn, d); err != nil { 354 return err 355 } 356 357 // Empty values: ALTER DATABASE name RESET configuration_parameter; 358 359 return resourcePostgreSQLDatabaseReadImpl(d, meta) 360 } 361 362 func setDBName(conn *sql.DB, d *schema.ResourceData) error { 363 if !d.HasChange(dbNameAttr) { 364 return nil 365 } 366 367 oraw, nraw := d.GetChange(dbNameAttr) 368 o := oraw.(string) 369 n := nraw.(string) 370 if n == "" { 371 return errors.New("Error setting database name to an empty string") 372 } 373 374 query := fmt.Sprintf("ALTER DATABASE %s RENAME TO %s", pq.QuoteIdentifier(o), pq.QuoteIdentifier(n)) 375 if _, err := conn.Query(query); err != nil { 376 return errwrap.Wrapf("Error updating database name: {{err}}", err) 377 } 378 d.SetId(n) 379 380 return nil 381 } 382 383 func setDBOwner(conn *sql.DB, d *schema.ResourceData) error { 384 if !d.HasChange(dbOwnerAttr) { 385 return nil 386 } 387 388 owner := d.Get(dbOwnerAttr).(string) 389 if owner == "" { 390 return nil 391 } 392 393 dbName := d.Get(dbNameAttr).(string) 394 query := fmt.Sprintf("ALTER DATABASE %s OWNER TO %s", pq.QuoteIdentifier(dbName), pq.QuoteIdentifier(owner)) 395 if _, err := conn.Query(query); err != nil { 396 return errwrap.Wrapf("Error updating database OWNER: {{err}}", err) 397 } 398 399 return nil 400 } 401 402 func setDBTablespace(conn *sql.DB, d *schema.ResourceData) error { 403 if !d.HasChange(dbTablespaceAttr) { 404 return nil 405 } 406 407 tbspName := d.Get(dbTablespaceAttr).(string) 408 dbName := d.Get(dbNameAttr).(string) 409 var query string 410 if tbspName == "" || strings.ToUpper(tbspName) == "DEFAULT" { 411 query = fmt.Sprintf("ALTER DATABASE %s RESET TABLESPACE", pq.QuoteIdentifier(dbName)) 412 } else { 413 query = fmt.Sprintf("ALTER DATABASE %s SET TABLESPACE %s", pq.QuoteIdentifier(dbName), pq.QuoteIdentifier(tbspName)) 414 } 415 416 if _, err := conn.Query(query); err != nil { 417 return errwrap.Wrapf("Error updating database TABLESPACE: {{err}}", err) 418 } 419 420 return nil 421 } 422 423 func setDBConnLimit(conn *sql.DB, d *schema.ResourceData) error { 424 if !d.HasChange(dbConnLimitAttr) { 425 return nil 426 } 427 428 connLimit := d.Get(dbConnLimitAttr).(int) 429 dbName := d.Get(dbNameAttr).(string) 430 query := fmt.Sprintf("ALTER DATABASE %s CONNECTION LIMIT = %d", pq.QuoteIdentifier(dbName), connLimit) 431 if _, err := conn.Query(query); err != nil { 432 return errwrap.Wrapf("Error updating database CONNECTION LIMIT: {{err}}", err) 433 } 434 435 return nil 436 } 437 438 func setDBAllowConns(conn *sql.DB, d *schema.ResourceData) error { 439 if !d.HasChange(dbAllowConnsAttr) { 440 return nil 441 } 442 443 allowConns := d.Get(dbAllowConnsAttr).(bool) 444 dbName := d.Get(dbNameAttr).(string) 445 query := fmt.Sprintf("ALTER DATABASE %s ALLOW_CONNECTIONS %t", pq.QuoteIdentifier(dbName), allowConns) 446 if _, err := conn.Query(query); err != nil { 447 return errwrap.Wrapf("Error updating database ALLOW_CONNECTIONS: {{err}}", err) 448 } 449 450 return nil 451 } 452 453 func setDBIsTemplate(conn *sql.DB, d *schema.ResourceData) error { 454 if !d.HasChange(dbIsTemplateAttr) { 455 return nil 456 } 457 458 if err := doSetDBIsTemplate(conn, d.Get(dbNameAttr).(string), d.Get(dbIsTemplateAttr).(bool)); err != nil { 459 return errwrap.Wrapf("Error updating database IS_TEMPLATE: {{err}}", err) 460 } 461 462 return nil 463 } 464 465 func doSetDBIsTemplate(conn *sql.DB, dbName string, isTemplate bool) error { 466 query := fmt.Sprintf("ALTER DATABASE %s IS_TEMPLATE %t", pq.QuoteIdentifier(dbName), isTemplate) 467 if _, err := conn.Query(query); err != nil { 468 return errwrap.Wrapf("Error updating database IS_TEMPLATE: {{err}}", err) 469 } 470 471 return nil 472 } 473 474 func grantRoleMembership(conn *sql.DB, dbOwner string, connUsername string) error { 475 if dbOwner != "" && dbOwner != connUsername { 476 query := fmt.Sprintf("GRANT %s TO %s", pq.QuoteIdentifier(dbOwner), pq.QuoteIdentifier(connUsername)) 477 _, err := conn.Query(query) 478 if err != nil { 479 // is already member or role 480 if strings.Contains(err.Error(), "duplicate key value violates unique constraint") { 481 return nil 482 } 483 return errwrap.Wrapf("Error granting membership: {{err}}", err) 484 } 485 } 486 return nil 487 }