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!
