Database lock on command: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA, while looping though schemas

From: AC Gomez <antklc(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Database lock on command: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA, while looping though schemas
Date: 2020-04-13 00:23:00
Message-ID: CABtmK-ijZcxOc7wstLa7w_gpGXQwZNd2zi3uf+bFsps_R=S0Nw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

In PostgreSQL 9.5:

I have created a function that does the following:

USER CREATE: 'CREATE USER user_x WITH PASSWORD 'abc' CREATEDB CREATEROLE;'
WITH GRANT: 'GRANT master_user TO user_x;'
GRANT CONNECT ON DATABASE my_db TO user_x

LOOP THROUGH ALL USER SCHEMAS:
OUTER LOOP: GRANT USAGE ON SCHEMA schemaN TO user_x
OUTER LOOP: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaN TO
user_x
OUTER LOOP: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schemaN TO
user_x
LOOP THROUGH ALL FUNCTIONS:
INNER LOOP: GRANT EXECUTE ON FUNCTION funcN() TO user_x

The* first iteration of the loop runs as expected*, no errors and it always
runs on *PUBLIC schema* first.

BUT, on the second iteration of the loop, it picks up the second schema,
and runs the first GRANT: GRANT USAGE ON SCHEMA schemaN TO user_x

And then it ALWAYS Locks up on the second command: GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA schemaN TO user_x

I know this because I run this command: SELECT * FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');
and the results always show that grant command as locked.

wait_event_type wait_event query
Lock transactionid GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA schemaN TO user_x

I kill all PID's, delete the user and try again and again it locks in the
same place in the same way.

There's no one else accessing the tables that might have them locked up.

Am I missing something here? Again, loops through PUBLIC schema just fine
but the second user schema dies. And I'm not talking info schema or pg
system schemas, I mean regular user created schema.

Thanks!

Browse pgsql-general by date

  From Date Subject
Next Message Chris Morris 2020-04-13 05:37:09 Re: Log Unique Queries without Params?
Previous Message Andrus 2020-04-13 00:02:15 Re: Which commands are guaranteed to drop role