Ubuntu: Enable Disable stop start of the service at boot

Following commands can be used for enabling, dsabling, starting and stoping og a service. postgresql service is used in case:

Stop a service for the current session:

    systemctl stop postgresql

Start a service for the session:

    systemctl start postgresql

Check the status of service:

    systemctl status postgresql
 

Disable a service and it won't auto-start at boot

    systemctl disable postgresql
 

Enable service postgresql. This will make it auto-start
 

    systemctl enable postgresql
 

Deploying Python web database app on Heroku

I wanted to make sure that next time when I deploy something on Heroku for free, I should not forget anything. I will be using ubuntu and Heroku CLI mostly.

Following files are required by Heroku in the root directory:

1. requirements.txt
2. Procfile
3. runtime.txt
   

Description and contents of these files are given at the end. Kindly check their details before starting if you are unfamiliar.

Here are the steps for deployment.

1. Create account on Heroku and download toolbelt from:

https://blog.heroku.com/the_heroku_toolbelt 

Login to Heroku using the command:

heroku login

2. Create the app by using:

heroku create awesome-app-name

Replace awesome-app-name with your app name. If the name is already taken then use another name. Now create database on heroku by using following link from the same blog:

Create and push PostgresSQL database to Heroku

3. Get the database name and URL by using the command:

heroku config --app awesome-app-name

4. Put the URL in your config file or where are have provided the database URL.

5. Get going with the git. Create a .gitignore file in the root directory. 

    Initialize the git repository by command:

•     git init

    Add all to git by command:

•     git add .

    Make a commit using the command:

•     git commit -m 'Initial commit'

Now push everything to heroku:

    Check if we are connected to heroku app by command:

    heroku info

    If it return no app specified then run:

    heroku git:reomte --app app-name

    app-name should be replaced by the name of out app on heroku.

    If it warns - did you mean git:remote then press y and enter.

    Now push the thing to heroku by using the command.

    git push heroku master

Now sit back and wait for it to push and install all the requirements on heroku.

requirements.txt has all the packages that are used in the project. This file can be created and populated by the following command.

pip freeze > requirements.txt

 

Procfile tells Heroku how to run the app. Here are the contents of a simple Procfile.

web: gunicorn run:app

It means there is a run.py file which is running the app. If Procfile has 

gunicorn then it has to be stated in the requirements.txt. If unsure about 

gunicorn version, then simply install gunicorn by:

pip install gunicorn 

After installation you can see the version e.g. gunicorn-20.0.4 and add it 

requirements.txt as gunicorn==20.0.4 or generate requirements file 

again. 

 

runtime.txt states the python version we are going to use. Please remember to check the current supported version on Heroku by visiting:

https://devcenter.heroku.com/articles/python-support#supported-runtimes

Create Database on Heroku and Push local on it

Heroku account, toolbelt and an app is required to follow this tutorial. 

Lets login to Heroku by using the command:

heroku login

Databases comes as add-ons in Heroku. Therefore, we have to create an add-on by using the following command. 

heroku addons:create heroku-postgresql:hobby-dev --app app-name

Replace the app-name at the end with your app name. The database is now created. This database is free and the package is hobby-dev.

Now if we have a local database which we want to push to the newly created database on heroku. 

Copy the name of the newly created heroku database which is shown in the messages that were shown after database create command. Or use

heroku pg:info

Name will be something like: postgresql-tetrahedral-60032

Use the below command to push your database:

heroku pg:push postgresql://root:root@localhost/height_data postgresql-tetrahedral-60032 --app app-name

In this command postgresql://root:root@localhost/height_data is my database on the local machine with the name height_data. So change it by your database.

app-name is the name of your app.

If you are trying to push to an old heroku database which is not empty so you need to reset the database first.

Here are the details from heroku website

https://devcenter.heroku.com/articles/heroku-postgresql#provisioning-the-add-on

Delete Git History

# Create - Check out to a temporary branch:
git checkout --orphan TEMP_BRANCH

# Add all the files:
git add -A

# Commit the changes:
git commit -am "History Flushing"

# Delete the old branch:
git branch -D master

# Rename the temporary branch to master:
git branch -m master

# Finally, force update to our repository:
git push -f origin master


Image / File storage / upload Best practice

I read couple of blogs and came across these guidelines:

Firstly where to store the image:

1. Amazon S3. The uploaded image can simply uploaded to Amazon and the URL stored in the DB.
2. Save the files to the disk or disk server or cloud

Flaws in storing in a Database:

