Creating a new user and db on postgres

Here are the steps to create a new user and database on PostgreSQL:

Step 1: Connect to the PostgreSQL server

You can use the psql command-line tool to connect to the PostgreSQL server. Replace your_username and your_password with your actual PostgreSQL username and password:

psql -U your_username -W your_password

Step 2: Create a new database

Once connected, you can create a new database using the following command:

CREATE DATABASE mynewdb;

Replace mynewdb with the name you want to give your new database.

Step 3: Create a new user

To create a new user, use the following command:

CREATE ROLE mynewuser WITH PASSWORD 'mypassword';

Replace mynewuser with the name you want to give your new user, and mypassword with the password you want to set for the user.

Step 4: Grant privileges to the new user

To grant privileges to the new user, use the following command:

GRANT ALL PRIVILEGES ON DATABASE mynewdb TO mynewuser;

This grants the new user all privileges on the new database.

Step 5: Verify the new user and database

To verify that the new user and database have been created, you can use the following commands:

\l

This will list all the databases on the server, including the new one.

\du

This will list all the users on the server, including the new one.

Example output:

Here's an example of what the output might look like:

psql (13.4)
Type "help" for help.

mydatabase=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mynewdb   | mynewuser| UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

mydatabase=# \du
                                  List of roles
   Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 mynewuser |                                                            | {}
 postgres  | Superuser, Create role, Create DB                            | {}

In this example, the new database mynewdb is owned by the new user mynewuser, and the new user has been granted all privileges on the database.

NewsBreakers

Creating a new user and db on postgres