We are going to install and use most advanced open source database system that is PostgreSQL.
Postgres is a general purpose and object-relational database management system. Let’s proceed
With the installation steps :
STEP 1 : Installing PostgreSQL :
Ubuntu apt’s repository contains Postgres Packages by default. After refreshing local
package index by below command , we use Postgres package along with a -contrib package .
$ sudo apt update $ sudo apt install postgresql postgresql-contrib
STEP 2: Now postgres has been installed. Now we use postgres Roles and
Roles : Postgres Roles are used to handle in authentication and
authorization. Postgres does not distinguish between users and groups
and instead it prefers the more flexible term “role”.
Roles : Postgres Roles are used to handle in authentication and authorization. Postgres does not distinguish between users and groups and instead it prefers the more flexible term “role”.
During installation postgresql creates a user account in the name of ‘postgres’ that get associated with default postgres role. For using postgres we can switch user to the postgres account user.
STEP 3 : Switching onto the postgres user account and accessing the postgres DB is very easy . commands are given below :
$ sudo -i -u postgres # psql Postgres=# \q
Last command will exit you from postgres user control.
STEP 4 : Creating a new Role :
We can create new roles apart from already existed postgres role by using ‘createrole’ command. ‘–interactive’ flag will ask you for the new role name and also ask for making it superuser permission.
$ firstname.lastname@example.org$ createuser –interactive
Enter name of role to add: linuxworld Shall the new role be a superuser? (y/n) y
NOTE: For more details of using flags you may type command ‘man createuser’
STEP 5: Creating new database :
You must create or login as a postgres or superuser role to create a new database
$ email@example.com$ createdb linuxworld
STEP 6: Access postgres prompt with new role
if you don’t have any user similar to postgres role and database, you can create a new linux user of same name for ident based authentication.
$ sudo adduser linuxworld
Now you can login with this new user as below :
$sudo -i -u linuxworld $psql
Now we can connect with different database by the new user linuxworld as below :
$ psql -d postgres
Once logged in, you can get check your current connection information by below command
You are connected to database "linuxworld" as user "linuxworld" via socket in "/var/run/postgresql" at port "5432".
All Done Enjoy 🙂