Remember way back in the set_user Installation and Set-Up post when we edited postgresql.conf to add set_user to the shared_preload_libraries?
Well, there are some additional parameters that may be optionally added to the config file and set to control their respective commands.
First, let’s look at the settings that limit users who have been escalated to superuser and their ability to run commands you may only want the true administrator to execute.
The good news is set_user’s default is set to “on” which will not allow the users who have been escalated to superuser to access these commands. So, if you don’t want your escalated users to have these privileges, you can skip adding these lines to your postgres.conf.
- set_user.block_alter_system = (defaults to “on”)
- set_user.block_copy_program = (defaults to “on”)
- set_user.block_log_statement = (defaults to “on”)
There are times when you want to give your escalated users priviliges to some of these super – superuser commands. Let’s break these config settings down to better understand when and if you want to unlock your users from these restrictions.
ALTER SYSTEM is used for changing server configuration parameters across the entire database cluster. It can be more convenient than the traditional method of manually editing the postgresql.conf file. ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf.
Your postgresql.conf is a sacred document, only to be trusted with users who understand the implications of making changes.
Starting in Postgres 9.3 COPY added the PROGRAM option, allowing you to pipe data with an external program, both as input and output.
In COPY FROM, the input is read from standard output of the command, and in COPY TO, the output is written to the standard input of the command.
Note that the command is invoked by the shell, so if you need to pass any arguments to shell command that come from an untrusted source, you must be careful to strip or escape any special characters that might have a special meaning for the shell. For security reasons, it is best to use a fixed command string, or at least avoid passing any user input in it.
So, for example if you want to copy json data from a weather site into your database, you could use something like:
COPY weather_json FROM PROGRAM 'curl http://api.openweathermap.org/data/2.5/weather?q=Tokyo';
If set_user.block copy program is set to ’off’, COPY PROGRAM commands will be allowed.
You want to keep these commands blocked until needed because importing data from an external sources can be dangerous (for many reasons). Even worse, with export someone could take your data (see Equifax).
SET log statement and variations
There are many ways to customize your logging processes based on when, what and how you want your logs created.
If set user.block log statement is set to ’on’, SET log statement and variations will be blocked.
Allowing escalated users to customize logging behavior could lead to a loss in auditing trail or overwhelming i/o resulting in latency… and worse!
reset_user() will revoke block commands
When finished with required actions as rolename, the reset_user() function can be executed to restore the original user. At that point, blocked command behaviors return to normal.
In our next post, we will explore another advanced control feature: whitelist.
Although this extension compiles and works with all supported versions of PostgreSQL starting with PostgreSQL 9.1, all features are not supported until PostgreSQL 9.4 or higher. The ALTER SYSTEM command does not exist prior to 9.4 and COPY PROGRAM does not exist prior to 9.3.user to shared_preload_libraries in postgresql.conf.