Postgresql 10: Create database and user

Postgresql provides creation of database schemas with a specific role or user assigned to it. This is great where there will be a few users who need to access one or another database on the same server.

Default user created by Postgresql is named postgres, that can be used to create any database schema and user roles. For this tutorial, Postgresql is installed on Centos Linux 7. To start postgresql client and access the default user, at the terminal type;

sudo -u postgres psql

Steps to create the database are as follows;

1. Create the database. In this case we call it student_management.
2. Create user and assign password for login. We call out user student and password is 123456
3. Assign the access to the database. We give full privileges to read, write.
4. Enable the user to login.
5. Exit from client (optional)

CREATE DATABASE student_management;
CREATE ROLE student WITH PASSWORD '123456';
GRANT ALL PRIVILEGES ON DATABASE student_management TO student;
ALTER ROLE student WITH LOGIN;

Next, test by login with that user account and password.

There is an alternative approach for a dedicated database server that require users to have their own databases. This approach doesn't require you to use the psql client. It is useful when you plan to have scripts that auto generate the process.

sudo -u postgres createdb developer
sudo -u postgres createuser --interactive
sudo adduser developer
sudo passwd developer

The user "developer" can login and get connected to the default database with the same name as the user. In this case the database name is "developer".

Login as the user.

sudo -i -u developer psql

Laravel: Getting started

Notes on getting started with Laravel, on Centos Linux 6,7,8.

In order to get started with Laravel, there are required software to run composer and laravel that needs to be installed. This includes;

1. Web server
2. PHP
3. Composer
4. Node JS

Step 1. Install PHP 7.3 or newer.

If you havent installed PHP, follow notes from previous post.
After installing PHP, install all the regular extensions. Ensure PHP is working with the web browser.

sudo yum install php-cli php-zip wget unzip  php-dom php-posix php-shmop  php-xsl php-xmlrender

Step 2. Web server.

In this case, I am using Apache HTTPD. Install this and test that the php is working.

sudo yum install httpd

Step 3. Composer

Download the installer.

php -r "copy('https://getcomposer.org/installer', 'composer-setup.php' ");
sudo php composer-setup.php --install-dir=/usr/local/bin --filename=composer

Step 4. Node JS

sudo yum install nodejs

You are ready to work with Laravel projects. Here are links to the remainder tutorials
Part 1, Part 2,

Install Postgresql 10 database on Centos 7

Installation and configuration of Postgresql 10 on Centos 7

Installation

Centos Linux 7 by default installs Postgresql version 9. These are summary of steps in order to install Postgresql version 10, the following steps can be taken.

1. Yum manages the installation of software, and utilises a list of repositories of there to locate its repository. It is highly advised to update existing software with yum before proceeding with software installations. Add the additional repository site must be added for Postgresql 10 from postgresql.org
2. Install the version of Postgresql database client software that is needed to open the database.
3. Install the version of Postgresql database server software. This will hold the database files and controlling software.
4. Initialise Postgresql database files. This will create the username "postgres" where it is configured to run all the client commands.
5. Start Postgresql database server.
6. Test the server by requesting info.
7. Configure Centos 7 to automatically start at boot up.

At a terminal, execute the commands

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install postgresql10
# yum install postgresql10-server
# /usr/pgsql-10/bin/postgresql-10-setup initdb
# systemctl start postgresql-10
# /usr/pgsql-10/bin/postgres -V
# systemctl enable postgresql-10

Using the client

For those who wish to use the postgres clients, here are commands to get started.

Login as user "postgres" and use the Postgresql client.

# sudo -u postgres psql

Once in psql, here are several commands to browse the database;

Display server version (the Uppercase is a matter of good practice)
SELECT version();

List database schema
\l

Switch database schema
\c database_name

Describe a table
\d table_name

Get the last command
\g

Show history of commands
\s

Help with a command
\h command_name

Exit Postgresql client
\q

Remote access and firewall

In development databases, many users may require to access the database from remote computers. On the default Centos 7, with firewall running this require some configuration. The steps involved;

1. Add httpd and postgresql service rule to the firewall. The httpd is an additional web service I am demonstrating here.
2. Restart firewall
3. Configure postgresql to listen from incoming networks, or all networks. Replace the line listen_addresses = 'localhost'
4. Allow user authentication from incoming networks, or all networks. Replace the line with 127.0.0.1/32
5. Restart postgresql

At a terminal, execute the commands

# sudo firewall-cmd --add-port=8080/tcp --permanent

# sudo firewall-cmd --add-service=postgresql --permanent

# sudo firewall-cmd --reload

# sudo vi /var/lib/pgsql/10/data/postgresql.conf

listen_addresses = '*'

# sudo vi /var/lib/pgsql/10/data/pg_hba.conf

host    all             all             0.0.0.0/0            md5

# sudo systemctl restart postgresql-10

Laravel 6 Step by Step Tutorial Part 2

Laravel 6 provides an advanced framework for programmers to quickly create enterprise on a wide number of PHP platforms. Part 2 of this Laravel guide can be found at Laravel 6 Part 2

In order to proceed with part 2 of the tutorial, please complete part 1 as posted in
First of Laravel 6 step by step guide.

The tutorial can be followed by having your copy of PHP 7.3 onwards and a database. Notes are provided for MySQL and Postgresql database.

This demonstrates how to implement data relationships of has one. In Laravel this is implemented with the function belongsTo. The idea is to maintain a list of articles where each have one author. That author could essentially have written many articles.