Pgpool with load balancing Postgresql cluster has one serious drawback. If you are using SCRAM passwords, the passwords have to be entered also to Pgpool with pg_enc. Pg_enc needs access to salt file so users who run it, have to have access to salt file. They also have to have access to servers. Safest way is to run pg_enc only with postgres account, but then it would be the administrators job to change user's passwords. So user's would have to give passwords to admins and admin change the passwords. Cannot happen.
Here is a solution. First we create one bash script to change password. The underlying installation is done more or less with these instructions: Pgpool-II + Watchdog Setup Example
--700 postgres:postgres /etc/pgpool2/pgpool_set_password.sh ----------------------------------------------------------
#!/bin/bash
USERNAME="$1"
NEW_PASSWORD="$2"
NODE="node-1.pgcluster.net"
echo "Setting password to node-1"
## Test passwordless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
if [ $? -ne 0 ]; then
logger -i -p local1.info pgpool_set_password.sh: passwordless SSH to postgres@${NODE} failed. Please setup passwordless SSH.
exit 1
fi
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool /usr/sbin/pg_enc -m -k ~/.pgpoolkey -u ${USERNAME} ${NEW_PASSWORD}
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool /usr/sbin/pgpool reload
echo "Setting password to node-2"
NODE="node-2.pgcluster.net"
## Test passwordless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
if [ $? -ne 0 ]; then
logger -i -p local1.info pgpool_set_password.sh: passwordless SSH to postgres@${NODE} failed. Please setup passwordless SSH.
exit 1
fi
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool /usr/sbin/pg_enc -m -k ~/.pgpoolkey -u ${USERNAME} ${NEW_PASSWORD}
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool /usr/sbin/pgpool reload
echo "Setting password to node-3"
NODE="node-3.pgcluster.net"
## Test passwordless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
if [ $? -ne 0 ]; then
logger -i -p local1.info pgpool_set_password.sh: passwordless SSH to postgres@${NODE} failed. Please setup passwordless SSH.
exit 1
fi
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool /usr/sbin/pg_enc -m -k ~/.pgpoolkey -u ${USERNAME} ${NEW_PASSWORD}
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NODE} -i ~/.ssh/id_rsa_pgpool /usr/sbin/pgpool reload
echo "Password set"
This script takes username and password as parameters and sets the password in each of the Pgpool servers. It only works with postgres account so it's only ment for admins.
Then we create pgpool_set_password function in Postgresql.
First connect to Postgresql as postgres user.
\c postgres
CREATE DOMAIN whoami AS NAME
CHECK( VALUE = CURRENT_USER )
;
BEGIN;
CREATE OR REPLACE FUNCTION pgpool_set_password(new_password TEXT,user_name whoami DEFAULT CURRENT_USER ) RETURNS TEXT
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER
SET search_path = pg_catalog,pg_temp
as $$
begin
PERFORM pg_advisory_xact_lock(875476457347);
EXECUTE format('COPY (SELECT 1) TO PROGRAM ''/etc/pgpool2/pgpool_set_password.sh %s ''''%s'''' '' ', user_name, replace(new_password,'''',''''''));
EXECUTE format('ALTER USER %s WITH PASSWORD ''%s'' ;' , user_name, replace(new_password,'''',''''''));
return 'PgPool password changed';
end;
$$
;
REVOKE ALL ON FUNCTION pgpool_set_password(new_password TEXT,user_name whoami ) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgpool_set_password(new_password TEXT,user_name whoami ) TO PUBLIC;
COMMIT;
Ok, lets go through what we did here.
SECURITY DEFINER means function is run with privileges of who defined the function so it's run as postgres user.
Function is created in postgres schema, where others cannot change or replace this function.
SET search_path = pg_catalog,pg_temp is needed so nobody can overload any functions this function uses. More about this in Abusing SECURITY DEFINER functions
Because the function has security definer set, it is run as postges user so inside the function we cannot get the real users username. So we have the username as parameter and default value is CURRENT_USER because it can be set as the real user before the function is executed. We also create a domain whoami that checks that username parameter is the same as the current user. This makes sure user can change only his/hers own password. The default is user's own username, so user can run the function simply by select pgpool_set_password('new_password');
To execute bash script we execute COPY (SELECT 1 ) TO PROGRAM 'script.sh'. It is normally used for transferring data from and to postgres but can also be (mis)used just for executing programs. Because Postgresql is run as postgres user, the script is also run as postgres user. Which is exactly what we need. Also only postgresql admins can execute COPY but that's why we created the function with
security definer.
We then change the password from Postgresql also with ALTER USER username WITH PASSWORD 'new_password'.
The function is created inside a block that also revokes any privileges from public and the grants execure privilege to public. Because it's all done in one block, there is no theoretical chance that any other users can change this function.
Ok, so we now have a function in Postgresql that users can use to change their passwords without need to give any additional privileges to users.
Comments
Post a Comment