Learn how to create databases, users, and grant permissions in PostgreSQL on Ubuntu with this simple step-by-step guide for efficient management.

1. Access the PostgreSQL Command Line Interface (CLI)

Run the following command to switch to the PostgreSQL user and enter the PostgreSQL CLI:

sudo -u postgres psql

2. Create a Database

Use the CREATE DATABASE command to create a new database. Replace my_database with your desired database name:

CREATE DATABASE my_database;

You should see a confirmation like CREATE DATABASE.

3. Create a New User

Use the CREATE USER command to create a new user. Replace my_user with the desired username and my_password with a strong password:

CREATE USER my_user WITH PASSWORD 'my_password';

4. Grant Permissions

You can grant permissions to the user based on your requirements.

Grant All Privileges on the Database:

To give the user full control over the database, use the following command:

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

Grant Specific Privileges:

If you want to grant specific permissions (e.g., SELECT, INSERT), use:

GRANT SELECT, INSERT ON DATABASE my_database TO my_user;

5. Verify the Changes

To check the granted privileges, you can use:

\du

This lists all roles and their privileges.

6. Exit the PostgreSQL CLI

Type \q to exit the PostgreSQL CLI.

7. Optional: Adjust User Role Attributes

If you want the user to create databases or have superuser privileges, you can alter the role:

Grant Superuser:

ALTER USER my_user WITH SUPERUSER;

Grant Database Creation Permission:

ALTER USER my_user WITH CREATEDB;

Grant Role Creation Permission:

ALTER USER my_user WITH CREATEROLE;

8. Reload PostgreSQL Configuration (if necessary)

If you make changes to PostgreSQL configuration files, reload the service:

sudo systemctl reload postgresql

Example:

Here’s a full example:

sudo -u postgres psql

CREATE DATABASE example_db;
CREATE USER example_user WITH PASSWORD 'securepassword';
GRANT ALL PRIVILEGES ON DATABASE example_db TO example_user;
\q

Now, the user example_user can access and manage the database example_db with the permissions granted.

You’re all set! ?