Most webmasters prefer MySQL for web projects requiring simple relational databases. PostgreSQL is most suitable for large systems requiring complex query executions and high volume data operations.
This guide details how to manage PostgreSQL databases and users from the command line. We’ll be guiding you on how to:
- Create Postgre users
- Delete a Postgre user
- Create a PostgreSQL database
- Delete a PostgreSQL database
- Add existing users to a PostgreSQL database
Follow this quick guide to manage your PostgreSQL databases and users remotely.
Creating PostgreSQL Users
To create a PostgreSQL database, you’d need to create a Postgre user that’ll own the database. By default, a Postgres installation creates a superuser. You’ll have to connect to PostgreSQL as the superuser to create other users.
Follow these steps to get started.
Install PostgreSQL on your local computer. Go to the PostgreSQL download page to install the latest version of the software.
Open PostgreSQL’s command line.
Type the command below to run commands on PostgreSQL as a superuser.
su – postgres
Use this command to create a new user (role):
createuser –interactive –pwprompt
The program will prompt you to enter the name of the role. Type the user’s name and press Enter.
The next two prompts will request you to create and confirm a password for the new role. Type the password, then retype to confirm it.
Now assign privileges to the user in the next three prompts.
- You can grant superuser access to the new role in the first prompt. Type y in the command line to assign the privilege, or type n to withhold this access.
- In the next prompt, type y to permit the new user to create databases or n to deny.
- In the last prompt, type y if you want the new user to create new roles; otherwise, type n to withhold the privilege.
PostgreSQL will create the new users with the credentials you specified.
Deleting a Postgre User
You can delete a user in PostgreSQL by using the dropuser command.
However, you can’t drop a user that owns a database. You could consider changing the database owner or dropping the database before deleting the user.
When you sort this out, use this simple command to delete a Postgre user.
dropuser username
Of course, replace the username with the name of the user you intend to delete. Press Enter to execute the command.
Creating a PostgreSQL Database
Like mentioned earlier, PostgreSQL is suitable for large and complex projects.
It’s a free and open-source database known for its flexibility, versatility, reliability and scalability. These attributes make Postgres compatible with various specialized use cases — making it a “one-size-fits-all” solution for database management.
Postgre supports both relational and non-relational data types. And here’s how to create a Postgres database.
To create one, type the command below as the server’s root user.
su – postgres
Executing the command will enable you to run Postgres commands as a superuser. Now, as a superuser, type this command to create a database.
createdb -O user dbname
Replace “user” and “dbname” with the name of the user you want to own the database and the database’s name you intend to create, respectively.
Only Postgres users with the privilege to create a database can create one. The user can create one from their account by running this command:
createdb dbname
Replace “dbname” with the name of the database you intend to create.
Deleting a PostgreSQL Database
You can delete a Postgres database by running the dropdb command. Of course, you’ll need to have superuser privileges or own the database to be able to delete it.
Use this command to delete a Postgres database:
dropdb dbname
“dbname” should be the name of the database you want to delete.
Adding Existing Users to a PostgreSQL Database
Database owners and superusers can add existing users to a Postgres database, and assign privileges to them.
You can do that by executing this grant command:
GRANT permissions ON DATABASE dbname TO username;
Replace “permissions” with the privilege you intend to grant the user, while “dbname” should be the name of the database you want to add the user to.
Also, replace “username” with the Postgres user’s name you intend to add to the database.
You can learn about all the privileges you grant existing users by visiting Postgres’ main website.
Wrapping It Up
PostgreSQL is abundant with rich features and extensions that make it easier to create highly scalable and easy-to-administer databases. The command line allows you to manage the databases and users remotely from anywhere.
Thankfully, this guide can get you started in an instant.
