postgres new user

Heya,

To create a new superuser, use the CREATE USER command along with SUPERUSER and LOGIN options, and set the password for the new user. Replace new_username with the desired username and your_password with the password you want for this new user:

CREATE USER new_username WITH SUPERUSER LOGIN PASSWORD 'your_password';

Grant privileges to the new user:

This step isn’t always necessary because the SUPERUSER role already provides full privileges, but you can explicitly grant all privileges to the new user on a specific database if needed:

GRANT ALL PRIVILEGES ON DATABASE your_database TO new_username;

Exit PostgreSQL:

Once the user has been created, exit the PostgreSQL prompt. Now you can try logging in with the new superuser account by running the following command:

psql -U new_username -d your_database -W

You’ll be prompted to enter the password for new_username to access the database.

This will create a new superuser with the specified password, allowing you to access the database using this new user.

Heya, @charmeem

You can create the superuser using CREATE ROLE

CREATE ROLE new_username WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'your_password';

You can manage privileges efficiently by creating a group role:

1. Create a group role:

CREATE ROLE admin_group WITH SUPERUSER CREATEDB CREATEROLE NOLOGIN;

2. Assign your new user to the group:

GRANT admin_group TO new_username;

This setup makes it easier to manage superuser-like privileges for multiple users.

Also assign superuser privileges only if absolutely necessary. For less sensitive tasks, consider roles with specific privileges instead.

Hope that this helps!

Hi there,

In addition to what has already been mentioned, if you are using a managed Postgres cluster, you can follow the steps on how to do that here:

https://docs.digitalocean.com/products/databases/postgresql/how-to/manage-users-and-databases/

- Bobby