gsa.datagov-deploy-postgresql only works the first time in sandbox

[adborden]

TASK [gsa.datagov-deploy-postgresql : grant privileges for app role] *****************************************************************************************
fatal: [inventory-web1tf.internal.ci.datagov.us]: FAILED! => {"changed": false, "msg": "permission denied for relation _table_metadata\n"}

The first apply works fine, but subsequent ones fail with this error. This role is only applied to sandbox environments (we'd like them to run on production in order to rotate creds, but don't want to do that work yet).

Work-around: specify --skip-tags database when running in the sandbox to avoid running this role.

How to reproduce

1. ansible-playbook catalog.yml --inventory inventories/ci

Expected behavior

Role applies successfully.

Actual behavior

Role fails.

[adborden]

Stepping back, I think we want to simplify this role to only manage one application database and one app user. Managing the permissions gets complicated. For Inventory, which needs to create a datastore read-only user, this really needs to be handled by the application since paster generates the SQL commands. This fits better with cloud.gov, since the application is going to have to create that database user at startup.

[jbrown-xentity]

Couldn't we design a script that runs inside the app on startup? This would then be run locally, on staging, on cloud.gov, etc while using environment variables so that we ensure this is working as expected? @adborden

[adborden]

Yes! You've hit the nail on the head. What we're struggling with here is the line between platform responsibility vs application responsibility. The application is responsible for creating the schema and running migrations while the platform should be responsible for creating a database, creating a user, setting the password, and exposing those details as configuration (environment variables) to the application.

We ran into trouble here when we said the read-only datastore user should be created by the platform. But from a technical standpoint, postgresql doesn't support that very well since the read-only-ness is set using table-level permissions and really only CKAN knows about the schemas in order to do that (that's why they have a paster command to generate SQL).

So, yes, what should happen is this role should:

• create the database
• create a "privileged" role (that can be used by CKAN to create the datastore read-only user)
• set the password
• enable extensions like postgis.

Then, as you say, the startup script should run any migrations and set permissions on the datastore user. This should work the same in all environments.

We're already doing this on Dashboard. CodeIgniter has this built in at request time (I would prefer startup time, but whatever).

One catch on BSP, since CKAN is run via apache2, there's no good place to execute the startup script. This is why we need to separate CKAN into its own process (a startup script that ends with executing a wsgi program).

[nickumia-reisys]

There are still issue with db initialization in local tests (for certain ckan extensions). But the issue of db initialization for local catalog testing and all cloud.gov spaces is resolved. There are a lot of references to list for this, but as a start,

• Local Testing: Feature/py3 catalog.data.gov#328
• Cloud.gov Setup:
  • Add db inits for extension and gunicorn server catalog.data.gov#242
  • Feature/harvest running catalog.data.gov#369