creating DB/User for Postgresql

[Barry Morrison]

Browsing documentation and GitHub for other's states.  I haven't found much in regards to creating the actual DB's or users for postgresql with Salt. 

Am I missing something perhaps?  Should I not use Salt to do this? 

Just looking for guidance/best practices. 

Thanks!

[Alec Koumjian]

This is the only reference I know of: http://saltstarters.org/example/brutasse/states

[Barry Morrison]

Yes, I've found that, but unless I'm missing it, he isn't actually creating a DB or a user in/for postgresql. 

[Alec Koumjian]

Ah, yes. You appear to be correct.

[Ben Hosmer]

I haven't done it for Postgresql, but there are built-in states for it:

http://docs.saltstack.org/en/latest/ref/states/all/salt.states.postgres_database.html#module-salt.states.postgres_database

http://docs.saltstack.org/en/latest/ref/states/all/salt.states.postgres_user.html#module-salt.states.postgres_user

--
Ben Hosmer

Brevard Tech - Brevard County Florida's Tech User Group

http://www.brevardtech.org

Drupal Developer

http://www.radarearth.com

321.252.9581

[Barry Morrison]

Yes, I found those as well. 

Here is what I'm tinkering with:

tc2012:
    postgres_user.present
    - password: foo


tc2012DB:
    postgres_database.present
    - encoding: UTF-8
    - owner: tc2012
    - require:
        - tc2012

Thoughts?

Thanks!

[Ben Hosmer]

Yeah, looks good.

Remind me, how is the root user initially set in postgres?

The only reason I ask, is because I ran into some issues using the mysql modules.

[Barry Morrison]

The previously mentioned continually failed:

[WARNING ] Duplicate Key: "libpq-dev" found in /var/cache/salt/files/base/reqs/init.sls
local:
    Data failed to compile:
----------
    Rendering SLS postgresql failed, render error:
mapping values are not allowed here
  in "<unicode string>", line 10, column 14:
        - require:
                 ^

[WARNING ] Duplicate Key: "libpq-dev" found in /var/cache/salt/files/base/reqs/init.sls
local:
    Data failed to compile:
----------
    Rendering SLS postgresql failed, render error:
mapping values are not allowed here
  in "<unicode string>", line 9, column 12:
        - owner: tc2012
               ^

[WARNING ] Duplicate Key: "libpq-dev" found in /var/cache/salt/files/base/reqs/init.sls
local:
    Data failed to compile:
----------
    Rendering SLS postgresql failed, render error:
mapping values are not allowed here
  in "<unicode string>", line 8, column 15:
        - encoding: UTF-8
                  ^

[WARNING ] Duplicate Key: "libpq-dev" found in /var/cache/salt/files/base/reqs/init.sls
local:
    Data failed to compile:
----------
    Rendering SLS postgresql failed, render error:
while scanning for the next token
found character '\t' that cannot start any token
  in "<unicode string>", line 3, column 5:
            - password='foo'
        ^


So here is the current state of affairs and it's errors:

tc2012:
    postgres_user.present
        - password='foo'


tc2012DB:
    postgres_database.present
    - encoding='UTF-8'
    - owner='tc2012'

[WARNING ] Duplicate Key: "libpq-dev" found in /var/cache/salt/files/base/reqs/init.sls
local:
    Data failed to compile:
----------
    The function "present - password='foo'" in state "tc2012" in SLS "postgresql" has whitespace, a function with whitespace is not supported, perhaps this is an argument that is missing a ":"
----------
    The function "present - encoding='UTF-8' - owner='tc2012'" in state "tc2012DB" in SLS "postgresql" has whitespace, a function with whitespace is not supported, perhaps this is an argument that is missing a ":"

[Barry Morrison]

Further data:

tc2012:
    postgres_user.present:
        - password: foo

tc2012DB:
    postgres_database.present:
    - encoding: UTF-8
    - owner: tc2012

Latest state.highstate:

