Search Postgresql Archives

Looping though schemas to grant access will work in PUBLIC loop iteration but fails on next iteration of user schema at: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!




   

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux