How to Create a Database and Always Connect to It in PostgreSQL Without Needing Superuser Access

In this guide, we'll walk through the steps to create a database in PostgreSQL as a non-superuser, assign necessary privileges, and ensure that you can always connect to your newly created database without needing to log in as a superuser.

This is useful for developers or users who want to manage their own databases in PostgreSQL without needing administrative permissions for every action.

What We Will Achieve:

1.Create a new database (tortoise-demo).
2.Grant permissions to a non-superuser role (testuser) to create and access the database.
3.Switch to the database (tortoise-demo) seamlessly without needing to switch to the superuser first.
4.Configure PostgreSQL to always connect to tortoise-demo automatically when logging in as testuser.

Step 1: Grant Necessary Permissions to testuser

Before you can create a database as a non-superuser, you need to ensure that your role (in this case, testuser) has the appropriate permissions to create databases.

Granting CREATEDB Privileges

By default, a newly created PostgreSQL role does not have permission to create databases. If you're logged in as a superuser (like postgres), you can grant the necessary permissions to the testuser role.

1.Log in as a superuser (e.g., postgres):

   psql -U postgres

2.Grant CREATEDB Privilege to testuser:

Run the following SQL query to allow testuser to create new databases:

   GRANT CREATEDB TO "testuser";

This will enable testuser to create databases without needing superuser privileges.

3.Exit the superuser session:

   \q

Step 2: Log in as testuser and Create a Database

Now that the testuser role has the CREATEDB privilege, you can log in as testuser and create a new database.

Log in as testuser:

To log in as the testuser role, run the following command:

psql -U "testuser" -d postgres -W

• The -U "testuser" option specifies the user.
• The -d postgres option connects you to the postgres database (a default administrative database).
• The -W option prompts for the password you set for testuser (e.g., 1234567890).

Create the tortoise-demo Database:

Once logged in, create the new database tortoise-demo:

CREATE DATABASE "tortoise-demo";

This command creates a new database called tortoise-demo.

Set Ownership (Optional):

If you want to ensure that testuser has full control over the database, you can assign ownership of the database to testuser:

ALTER DATABASE "tortoise-demo" OWNER TO "testuser";

This step is optional, but it ensures that the testuser role has full administrative control over the tortoise-demo database.

Step 3: Switch to the tortoise-demo Database

After creating the database, you may want to switch to the newly created database (tortoise-demo) and start working with it.

To connect to tortoise-demo, run:

\c "tortoise-demo"

The \c command switches the current session to the tortoise-demo database. From this point, you can execute SQL queries and manage the database.

Step 4: Automate the Connection to tortoise-demo Without Needing to Switch Each Time

Now that you've successfully created and switched to the tortoise-demo database, the next step is to automate this process. Specifically, we want to configure PostgreSQL so that every time you log in as folasayoolayemi, it automatically connects you to the tortoise-demo database without needing to explicitly switch.

Option 1: Set the PGDATABASE Environment Variable

One easy way to ensure that you always connect to the tortoise-demo database is to set the PGDATABASE environment variable. This variable tells PostgreSQL which database to use by default when connecting.

1.Set PGDATABASE for the current session:

You can set the environment variable in your current terminal session like so:

   export PGDATABASE="tortoise-demo"

This will ensure that any subsequent psql commands you run will automatically connect to tortoise-demo by default.

2.Make the change permanent:

To make this change persistent across terminal sessions, add the export command to your shell's configuration file (.bashrc, .zshrc, etc.).

For example, if you're using bash, add the following line to your ~/.bashrc file:

   echo 'export PGDATABASE="tortoise-demo"' >> ~/.bashrc

Then, run:

   source ~/.bashrc

This will ensure that every time you open a new terminal session, PostgreSQL will automatically connect to tortoise-demo without needing to specify the database.

Option 2: Always Specify the Database in the Connection Command

If you prefer not to use the PGDATABASE environment variable, you can always specify the database name in the psql connection command:

psql -U "testuser" -d "tortoise-demo" -W

This way, you directly specify the tortoise-demo database every time you connect, which eliminates the need for any configuration changes.

Key Steps:

1.Grant CREATEDB Privilege: Ensure the testuser role has the necessary privileges to create databases.
2.Create the Database: Log in as testuser and create the tortoise-demo database.
3.Switch to the Database: Use the \c command to switch to tortoise-demo.
4.Automate Database Connection: Set the PGDATABASE environment variable to always connect to tortoise-demo by default or explicitly specify the database name in the psql command.

Conclusion:

By following these steps, you can create and manage your own databases in PostgreSQL as a non-superuser, without requiring superuser privileges every time you need to create a new database. The ability to automatically connect to a specific database will make your workflow more efficient, especially for developers who work with specific projects or applications.

Thanks for reading...
Happy Coding!