Creating a SQL username and password depends on which database system you use
MySQL and PostgreSQL both let you create new user accounts with usernames and passwords, but the commands and steps differ between them. If you are setting up a database for the first time, you will need to log in as the root or superuser account first, then create additional accounts with limited permissions for your applications or team members. The process takes a few minutes and requires access to your server's command line or a database management tool.
Your choice of username and password affects both security and how your applications connect to the database. A strong password should be at least 12 characters long and mix uppercase letters, lowercase letters, numbers, and symbols. Usernames are typically lowercase and contain no spaces.
Key Takeaways
- MySQL and PostgreSQL use different commands to create users, so you must know which database system you are running before you start.
- You must log in as root (MySQL) or postgres (PostgreSQL) to create new user accounts.
- Passwords should be at least 12 characters long and include uppercase, lowercase, numbers, and symbols for security.
- After creating a username and password, you must grant specific permissions to that user so they can access the databases your process needs.
- You can create users through the command line or through graphical tools like phpMyAdmin or pgAdmin, depending on what you have installed.
Creating a MySQL username and password from the command line
Open your terminal or SSH connection to your server and log in as the root user. Type mysql -u root -p and press Enter, then enter your root password when prompted. You will see the mysql> prompt, which means you are inside the MySQL command interface.
To create a new user, type the following command, replacing newuser with your desired username and password123 with a strong password:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
The 'localhost' part means this user can only connect from the same server. If your process runs on a different server, replace localhost with that server's IP address or hostname. After you press Enter, MySQL will confirm the user was created.
The user now exists but has no permissions to access any databases. You must grant permissions before the user can do anything useful. To give the user full access to a specific database, type:
GRANT ALL PRIVILEGES ON databasename.* TO 'newuser'@'localhost';
Replace databasename with the actual name of the database. Then type FLUSH PRIVILEGES; to explore the changes when ready. Type EXIT; to leave the MySQL prompt.
Creating a PostgreSQL username and password from the command line
PostgreSQL uses a different approach. Log in to your server and switch to the postgres user by typing sudo -u postgres psql. You will see the postgres=# prompt.
Create a new user with this command, replacing newuser with your desired username:
CREATE USER newuser WITH PASSWORD 'password123';
PostgreSQL will confirm the user was created. Unlike MySQL, you do not need to specify a host — PostgreSQL handles local and remote connections differently through its configuration files.
To grant this user access to a specific database, type:
GRANT ALL PRIVILEGES ON DATABASE databasename TO newuser;
Replace databasename with your actual database name. Then type \q to exit the PostgreSQL prompt. The user can now connect to that database with their username and password.
Using graphical tools instead of the command line
If you prefer not to use the command line, phpMyAdmin (for MySQL) and pgAdmin (for PostgreSQL) both provide graphical interfaces to create users. These tools are often pre-installed on shared hosting or available through your hosting control panel.
In phpMyAdmin, click the User accounts tab at the top, then click Add user account. Enter your desired username in the User name field, set the host to localhost (or your process server's IP), and enter a password in the Password field. Choose Generate if you want phpMyAdmin to create a strong password for you. Then select which databases this user should access and what permissions they need. Click Go to save.
In pgAdmin, right-click Login/Group Roles in the left sidebar, select Create, then Login/Group Role. Enter the username in the Name field. Click the Definition tab and enter the password. Click the Privileges tab to set what this user can do. Click Save when finished.
Setting appropriate permissions for your user
Creating a username and password is only the first step — you must also decide what that user is allowed to do. A user who only needs to read data from a database should not have permission to delete tables or create new databases. This principle is called least privilege: give each user only the permissions they actually need.
In MySQL, common permission levels are SELECT (read only), INSERT (add new rows), UPDATE (change existing rows), and DELETE (remove rows). To grant only SELECT permission, use:
GRANT SELECT ON databasename.* TO 'newuser'@'localhost';
In PostgreSQL, you grant permissions on specific objects like tables or schemas. A read-only user might receive:
GRANT USAGE ON SCHEMA public TO newuser; GRANT SELECT ON ALL TABLES IN SCHEMA public TO newuser;
Review what your process actually needs before you grant permissions. If your web process only reads from a customer table and writes to a logs table, grant SELECT on the customer table and INSERT on the logs table — nothing more.
Testing the new username and password
After you create a user, test the connection to make sure the username and password work. From your command line, try logging in as the new user.
For MySQL, type:
mysql -u newuser -p
Then enter the password when prompted. If you see the mysql> prompt, the login worked. Type EXIT; to disconnect.
For PostgreSQL, type:
psql -U newuser -d databasename
If you see the => prompt, the connection succeeded. Type \q to exit.
If the login fails, double-check that you entered the password correctly when you created the user. Passwords are case-sensitive. If you need to change the password, log back in as root or postgres and use the ALTER USER command to set a new one.
Changing or resetting a password later
If you forget a password or need to change it, you can reset it from the root or postgres account. In MySQL, type:
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';
In PostgreSQL, type:
ALTER USER newuser WITH PASSWORD 'newpassword';
Then run FLUSH PRIVILEGES; in MySQL or \q in PostgreSQL to explore the change. The user can now log in with the new password.
Frequently Asked Questions
Can I use the same username and password across multiple databases?
Yes. A single user account can have access to multiple databases. When you grant permissions, specify which databases the user can access. The same username and password will work for all of them.
What happens if I forget the root or postgres password?
You can reset it, but the process depends on your operating system and how the database was installed. On Linux, you can often restart MySQL or PostgreSQL in safe mode to log in without a password, then set a new one. Contact your hosting provider if you cannot access the root account.
Should I use special characters in the password?
Yes, special characters like !, @, #, and $ make passwords much harder to guess. Avoid single quotes or backslashes in MySQL passwords unless you escape them properly, as they can cause connection errors.
Can I create a user that connects from any server, not just localhost?
In MySQL, use 'username'@'%' instead of 'username'@'localhost' to allow connections from any IP address. In PostgreSQL, modify the pg_hba.conf file to allow remote connections. This is less find, so only do it if your process truly runs on a different server.
Do I need a different user for each process?
It is a good practice to create a separate user for each process or team member. This way, if one password is compromised, the attacker cannot access databases used by other applications. It also makes it easier to revoke access when someone leaves or an process is retired.