Download the PAM eBook PDF
Up to 80% of data breaches in the U.S. start with unauthorized access. Without insights into who’s accessing your database, their login privileges, and the duration of each session, it can be difficult to prevent potential security threats. Fortunately, PostgreSQL is a powerful open-source relational database management system you can use to manage access control. Known for its advanced support, complex queries, and rich features, PostgreSQL can list users in your database for security audits, compliance checks, and database access management. But how exactly do you list users in PostgreSQL? This guide walks you through the steps of listing PostgreSQL users using command-line queries and graphical user interface (GUI)-based tools.
StrongDM manages and audits access to infrastructure.
- Role-based, attribute-based, & just-in-time access to infrastructure
- Connect any person or service to any infrastructure, anywhere
- Logging like you've never seen
MySQL is an open-source relational database, made famous by its ease-of-use and simple setup on modern Linux and Windows operating systems. On an unmodified MySQL install, the root user account does not have a password. This is extremely insecure!
As a systems administrator, we know that the easiest way to compromise a system is using the default unchanged password with admin privileges. To set the root password for the root account:
$ mysql -u root --skip-password
Assign a password with the following command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';
Luckily, in most situations, operating system-specific installs of MySQL will be set to generate a random password for the root user when the database is started for the first time. Instead of setting the password, you’ll change it.
Different platforms (Windows, Linux, etc) will require slightly different methods for resetting the password.
Changing/Resetting the Root User’s Password on Linux
Log on to your system as the Unix user that the MySQL server runs as (for example, mysql). These will work on most Linux distributions (ubuntu, Debian, Fedora, etc).
Step 1
Log on to your target system with SSH or other remote shell
Step 2
Stop the MySQL server if it is running. Most times, this is done using your operating system’s init system (systemD, SysV init, or upstart).
# upstart$ sudo service mysql stop# SysV init/etc/init.d/mysql-server stopStep 3
If you’re unsure, or you have a custom MySQL installation, you can stop the MySQL server by sending a normal TERM to the mysqld process using the kill command
$ killall mysqld
Step 4
Create a text file containing the password assignment SQL statement on a single line
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';Step 5
Save the file to disk. Note: ensure the mysql user (or the user the database is running under) has read access to the file.
Step 6
Start the MySQL server with the init_file CLI flag set to name the file you saved above.
$ mysqld --init-file=/home/me/mysql-initStep 7
When the server boots, it will execute the contents of the file specified above which will change the password.
Step 8
After the server has started successfully, you can delete the SQL file you created above. In a separate command prompt, or with your GUI tool of choice, you should now be able to connect to the MySQL server as root using the new password.
Step 9
Stop the MySQL server (usually control-C) and start MySQL it normally from your operating system’s init system
#upstart$ sudo service mysql start# SysV init/etc/init.d/mysql-server stopChanging/Resetting the Root User’s Password on Windows
Resetting the root password is very similar to the process used on Linux, with a few tweaks! We will create another init file containing the password reset statement, and start the server manually in our command prompt.
Step 1
Log on to your system via RDP or on the console as a user with administrative privileges
Step 2
Stop the MySQL server if it is running
For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list and stop it.
Step 3
Create a text file containing the password assignment SQL statement on a single line
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Step 4
Save the file to disk.
Step 5
Open the command prompt (Start → Run → cmd)
Step 6
Start the MySQL server with the init_file CLI flag set to name the file you saved above. Note: backslashes must be escaped, so use two for the file path like the example below.
C:\> cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"C:\> mysqld --init-file=C:\\mysql-init.txtStep 7
When the server boots, it will execute the contents of the file specified above which will change the password.
Step 8
After the server has started successfully, you can delete the SQL file you created above. In a separate command prompt, or with your GUI tool of choice, you should now be able to connect to the MySQL server as root using the new password.
Step 9
Stop the MySQL server and restart it normally from the Windows Service Console.
Managing MySQL Users with StrongDM
Most security professionals would not recommend using the root user for human or application access to a database. The administrative privileges that the root user possesses are a huge security vulnerability waiting to happen!
So what’s the best way to do database access for your developers? Multiple accounts? One shared account? If you change the password, how do you communicate that to everyone?
Enter StrongDM. StrongDM allows you to abstract-away usernames and passwords from the developers and allows the systems administrator to keep the master passwords in a safe space under lock and key. StrongDM provides you centralized authentication and auditing for all actions against a MySQL host.
Try StrongDM for yourself with a free, 14-day trial or schedule a 15-minute demo with the team.
💙 this post?
Then get all that StrongDM goodness, right in your inbox.
You May Also Like
This guide breaks down the top cloud database solutions reshaping how organizations store, manage, and scale data. From relational databases to NoSQL options, we’ll cover what matters most when choosing the right solution for your needs. By the end, you’ll understand how modern cloud databases drive scalability and performance—and which one is the best fit for your organization.
When working with or administering a database, you'll need to delete, remove, or drop users at some point. This might seem like a routine task, but dropping unnecessary or old users is crucial for maintaining database security. This helps remove access points for malicious actors — both in-house and external — so only authorized individuals can perform operations on it.
Having a complete view of all your databases in PostgreSQL is essential for effective database management. This guide explores six proven methods you can use to quickly list all of your databases.
