Skip to content

Instantly share code, notes, and snippets.

@genslein
Last active November 7, 2019 20:21
Show Gist options
  • Save genslein/663f13745d104a2a1b033ff45fd4aba8 to your computer and use it in GitHub Desktop.
Save genslein/663f13745d104a2a1b033ff45fd4aba8 to your computer and use it in GitHub Desktop.
docker testing pgextwlist oid collision issue
# Start stock xenial
docker run -it ubuntu:xenial bash
# Prepare image
apt-get update -y \
&& apt-get install -y vim software-properties-common wget apt-transport-https
add-apt-repository -y "deb https://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" \
&& wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update \
&& apt-get install -y --no-install-recommends postgresql-common \
&& echo 'create_main_cluster = false' >> /etc/postgresql-common/createcluster.conf \
&& apt-get install -y --no-install-recommends \
postgresql-12 \
postgresql-12-dbg \
postgresql-12-pgextwlist \
postgresql-client-12 \
postgresql-contrib-12
# Update locale and make dir/files
locale-gen en_US.UTF-8 && update-locale;
mkdir /database && chown postgres /database;
touch /logfile && chown postgres /logfile;
# initdb and setup
su postgres
touch /tmp/pwdfile && echo "postgres_pass" > /tmp/pwdfile
env \
LC_COLLATE=en_US.UTF-8 \
LC_CTYPE=en_US.UTF-8 \
LC_MESSAGES=en_US.UTF-8 \
LC_MONETARY=en_US.UTF-8 \
LC_NUMERIC=en_US.UTF-8 \
LC_TIME=en_US.UTF-8 \
/usr/lib/postgresql/12/bin/initdb -D /database -E UTF8 -U postgres --pwfile=/tmp/pwdfile
rm /tmp/pwdfile
mkdir -p /database/pgextwlist/pg_stat_statements && touch /database/pgextwlist/pg_stat_statements/after-create.sql;
echo "GRANT EXECUTE ON FUNCTION pg_stat_statements_reset(Oid, Oid, bigint) TO test_user;" >> /database/pgextwlist/pg_stat_statements/after-create.sql;
#Update configuration to use pgextwlist
cat <<EOF >> /database/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
local_preload_libraries = pgextwlist
extwlist.extensions = 'pg_stat_statements'
extwlist.custom_path = '/database/pgextwlist'
EOF
# Start process, create user/db
/usr/lib/postgresql/12/bin/pg_ctl -D /database -l /logfile start
PGPASSWORD=postgres_pass psql -p 5432 -U postgres <<EOF
SET log_statement TO 'none';
ALTER ROLE postgres WITH PASSWORD 'postgres_pass';
ALTER ROLE postgres SET search_path = 'pg_catalog';
REVOKE ALL ON DATABASE postgres FROM PUBLIC;
CREATE ROLE test_user;
ALTER ROLE test_user WITH LOGIN PASSWORD 'test_user_pass' NOSUPERUSER NOCREATEDB NOCREATEROLE;
REVOKE ALL ON DATABASE postgres FROM test_user;
CREATE DATABASE test_db OWNER test_user;
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE test_db FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT CONNECT ON DATABASE test_db TO test_user;
GRANT ALL ON DATABASE test_db TO test_user;
GRANT ALL ON SCHEMA public TO test_user;
SELECT version();
EOF
PGPASSWORD=postgres_pass psql -U postgres -d postgres -c 'CREATE EXTENSION pg_stat_statements WITH SCHEMA public'
PGPASSWORD=test_user_pass psql -U test_user -d test_db -c 'CREATE EXTENSION pg_stat_statements WITH SCHEMA public'
PGPASSWORD=postgres_pass psql -U postgres -d postgres -c 'SELECT * FROM pg_extension'
PGPASSWORD=test_user_pass psql -U test_user -d test_db -c 'SELECT * FROM pg_extension'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment