Re: Extremely slow to establish connection when user has a high number of roles

[Michal Charemza <michal@xxxxxxxxxxxxx>]

Tom Lane <tgl@xxxxxxxxxxxxx> 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"