Creating a PostgreSQL Database, User, and Assigning Ownership

PostgreSQL is an open-source relational database management system known for its robustness and extensibility. In this guide, we will walk you through the step-by-step process of creating a database in PostgreSQL, creating a user, and granting ownership of the database to that user. Whether you’re a beginner or experienced with databases, this tutorial will help you get started with PostgreSQL.


From the terminal:

su - postgres
createuser -P username
createdb -O username dbname

Using SQL:

CREATE USER username WITH PASSWORD 'mypass';
ALTER DATABASE dbname OWNER TO username;;

Creating a Database

To create a new database, you’ll use the CREATE DATABASE SQL command. Replace 'yourdatabase' with your preferred database name:

CREATE DATABASE yourdatabase;

Creating a User

Users in PostgreSQL are separate from operating system users. To create a new user, you can use the CREATE USER command:

CREATE USER youruser WITH PASSWORD 'yourpassword';

Replace 'youruser' with the desired username and 'yourpassword' with a strong password.

  1. Granting Privileges: Now, let’s assign privileges to the newly created user. In PostgreSQL, users can have various roles like superuser, createdb, and login. To grant a user privileges, you can use the ALTER USER command:

    • Granting login privileges:

      ALTER USER youruser WITH LOGIN;
    • Granting database creation privileges:

      ALTER USER youruser CREATEDB;
  2. Assigning Ownership: To make the newly created user the owner of the database, you’ll use the ALTER DATABASE command:

    ALTER DATABASE yourdatabase OWNER TO youruser;

    This will ensure that the user has full control over the database.

  3. Verifying Ownership: To verify that the ownership has been successfully assigned, you can query the pg_database system catalog table:

    SELECT datname, datowner FROM pg_database WHERE datname = 'yourdatabase';

    This query will display the database name and its associated owner.

To contact me, send an email anytime or leave a comment below.