CyberKeeda In Social Media

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

    No comments:

    Post a Comment

    Related Posts Plugin for WordPress, Blogger...
    Designed By Kunal Saha