Tom Lane <t...@sss.pgh.pa.us> writes:
> It's not very clear what you mean by "sometimes". Is the slowness
reproducible for a particular user and role configuration, or does
it seem to come and go by itself?
Ah it's more come and go by itself - as in one connection takes 30 seconds,
then the next say 0.06s. It's happened for every user we've tried. Even
more anecdotally, I would say it happens more when the database is busy in
terms of tables being dropped/created and permissions changing.
Also: realise we did have one user that had directly was a member of
several thousand roles, but indirectly several million. It would sometimes
take 10 minutes for that user to connect. We've since changed that to one
role, and that user connects fine now.
> As Tomas said, a self-contained reproduction script would be very
helpful for looking into this.
Have tried... but alas it seems fine in anything but the production
environment. My closest attempt is attached to at least it show in more
detail how our system is setup, but it always works fine for me locally.
I am wondering - what happens on connection? What catalogue tables does
PostgreSQL check and how? What's allowed to happen concurrently and what
isn't? If I knew, maybe I could come up with a reproduction script that
does reproduce the issue?
#!/bin/bash
set -e
export PGPASSWORD=password
export PGUSER=postgres
export PGDATABASE=postgres
export PGHOST=127.0.0.1
echo "Starting PostgreSQL..."
trap "exit" INT TERM
trap "echo 'Stopping PostgreSQL'; docker stop postgres_perf" EXIT
docker run --rm -d -p 5432:5432 -e POSTGRES_PASSWORD=$PGPASSWORD --name postgres_perf postgres:14.10
while ! pg_isready --host 127.0.0.1 -U postgres
do
echo "Waiting for PostgreSQL to start..."
sleep 1
done
echo "PostgreSQL started"
rm -f setup.sql
echo "Creating user"
echo "CREATE USER connecting_user WITH password 'password';" >> setup.sql
echo "Creating database connect role..."
echo "CREATE ROLE database_connect;" >> setup.sql
echo "GRANT CONNECT ON DATABASE postgres TO database_connect;" >> setup.sql
echo "GRANT database_connect TO connecting_user;" >> setup.sql
echo "Creating intermediate role"
echo "CREATE ROLE intermediate;" >> setup.sql
echo "Creating script to create and roles with SELECT perms"
for idx in $(seq -w 1 10000); do
echo "CREATE TABLE table_${idx}(id int);"
echo "CREATE ROLE table_select_${idx};"
echo "GRANT SELECT ON table_${idx} TO table_select_${idx};"
echo "GRANT table_select_${idx} TO intermediate;"
done >> setup.sql
echo "Running script..."
psql -q -f setup.sql
export PGUSER=connecting_user
time psql -q -c 'SELECT 1;'
echo "Done"
