Set up MySQL, PostgreSQL, or SQLite for a Laravel web application

I’m showing only a basic setup in this guide, encompassing:

• Password-based authentication using MySQL’s default authentication plugin (caching_sha2_password at the time of writing).
• Granting all privileges on the web app’s DB to the non-root account. Note: we’re intentionally restricting the non-root account’s privileges to only the web app’s database (i.e. using GRANT ALL PRIVILEGES ON laraveldb.*) and not granting privilege for all databases on the server (which would be GRANT ALL PRIVILEGES ON *.*).

This is all you need to run a Laravel web app and should work well for most users, but keep in mind that there are many more possible authentication methods and privilege grants to choose from. Consider reading through these two Digital Ocean guides if you’re new to managing MySQL databases.

TLDR: read the warning about mysql_secure_installation in this Digital Ocean article, perform the suggested temporary change of root@localhost’s authentication method to mysql_native_password, then run:

# ASSUMING you've allowed password authentication for the root account, just
# follow the prompts and use common sense.
laravel@server$ sudo mysql_secure_installation

End TLDR

It’s best practice to secure your MySQL install using the mysql_secure_installation tool.

But at the time of writing, this is complicated by a clash between mysql_secure_installation and Ubuntu’s default MySQL settings: namely, mysql_secure_installation attempts to set a password for the root database account, but by default this account is only allowed to authenticate using Unix socket authentication (i.e. MySQL’s auth_socket method).

You can get around this by tweaking some MySQL config settings, but it takes manual intervention on your part. Digital Ocean has already covered this, so I refer to their guide (here you go) and see no reason to repeat the same information here.

Logging in to PostgreSQL can (understandably) be a bit confusing for new users.

Here’s everything involved in a PostgreSQL connection:

• A PostgreSQL database
• A PostgreSQL user account
• A host and port number
• A connection type
• An authentication method

(Most of these are set implicitly, so beginner users are not aware of them—this causes confusion upon encountering problems that require knowing what’s going on under the hood.)

First some background: the PostgreSQL installation, among other things, creates:

• A Linux user called postgres (you can confirm this by checking the bottom of the /etc/passwd file, which keeps track of Linux users, after installing PostgreSQL).
• A PostgreSQL account, also called postgres.
• A PostgreSQL database, also called postgres.
• A psql binary, often in /usr/bin/psql.

When you run psql without explicitly specifying a database, PostgreSQL user, host, or port, psql will try to log you into the PostgreSQL shell:

• as the PostgreSQL user whose username matches the username of the Linux user who ran the psql command (using peer-based authentication, which relies precisely on the Linux user’s and PostgreSQL user’s names matching),
• connected to the database whose name matches the username of the Linux user who ran the psql command,
• on port 5432, the default PostgreSQL port number,
• using a Unix domain socket connection (the local connection type in the pg_hba.conf file) on the local host.

(The last two bullet points fall beyond the scope of this tutorial and require some networking background, so don’t worry if you don’t understand them.) My goal here is mainly for you to understand why we switch to the postgres Linux user before running psql.

Well, our goal is to log in to the PostgreSQL shell as the postgres PostgreSQL user, connected to the default postgres database. We’ve mentioned that, by default, psql will connect you to the database and PostgreSQL account matching the name of the Linux user who ran the psql command, so running psql as the postgres Linux user ensures we connect to the postgres database as the postgres PostgreSQL user.

(And why connect with the postgres PostgreSQL account in the first place? Answer: postgres is the PostgreSQL superuser account (like the root Linux account) that is used to create new databases and other users.)

• You can show psql connection info by running \conninfo after connecting to a PostgreSQL shell.
• psql’s -U option lets you log in to the PostgreSQL shell as a custom user (but will need appropriate permissions/authentication).
• psql’s -d option lets you connect to a custom database
• psql’s -h option lets you connect to a specific host, and can be used to attempt a host-based connection.
• The comments in the pg_hba.conf file nicely summarize practical use of various authentication methods and connection types (you can locate the file by running SHOW hba_file; as a superuser from a PostgreSQL shell). (The pg_hba.conf file is where you configure PostgreSQL authentication, but you should know what you’re doing before editing it.)
• Chapter 21 of the PostgreSQL manual thoroughly covers authentication in PostgreSQL. This is where to go if you’re curious about things like connection types and authentication methods beyond the cursory info in this article.

I’m showing only a basic setup in this guide, encompassing:

• Password-based authentication (that will only work with local host-based connections on the server). This means you’ll need to add an -h flag when using psql.
• Giving the PostgreSQL user permissions to interact with the app’s database by simply making the user the owner of the database.

This is all you need to run a Laravel web app and should work well for most users, but keep in mind that there are many more possible authentication methods and privilege grants to choose from. If inspired consider reading through the PostgreSQL docs on client authentication and available privileges, as well as this StackOverflow answer and the references therein.