How to Create a New User and Grant Database Ownership in PostgreSQL on Ubuntu.

Some learners may have wondered, “Is there a simple way to create a new PostgreSQL user on Ubuntu?” In this tutorial, we’ll explore the step-by-step process of creating a new user and granting it database ownership.

Honestly, this isn’t only for ubuntu. The steps after accessing “psql” should be the same.

Let’s get started!

Create New User and Password

Step 1: Open psql in terminal by running the following command.

sudo -u postgres psql

You’ll be asked to enter your ubuntu password

Note that postgres” is the default user in PostgreSQL. Therefore, we use “postgres” to access the psql. After entering your ubuntu password, You will gain access to the PostgreSQL command line interface.

Run the following command to view the list of users.

\du

//or

\du+

Step 2: Create user and password by running this command “CREATE USER <username> WITH PASSWORD <’your password’>;”. See the following example.

CREATE USER peter WITH PASSWORD 'peterpeter';

If You have successfully created the user and password, psql will return this

and when You check the list of users, your new user should be included.

Grant All Privileges or Grant Database Ownership

Grant All Privileges

If You want to grant a user all privileges, You can simply run this commnd “GRANT ALL PRIVILEGES ON DATABASE <database name> TO <username>;”. See the following example.

GRANT ALL PRIVILEGES ON DATABASE dbtest TO peter;

If You have successfully granted the user for all privileges, psql will return this.

Grant Database Ownership

If You want to grant a user to be a database owner, You can simply run this command “ALTER DATABASE <db_name> OWNER TO <username>;”. See the following example.

ALTER DATABASE dbtest OWNER TO peter;

Now the user is an owner of the database.

Run the command below to view the list of databases and check if the owner has been changed from the default user “postgres” to the user You have created.

\l

Conclusion

Now You’ve successfully navigated through the steps to create a new user and grant database ownership in PostgreSQL on Ubuntu. With this knowledge under your belt, You’re well equipped to manage PostgreSQL users and ownership effectively.

I don’t claim this to be the only approach or the only way. But it’s a reliable one to empower your PostgreSQL management.

Happy coding!