CyberKeeda: SQL RDBMS

How to Configure PostgreSQL user and Database

Content.

In this blog post, we will cover the below content.

Creating PostgreSQL users
Creating PostgreSQL databases
Adding an existing user to a database
Deleting PostgreSQL User
Deleting PostgreSQL Database.

By default PostgresSQL installation always includes the postgres superuser ( Linux Shell User) and postgresql database user too. Initially you have to use the same postgres user to coonnect to PostgreSQL until you create other users.

A PostgreSQL users are also known as role.

Follow the steps to create a PostgreSQL user.

Use the passwd copmmand ( passwd postgres )to change the postgres linux user's password.
Just after PostgreSQL db instllation, change the password for default postgres database superuser'spassword.


#    su - postgres

# psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

Now we are all set to create new database user, switch again to postgres super user


#    su - postgres

#    createuser muser --pwprompt

Above crateuser will prompt you few questions.

Enter password for new role: prompt, type a password for the user.
Enter it again: prompt, retype the password.
Shall the new role be a superuser? prompt, type y if you want to grant superuser access. Otherwise, type n.
Shall the new role be allowed to create databases? prompt, type y if you want to allow the user to create new databases. Otherwise, type n.
Shall the new role be allowed to create more new roles? prompt, type y if you want to allow the user to create new users. Otherwise, type n.

Follow the steps to create a PostgreSQL Database.

Open a new linux terminal from root and run the commands one by one.


#    su - postgres

One can now run commands as the PostgreSQL superuser.

To create a database, type the following command. Replace user with the name of the user that you want to own the database, and replace dbname with the name of the database that you want to create.

PostgreSQL users that have permission to create databases can do so from their own accounts by typing the following command, replacing dbname is the name of the database to create.


#    createdb -O myuser dbname

#    createdb dbname

Follow the steps to add a PostgreSQL db user to existing Database.

Run the psql command program as the database's owner, or as the postgres superuser.
I will be using postgres user here


#    GRANT permissions ON DATABASE dbname TO username;

Replace above dbname and username as per your requirement.

Follow the steps to delete a PostgreSQL user.

Important point to consider before deleting a db user.

If the user owns any databases or other objects, you cannot drop the user. Instead, you receive an error message " dropuser: removal of role "username" failed: ERROR: role "username" cannot be dropped because some objects depend on it "

DETAIL: owner of database dbname "

You should change the database's owner (or drop the database entirely), and then you can drop the user, then run the below command


#    dropuser username

Follow the steps to delete a PostgreSQL Database


#    dropdb dbname

Method 1

Install from CentOS repository.
For the time being CentOS 7 repositories ship with PostgreSQL version 9.2.15.
To install from the CentOS repositories, simply run:


#  sudo yum install postgresql-server postgresql-contrib

Now lets Initialize your Postgres database and start PostgreSQL and configure it to start on Boot


#  sudo postgresql-setup initdb 

#  sudo systemctl start postgresql

#  sudo systemctl enable postgresql

Method 2.

Install the latest stable version of PostgreSQL from Postgres Repository

Official repository link : Here

Note : Currently official repositiry provides packages based on RPM distribution ( Redhat/CentOS/Fedora...)

Select the version you wish to install from the Postgres Yum repositories.
Locate the CentOS 7 link for your chosen version and download it to your Centos 7 host.
For the time being, I'm selecting Postgresql 9.6.3, choose as per your requirement.


#  wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Install the downloaded postgres rpm package and install centos epel too.


#  rpm -ivh pgdg-centos96-9.6-3.noarch.rpm

#  yum install epel-release

Now we are almost all set to install PostgreSQL.

Update Yum to apply your changes and install PostgreSQL.
When installing Postgres manually, you will have to specify the version, we will be installaing postgresql-9.6



#  sudo yum update

#  sudo yum install postgresql96-server postgresql96-contrib

Important Point to notice.

When Postgres is installed using above method 2, the version number is included in its configuration directories. For example, /var/lib/pgsql becomes /var/lib/pgsql/9.6. 
This is also the case with systemd units
systemctl status postgresql becomes systemctl status postgresql-9.6

Now lets Initialize your Postgres database and start PostgreSQL and configure it to start on Boot.


#  sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

#  sudo systemctl start postgresql-9.6

#  sudo systemctl enable postgresql-9.6

And we are done..

CyberKeeda