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:

1su - postgres
2createuser -P username
3createdb -O username dbname

Using SQL:

1CREATE DATABASE dbname;
2CREATE USER username WITH PASSWORD 'mypass';
3ALTER USER username WITH LOGIN;
4ALTER 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:

1CREATE 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:

1CREATE 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:

     1ALTER USER youruser WITH LOGIN;
     

   • Granting database creation privileges:

     1ALTER USER youruser CREATEDB;
     

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

   1ALTER 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:

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

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