----------
    State: - postgres_database
    Name:      tc2012DB
    Function:  present
        Result:    False
        Comment:   An exception occured in this state: Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.7/salt/state.py", line 823, in call
    ret = self.states[cdata['full']](*cdata['args'])
  File "/usr/lib/pymodules/python2.7/salt/states/postgres_database.py", line 61, in present
    if __salt__['postgres.db_exists'](name, runas=runas):
KeyError: 'postgres.db_exists'

        Changes:  
----------
    State: - postgres_user
    Name:      tc2012
    Function:  present
        Result:    False
        Comment:   An exception occured in this state: Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.7/salt/state.py", line 823, in call
    ret = self.states[cdata['full']](*cdata['args'])
  File "/usr/lib/pymodules/python2.7/salt/states/postgres_user.py", line 50, in present
    if __salt__['postgres.user_exists'](name, runas=runas):
KeyError: 'postgres.user_exists'

[Barry Morrison]

It appears with salt-call state.highstate --log-level=debug, this will run against the local host/minion. 

When I ran salt salt-minion state.highstate, I received this error:

    State: - postgres_database
    Name:      tc2012DB
    Function:  present
        Result:    False
        Comment:   An exception occured in this state: Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.7/salt/state.py", line 823, in call
    ret = self.states[cdata['full']](*cdata['args'])
  File "/usr/lib/pymodules/python2.7/salt/states/postgres_database.py", line 61, in present
    if __salt__['postgres.db_exists'](name, runas=runas):

  File "/usr/lib/pymodules/python2.7/salt/modules/postgres.py", line 127, in db_exists
    databases = db_list(user=user, host=host, port=port, runas=runas)
  File "/usr/lib/pymodules/python2.7/salt/modules/postgres.py", line 108, in db_list
    header = [x.strip() for x in lines[0].split("|")]
IndexError: list index out of range

        Changes:  
----------
    State: - postgres_user
    Name:      tc2012
    Function:  present
        Result:    False
        Comment:   An exception occured in this state: Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.7/salt/state.py", line 823, in call
    ret = self.states[cdata['full']](*cdata['args'])
  File "/usr/lib/pymodules/python2.7/salt/states/postgres_user.py", line 50, in present
    if __salt__['postgres.user_exists'](name, runas=runas):

  File "/usr/lib/pymodules/python2.7/salt/modules/postgres.py", line 267, in user_exists
    users = user_list(user=user, host=host, port=port, runas=runas)
  File "/usr/lib/pymodules/python2.7/salt/modules/postgres.py", line 249, in user_list
    header = [x.strip() for x in lines[0].split("|")]
IndexError: list index out of range

        Changes:

[Ben Hosmer]

Maybe:

tc2012:
    postgres_user.present
        - password: foo

tc2012DB:
    postgres_database.present
    - encoding: UTF-8
    - owner: tc2012

[Barry Morrison]

Here is more info:

root@ubuntu:/srv/salt# salt salt-minion postgres.user_list
{'salt-minion': 'Traceback (most recent call last):\n  File "/usr/lib/pymodules/python2.7/salt/minion.py", line 281, in _thread_return\n    ret[\'return\'] = func(*args, **kw)\n  File "/usr/lib/pymodules/python2.7/salt/modules/postgres.py", line 249, in user_list\n    header = [x.strip() for x in lines[0].split("|")]\nIndexError: list index out of range\n'}


root@ubuntu:/srv/salt# salt salt-minion postgres.db_list
{'salt-minion': 'Traceback (most recent call last):\n  File "/usr/lib/pymodules/python2.7/salt/minion.py", line 281, in _thread_return\n    ret[\'return\'] = func(*args, **kw)\n  File "/usr/lib/pymodules/python2.7/salt/modules/postgres.py", line 108, in db_list\n    header = [x.strip() for x in lines[0].split("|")]\nIndexError: list index out of range\n'}


I think I may have found it:

