Tuesday, 13 June 2023

Connecting to remote PostgreSQL 14 instance

 psql 'postgres://<username>:<password>@<host>:<port>/<databasename>?sslmode=disable'

Example of the above command with example username, password and database name:

psql 'postgres://myUsername1:databasePassword1@192.168.10.60:5436/database1?sslmode=disable'


Wednesday, 25 November 2020

Create PostgreSQL Database and User in Ubuntu

 Enter the psql command line by hitting

sudo -u postgres psql

 

Create the database by using the command. (don't forget to put your database name in place of: yourdbname )

CREATE DATABASE yourdbname;


Create a user with an encrypted password by using the command: (change youruser to your username and yourpass to your password. Password comes in the single comas)

CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';


 Grant all privileges to your new user on your new database. (again provide your username and database name)

GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

Friday, 23 October 2020

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
 
 
 
 

Wednesday, 14 October 2020

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

Monday, 12 October 2020

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

Tuesday, 6 October 2020

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/


Sunday, 4 October 2020

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;


Wednesday, 30 September 2020

Convert IPython notebook file .ipynb to python script .py on Ubuntu

Try to use the first commands, if its not working then use the second one:
  1. jupyter nbconvert --to script stock_analysis.ipynb 
  2. jupyter nbconvert --to python stock_analysis.ipynb

If you are getting an error as:

AttributeError: module 'tornado.web' has no attribute 'asynchronous'

 Try this

pip3 install --upgrade nbconvert 

After the above command has ran successfully try the previous command listed as 1 or 2.

 

Tuesday, 12 February 2019

Python: Fastest way to retrieve element from dictionary

Some helpful reading about the dictionary from python docs here.

Here is something from the stackoverflow.

Here is a program that checks and retrieves the values for keys in a dictionary:

data = {'one': 1, 'two': 2, 'three': 3}
 
def translate(word):
    # The below line gets the value from 
    # the dictionary if the key is present 
    # in the dictionary. It will return None 
    # if the key is not in the dictionary. 
    # But its slow 
 
    definition = data.get(word, None)

    # The below line is fast and checks if the
    # key is in the dictionary 
 
    if word in data:
        return data[word]
    else:
        return "No definition found for the word"
 
   # This below is an also alternative 
    try:
        return data[word]
    except KeyError:
        return "No definition found for the word"


word = input("Enter a word: ")
print(translate(word=word))

Thursday, 3 January 2019

Django: Change password for postgres database user

Start the shell using the command

python manage.py shell_plus

Print all the users using

users=User.objects.all()
print(users)

Get the specific user for which password has to be changed


user=User.objects.get(email='')
 
in the '' write the email for the user
OR we can get the user by username
 
user=User.objects.get(username='') 

Here username has to be written in the ''

Set the new password for the user
 
user.set_password('1234qwer')

We have changed the password for the user. The new password is 
1234qwer

Wednesday, 19 September 2018

Get first n characters of a string - PHP

....
//The simple version for 10 Characters from the beginning of the string
$string = substr($string,0,10).'...';
Update:
Based on suggestion for checking length (and also ensuring similar lengths on trimmed and untrimmed strings):
$string = (strlen($string) > 13) ? substr($string,0,10).'...' : $string;
So you will get a string of max 13 characters; either 13 (or less) normal characters or 10 characters followed by '...'
Update 2:
Or as function:
function truncate($string, $length, $dots = "...") {
    return (strlen($string) > $length) ? substr($string, 0, $length - strlen($dots)) . $dots : $string;
}
Update 3:
It's been a while since I wrote this answer and I don't actually use this code any more. I prefer this function which prevents breaking the string in the middle of a word using the wordwrap function:
function truncate($string,$length=100,$append="&hellip;") {
  $string = trim($string);

  if(strlen($string) > $length) {
    $string = wordwrap($string, $length);
    $string = explode("\n", $string, 2);
    $string = $string[0] . $append;
  }

  return $string;
}

Saturday, 11 August 2018

Ubuntu: Check Different Python Versions Installed / Available

This is taken from the following link

To see all version of python available, run the following command in the terminal:

$ compgen -c python
 
 
 

Wednesday, 11 July 2018

Change/use different node version on Ubuntu

For Ubuntu NVM (Node Version Manager) works best. Therefore, I will recommend NVM instead of others e.g (n).

Installation and usage is very simple. I will extract the steps from the NVM github repository page.

Run following commands to install nvm:

curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.33.11/install.sh | bash

Enable the nvm command by running:

source ~/.bashrc

To check proper installation run the following command

command -v nvm 

It should output nvm. If correct output is generated then we can install different versions of node using the command:

nvm install v8.11.0

OR

nvm install v8.10.0

Now when a different node version is installed it can be used by using the command:

nvm use 8.10.0

Tuesday, 10 July 2018

Securely Hash Passwords with PHP

PHP 5.5+ now comes baked with a password_hash function to generate secure, one-way hashes along with a password_verify function to match a hash with the given password—If you’re a PHP developer, you should always be securely storing user passwords, no excuses.
Developers have a huge responsibility when handling and storing user-sensitive information, such as a password. We should take extra precaution and the necessary steps to make sure the user’s data is safe and secure*.
*Please keep in mind the following implementation is only part of the problem since it handles the data once the web server receives it; however, it does not address the other issue of securely sending the sensitive data over-the-air from the browser to the server, which is why a valid SSL certificate is necessary.

Hashing passwords

To hash a password, take the password string and pass it into password_hash the function as a parameter along with the algorithm you want to use, then store the returned hash into the database.
password_hash( $password, $algorithm [, $options ] )
  • $password string.
  • $algorithm integer. Supports constants PASSWORD_BCRYPT or PASSWORD_DEFAULT.
  • $options array.
password_hash also randomly generates a salt every time a hash is generated and is a part of the returned hash, so there’s no need to store salts in a separate column.
$algorithm 
PASSWORD_BCRYPT uses the CRYPT_BLOWFISH algorithm and will return a 60 character string.
PASSWORD_DEFAULT uses the bcrypt algorithm. PHP documentation recommends that you set the column size to 255 in the event the algorithm changes over time.
$options 
password_hash supports the following options:
  • salt - You can manually pass in your own salt, although password_hash randomly generates a salt for each password.
  • cost - The algorithmic cost to be used. Default value is 10.
<?php
  $options = array(
    'salt' => mcrypt_create_iv(22, MCRYPT_DEV_URANDOM),
    'cost' => 12,
  );
  $password_hash = password_hash($password_string, PASSWORD_BCRYPT, $options);
?>
Here’s a dirty, incomplete example that shows implementation of password_hash:
<?php
  $password_string = mysqli_real_escape_string($_POST["password"]);
  // The value of $password_hash
  // should similar to the following:
  // $2y$10$aHhnT035EnQGbWAd8PfEROs7PJTHmr6rmzE2SvCQWOygSpGwX2rtW
  $password_hash = password_hash($password_string, PASSWORD_BCRYPT);

  $mysql_query = "INSERT INTO Users (email, password_hash)
                  VALUES ($email_address, $password_hash)";
  mysqli_query($mysql_connection, $mysql_query);
?>

Verifying passwords

When checking passwords, you can use the handy-dandy password_verify function, which checks a password string against a password hash, then returns a boolean.
password_verify( $password, $hash )
  • $password string.
  • $hash string.
<?php
  $password_string = "abc123";
  $password_hash = "$2y$10$aHhnT035EnQGbWAd8PfEROs7PJTHmr6rmzE2SvCQWOygSpGwX2rtW";

  if (password_verify($password_string, $password_hash)) {
    // Correct password
  } else {
    // Incorrect password
  }
?>

PHP 5.3.7+

There’s a very useful library that allows the password_* functions to be used on servers running PHP 5.3.7+: https://github.com/ircmaxell/password_compat
If you’re running an even older version of PHP, it’s time to upgrade—older versions of PHP contains a security issue with BCRYPT (More information).

Password hashing functions

You can get a more thorough, in-depth explanation about the password hashing functions right from PHP’s documentation: http://us2.php.net/manual/en/ref.password.php. There are 2 additional functions that I didn’t cover, password_get_info and password_needs_rehash, that you may find userful.
For me, it always helps to know or better understand what’s going on in the background of these functions.