CyberKeeda In Social Media
Showing posts with label PSQL. Show all posts
Showing posts with label PSQL. Show all posts

How to Configure PostgreSQL user and Database


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

    Read more ...

    How to Install PostgreSQL Relational Database on CentOS

    PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
    Official link : Here 

    Let's know how to install PostgreSQL on CentOS 7.
      Note :   Video Tutorial at the bottom of the blog post

    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
    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..

    In case you are looking for the Installation Video Tutorial, below is the stuff.

    Read more ...
    Designed By Jackuna