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
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';
'youruser' with the desired username and
'yourpassword' with a strong password.
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
Granting login privileges:
ALTER USER youruser WITH LOGIN;
Granting database creation privileges:
ALTER USER youruser CREATEDB;
Assigning Ownership: To make the newly created user the owner of the database, you’ll use the
ALTER DATABASE yourdatabase OWNER TO youruser;
This will ensure that the user has full control over the database.
Verifying Ownership: To verify that the ownership has been successfully assigned, you can query the
pg_databasesystem 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.