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.

TLDR

From the terminal:

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

Using SQL:

CREATE DATABASE dbname;
CREATE USER username WITH PASSWORD 'mypass';
ALTER USER username WITH LOGIN;
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.