Creation of tables failing when done SHELL Module of Ansible on PostgreSQL

Archives Ansible Developer
1

I wrote the shell script to be run over the PostgreSQL to create tables in a particular database schema. For users, db and schema creation I am using PostgrSQL Ansible modules but for tables creation I am using the shell module which runs a shell script and creates the tables.

For normal user to create this, I have used setting trust in pg_hba.conf using pg_ident.conf file, but this would be a security override for a normal DB user.

Would anybody suggest how can I use password to run this script over the database? P.S.: I even tried using PGPASSWORD, but it is not being picked up to run this shell script which in turn runs SQL file which has calls to multiple different SQL files.

Thanks in advance.

2

Did you try:

  • Providing a pg_hba.conf that is trusting enough to create the schema
  • Replace pg_hba.conf with your default secure version
  • Reload the database to assert the correct rules

I’ve had to do just that manually in the past for other reasons and it worked for me as a quick solution

3

Hi Mike,
Thanks for your reply.
I got the solution for this issue with the environment variables’ usage for now.
Looking at the security constraint, environment variables usage is not allowed. Will have to make some robust way of doing it.

Thanks and Regards,
Tushar.