From here: http://salt.readthedocs.org/en/latest/ref/modules/all/salt.modules.postgres.html#module-salt.modules.postgres

In order to connect to Postgres, certain configuration is required in /etc/salt/minion on the relevant minions. Some sample configs might look like:

postgres.host: 'localhost'
postgres.port: '5432'
postgres.user: 'postgres'
postgres.pass: ''
postgres.db: 'postgres'

This data can also be passed into pillar. Options passed into opts will overwrite options passed into pillar

Need to dig a little further into initiating/bootstrapping Postgresql I guess...See how to create/manage the postgres user.

[Barry Morrison]

Even after adding postgres settings to /etc/salt/minion this is the error message I get:

root@ubuntu:~# salt salt-minion postgres.db_list

{'salt-minion': 'Traceback (most recent call last):\n  File "/usr/lib/pymodules/python2.7/salt/minion.py", line 281, in _thread_return\n    ret[\'return\'] = func(*args, **kw)\n  File "/usr/lib/pymodules/python2.7/salt/modules/postgres.py", line 108, in db_list\n    header = [x.strip() for x in lines[0].split("|")]\nIndexError: list index out of range\n'}

[Barry Morrison]

More digging this morning. 

By default Postgres sets auth like so:

host    all             all             127.0.0.1/32            md5

md5 == requires password

_psql_cmd does not require password: 

cmd = ['psql', '--no-align', '--no-readline', '--no-password']

If I run psql --no-align --no-readline --no-password --username postgres --host localhost --port 5432 -l

It returns:  psql: fe_sendauth: no password supplied

so in /etc/postgres/9.1/main/pg_hba.conf

change auth to: host    all             all             127.0.0.1/32            trust

trust does not require a password

and now, it works!

root@ubuntu:/srv/salt# salt salt-minion postgres.db_list

{'salt-minion': [[['Name', 'postgres'],
                  ['Owner', 'postgres'],
                  ['Encoding', 'UTF8'],
                  ['Collate', 'en_US.UTF-8'],
                  ['Ctype', 'en_US.UTF-8']],
                 [['Name', 'template0'],
                  ['Owner', 'postgres'],
                  ['Encoding', 'UTF8'],
                  ['Collate', 'en_US.UTF-8'],
                  ['Ctype', 'en_US.UTF-8']],
                 [['Name', 'template1'],
                  ['Owner', 'postgres'],
                  ['Encoding', 'UTF8'],
                  ['Collate', 'en_US.UTF-8'],
                  ['Ctype', 'en_US.UTF-8']]]}


Hope this helps others. 

[Ben Hosmer]

Barry,

This is awesome! Would you consider sharing your state here: https://github.com/saltstack/salt-states

 in the /small

This would be a valuable addition to the ever-growing example states.

[Alec Koumjian]

And here! 

http://saltstarters.org

[Barry Morrison]

I'm not opposed to putting it anywhere, here is what exists https://github.com/esacteksab/salt-states  I'm not certain if it is up to the standards of other's work...but I think most of it works.  There are a few pieces that exist when I was starting out that may still be lingering. 

Any advice, feedback would be greatly appreciated!

[Bruce Wang]

Just for a reference, I did managed to create a postgres user and a db without changing default pg_hba.conf

Here is the state file https://gist.github.com/3792011

I think the only difference is using 'runas: postgres'

HTH

Cheers,

Bruce 

On Thu, Sep 27, 2012 at 12:14 PM, Barry Morrison <bdmor...@gmail.com> wrote:

I'm not opposed to putting it anywhere, here is what exists https://github.com/esacteksab/salt-states  I'm not certain if it is up to the standards of other's work...but I think most of it works.  There are a few pieces that exist when I was starting out that may still be lingering. 

Any advice, feedback would be greatly appreciated!

--
simple is good
http://brucewang.net
http://twitter.com/number5

[Barry Morrison]

Doh!  That makes perfect sense! 

Thank you! I like this approach a lot better.