It is typically considered a best practice to run your database server as a standalone machine. This gives you the ability to tune the system for its primary function as well as many other benefits. This separation from application servers also means that you will have to enable remote machines to connect to it. I’d like to go over how this is accomplished in MySQL, which is what I am used to as well as what I discovered about how Postgres handles remote connections.
MySQL has an optional configuration variable called bind-address that defaults to 0.0.0.0. In some distributions, the MySQL package installs MySQL and sets this variable to 127.0.0.1, which is obviously the localhost. You need to comment out this line in the my.cnf file, or specify the servers address so that it listens on the correct IP. This is not a dynamic variable, so a server/service restart is required to apply the change.
Postgres has a similar configuration variable called listen_addresses. This variable defaults to localhost which restricts remote access. You can change this value to be the IP you want the server to listen on, 0.0.0.0, or a string (using ‘ marks) containing an asterisk ‘*’. This value can only be set at server start, so you will have to restart the postgresql service to apply the change.
Restrictions by network or IP
I come from a MySQL world where you specify the address that the user is allowed to connect from as part of the user creation process. Something like the below create statement would work for restricting access to a single IP, or you can use a % as a wildcard. Wildcards can be mixed with ip addresses to denote a block (i.e. 192.168.1.%).
create user '[username]'@'[ip]' identified by password '[password_hash]';
Postgres uses a file based permission mechanism. The file pg_hba.conf contains the settings for what connections are allowed or disallowed. The below formats are acceptable in this file:
local database user auth-method [auth-options] host database user address auth-method [auth-options] hostssl database user address auth-method [auth-options] hostnossl database user address auth-method [auth-options] host database user IP-address IP-mask auth-method [auth-options] hostssl database user IP-address IP-mask auth-method [auth-options] hostnossl database user IP-address IP-mask auth-method [auth-options]
To allow for remote connections on Postgres you must have a line similar to these:
#non ssl host all all 192.168.1.0/24 md5 #ssl hostssl all all 192.168.1.0/24 md5
The keyword “all” can be used in place of individual usernames or databases and is a substitute for listing all of the users or databases with separate rules. Rules are applied in the order in which they occur in this file. Once a match is encountered for a user, that is the rule that will be applied to them. If you run into an issue where you have an explicit rule for something to be allowed and it is still not working, you will want to double check that you are not matching a rule higher up in the list first and not getting the access you are trying to achieve.
In addition to these actions, you have to give at minimum the “login” privilege when creating the user in Postgres to allow them login permissions. Login can be toggled off if you would like to preserve an account but disable it at a later time.
create user [username] with login;
Other options that can be granted with the create user statement include:
In summary, Postgres separates out the location based permissions to a file, and changes to this file require a restart of the server. Postgres does not require as much information for creating a user because of this permissions file.
MySQL has a setting that allows you to specify what IP(s) to listen on and changes to this configuration variable will require a restart of the server as well, however all other server access permissions are handled within the database and user creation process.