Shell script for creating database instances

[MikeTheCanuck]

Next step is to refactor lines ~61-100 into a separate script that can be used to create database instances one at a time.

Acceptance criteria

Given I need to create a new database on our monolithic EC2-deployed PostgreSQL server AND Given I have the database instance name, database user name and password ready AND Given I have admin-level permissions to the database AND Given I have SSH access to the database server When I run this script from an SSH terminal on the EC2 machine Then the database is successfully created with the name, database user and password AND Then the database user has Owner rights to the database.

Given I have the database instance name, database user name and password pre-determined AND Given I can run this shell script interactively via sudo [script_name.sh] When I feed instance/user/password as command line parameters to the shell script (e.g. [script_name.sh] database_instance_name database_user_name database_user_password) Then the database is successfully created with the name, database user and password.

[znmeb]

1. Does the new user get a Linux account too?
2. Does the new PostgreSQL role get a "login" privilege to the database, or just "connect?"

i.e., are we talking about a human user / administrator or a robot / process / API accessing the service from outside the server?

[MikeTheCanuck]

We haven’t heard any critical requirements for OS privileges - I’m working from a position of least privilege to get the core needs met, and adjust as necessary.

Why would the team(s) need OS logins?

[znmeb]

Not so much OS logins as the ability to connect with psql or pgAdmin. If the server is otherwise behind a firewall they'd have to ssh in and use psql on the server.

I do nearly everything with pgAdmin - the only time I use psql is when I have to \copy a CSV file into a table.

You'll have scripts to restore pg_dump files from S3, right? That might be all we'll need unless something breaks and we have to go look around. I'm planning to make a local development environment that looks at the Linux command line and PostgreSQL server levels exactly like what you have up on AWS, via the magic of https://hub.docker.com/_/amazonlinux/

[MikeTheCanuck]

Ah thanks for the clarification. Yes, according to my testing the Dev database server at least will allow (and intentionally so) Pgadmin, without any OS creds underlying.

[znmeb]

Sounds like a plan! By the way, I have the Docker image running in a private repo on Bitbucket. I'm waiting to see if anyone needs it before I pollute the Hack Oregon space with yet another repository.

[MikeTheCanuck]

OK - we're not expecting to use Docker containers for our data layer in AWS, but please continue to work with the developers if they're finding value in them for local development.

[khashf]

Working on the create-db.sh script on branch create-db-instance Currently the script is able to:

• support interactive & non-interactive input.
• confirmably (by \l command) create the desired new database and user after running

Known bug:

• The newly created user cannot log back in with psql -d <dbname> -U <username> -W

Some symptoms:

• could not change directory to "/home/ec2-user": Permission denied whenever I issue the psql command
• authentication failed: **psql: FATAL: Peer authentication failed for user "khuong2"
• .pgpass is nowhere to be seen in ~/ directory

Note:

• This project is my first time riding Slonik the elephant of PostgreSQL

Exact output:

[ec2-user@ip-172-31-18-237 ~]$ ./create-db.sh 
NOTE: the database naming convention is (without bracket): <the project name>-<data purpose>
Database name: test4
Username: khuong2
Creating DB test4 user khuong2 - prompts for password...
could not change directory to "/home/ec2-user": Permission denied
Enter password for new role: 
Enter it again: 
could not change directory to "/home/ec2-user": Permission denied
[ec2-user@ip-172-31-18-237 ~]$ psql -d test4 -U khuong2 -W
Password for user khuong2: 
psql: FATAL:  Peer authentication failed for user "khuong2"
[ec2-user@ip-172-31-18-237 ~]$ sudo -u postgres psql -U postgres
could not change directory to "/home/ec2-user": Permission denied
psql (9.6.6)
Type "help" for help.

postgres=# \l
                                                List of databases
          Name           |         Owner          | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------------------+------------------------+----------+-------------+-------------+-----------------------
 disaster-resilience-    | disaster-resilience    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 housing-affordability-  | housing-affordability  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 local-elections-        | local-elections        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres                | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0               | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |                        |          |             |             | postgres=CTc/postgres
 template1               | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |                        |          |             |             | postgres=CTc/postgres
 test                    | khuong                 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test2                   | khuong                 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test3                   | khuong                 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test4                   | khuong2                | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 transportation-systems- | transportation-systems | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 urban-development-      | urban-development      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(12 rows)

[MikeTheCanuck]

Quite a ride, it's my first professional-like ride as well!

For that psql authentication issue, the "could not change directory" issue is probably not related (it's been bugging me everywhere), the "FATAL" error is interesting and the .pgpass is not something we will use in this cloud-hosted server environment.

Let me try out this script and see if I'm running into the same situation.

[MikeTheCanuck]

I've tried the script on a test server and it appears to work without issue:

• I ssh into the server with ec2-user
• I run the create-db.sh script with no command-line parameters, so that everything prompts interactively
• I see no FATAL error or "Peer authentication failed..."

[MikeTheCanuck]

I believe this is ready to close once we have merged a PR from that branch referenced in #37 .