Best Practice for Running Programmatic Postgres DB Migrations on Linux

On many varieties of Linux PostgreSQL runs under a separate user account, so you have to do:

sudo su - postgres

to get any work done. That’s all well and good if you just want to type SQL in manually, but what if you have a migration written in a programming language (in my case, Node/Knex)?

Is the common practice to somehow make the code aware of the user situation (ie. write something equivalent to sudo su - postgres in to my code)?

Or, is it to run all of my code as the DB user (even though that would mean giving my DB user permissions on my non-DB user’s home folder)?

Or, is it to make my normal user have Postgres access (in which case why does Linux even bother setting Postgres up on a separate user)?

Or, is there some other approach I’m missing?

P.S. I realize this is somewhat a systems administration question, but I posted here rather than super user because it’s specifically about running programmer-written code (which just happens to alter a database).

Answer

You are conflating three separate user accounts.

First there is the OS account under which the postgresql daemon runs. As you say in most Linux distros this would be a separate user used only for this purpose, often named postgres. This is to prevent other users on the system from accessing the postgresql data files and other resources, and also to limit the damage that could be done by someone who hacked their way into the database.

Then there is the user account which the client program, such as psql or your migration tool might run under.

Finally there is the postgresql user account. Postgresql has it’s own user account system to manage the permissions of users within the databases that it administrates, unconnected to the OS user account system.

The one are of overlap between the OS accounts and the postgresql database accounts is that the psql command line tool will connect to the database using a user name the same as the OS user running the tool if you do not specify a user on the command line. For example, if I connect with this:

psql mydatabase

then it will attempt to connect with the user harmic, my Linux user account, but if I use this:

psql -U postgres mydatabase

then it will connect with the user postgres, which is the default administrator account.

Another related aspect is the authentication method. Most likely, if you try the above command on your machine, you would get an error. This is due to the allowed authentication methods, which are configured in the file pg_hba.conf. This file configures allowed authentication methods that specific users can use when connecting to specific databases from specific hosts. The postgres user is normally only allowed to connect from within the same host, using ident as the authentication method, which means identify the user based on the OS user running the command.

This explains why you have been using sudo su - postgres to switch to the postgres user: most likely in your current configuration that is the only way to access this account.

OK, this probably all sounds rather complex. To simplify things, here are my recommendations for best practices in this area:

1. Do not mess with the OS account used to run the database backend. It is not needed and would weaken security.

2. Create a separate database account for administrating the application’s database(s). Use this account rather than the postgres account for migration scripts and the like. The reason for this is that the postgres account has full permissions over all databases on the server, while you can grant your admin user only the permissions it needs, and only to the database(s) the application controls (not any other databases that might be there). See: CREATE USER SQL command.

3. Update the pg_hba.conf file to specify the authentication mode that will be used to authenticate this user. See Client Authentication in the manual. md5 with a suitably strong password might be a good choice.

4. Update your migration tool to use this new user. The user (and password if using passwords) would be supplied via the connection string or connection parameters supplied when connecting to the database. Likewise when connecting with psql specify the user name with the -U option.

   Note that there is no need to use sudo su - or even to have an OS account with the same name as the admin user.