1. Losing space on the database server.
2. Slows down the database server by: 
1. Transmitting the entire picture in it’s query response to the webserver.
2. Webserver sends this to the requesting browser. 
3. Large Bandwidth usage
3. If the user is uploading the images to the server will effect hosting price.
4. Database is not designed to work as file storage.

Where not to store:

1. Not within the website structure where they can be accessed by a URL.
2. Not in a username folder.
3. Do not store all the image in one folder. Create different folders and limit the number of images that can be stored in a folder

Please read the below link to check how many files can be saved in a directory. Consider the fact that its an old answer and may differ from todays facts
https://stackoverflow.com/questions/466521/how-many-files-can-i-put-in-a-directory
 

Precautions while storing image:

1. Avoid user provided image names.
2. Resize the images.
3. Open and check the files contents to be an image. Do not rely on the file extensions and mime type

Guideline while uploading images:
These are taken from acunetix.com

    Define an .htaccess file that will only allow access to files with allowed extensions.
    Do not place the .htaccess file in the same directory where the uploaded files will be stored, instead, place it in the parent directory. This way the .htaccess file can never be overwritten by an attacker.
    A typical .htaccess which allows only GIF, JPG, JPEG, and PNG files should include the following (this should be adapted as necessary for specific requirements). The following will also prevent double extension attacks:

    deny from all <
    files ~ “^w+.(gif|jpe?g|png)$”>
    order deny,allow
    allow from all
    </files>

    If possible, upload the files in a directory outside the server root.
    Prevent overwriting of existing files (to prevent the .htaccess overwrite attack).
    Create a whitelist of accepted MIME-types (map extensions from these MIME-types).
    Generate a random file name and add the previously generated extension.
    Don’t rely on client-side validation only, since it is not enough. Ideally, both server-side and client-side validation should be implemented.


For Best Size please read the guidelines here:
https://flothemes.com/flothemes-image-sizes/

Good resources to read:
https://www.acunetix.com/websitesecurity/upload-forms-threat/

Create a user table in the PostgreSQL

Here are some common recommendations and guidelines I came across:

• In PostgreSQL user is a reserved keyword.
• It is usually not a good idea use reserved words for identifiers (tables, columns).
• Also name a table with plural (such as "users") is not recommended.
• Table name refers to one row in the table, and not the total content of the table (we know that the Person table contains all the Persons and one row represents a person).
  

Therefore, we can name the User table as person OR account. Here is the query:

CREATE TABLE person (
    user_id serial PRIMARY KEY,
    name VARCHAR ( 100 ) NOT NULL,
    email VARCHAR ( 150 ) UNIQUE NOT NULL,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 150 ) NOT NULL,
    created TIMESTAMP NOT NULL,
    last_updated TIMESTAMP
);

Its a good practice to give column name as user_id instead of id. Other columns can be different according to requirements / scenario.

PostgreSQL basic commands

PostgreSQL Commands and Queries

We are working in Ubuntu;

• Check if there is PostgreSQL installed by using the command:

service postgresql status

• Check if psql command line for postgreSQL is installed:

psql -V

• Access posegreSQL command-line interface:

sudo su - postgres

• Connect to a database:

once we are in the postgreSQL command line we can connect to a Database using command:

psql DATABASE-NAME USERNAME;

Use database and user names instead of DATABASE-NAME USERNAME. If the username is forgotten try the command without username.

• Show databases command:

There are two ways to show all the databases.

1.

\l

2.

SELECT datname FROM pg_database;

• Switch / Connect to a different database:

\c DATABASE-NAME

Show Tables:

\dt

For more details on table:

\dt+

• Check the table and column details and description:

Three commands can be used in order to see the table description;

1.
\dt TABLE-NAME

2.
\dt+ TABLE-NAME

3.
SELECT
   table_name,
   column_name,
   data_type
FROM
   information_schema.columns
WHERE
   table_name = 'TABLE-NAME';

Change the TABLE-NAME to the name of the table that you want to check details for.

• Get information about which user is connected to the DB now:

\conninfo

 

• Check current user:

SELECT current_user;

 

• Connect / Change the user

\c - user-name

Change the user-name with the user you want to connect.

 above command will change the user connection to the current database. If we want to connect to a different database as a new / different user then use:

\c DATABASE-NAME user-name

Change the user-name and DATABASE-NAME to the desired user and database names.

• Also to set the current session and user:

SET SESSION AUTHORIZATION user-name;

 

• To check current user and session:

SELECT session_user, current_user;