Which commands are guaranteed to drop role
Hi! Database "mydb" is owned by role "mydb_owner". User "currentuser" tries to delete role "roletodelete" from this database using revoke all on all tables in schema public,firma1 from "roletodelete" cascade; revoke all on all sequences in schema public,firma1 from "roletodelete" cascade; revoke all on database mydb from "roletodelete" cascade; revoke all on all functions in schema public,firma1 from "roletodelete" cascade; revoke all on schema public,firma1 from "roletodelete" cascade; revoke mydb_owner from "roletodelete" cascade; ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from "roletodelete"; GRANT "roletodelete" TO "currentuser"; reassign owned by "roletodelete" to mydb_owner; drop owned by "roletodelete"; drop role "roletodelete"; But got error ERROR: role "roletodelete" cannot be dropped because some objects depend on it DETAIL: privileges for schema public; How to create script which is guaranteed to delete role ? This script already contains: revoke all on schema public,firma1 from "roletodelete" cascade; Why postgres complains that privileges for schema public depend on this role if they are revoked ? How to fix this? Andrus
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
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_eventquery
Lock transactionidGRANT 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!
Log Unique Queries without Params?
I have a local script I've written that will scan a log of PG queries to extract out unique queries without any specific parameter data. For example, if these 2 queries are actually run: SELECT * FROM foo where bar = 1; SELECT * FROM foo where bar = 2; It will capture only: SELECT * FROM foo whee bar = :id; Are there any existing tools that do this already for me? I'm considering setting up a server that can have logs forwarded to it and only logging unique queries like this, but I don't want to build anything that may already exist out there.
Re: Log Unique Queries without Params?
On Sun, Apr 12, 2020 at 6:51 AM Chris Morris wrote: > > I have a local script I've written that will scan a log of PG queries to > extract out unique queries without any specific parameter data. For example, > if these 2 queries are actually run: > > SELECT * FROM foo where bar = 1; > SELECT * FROM foo where bar = 2; > > It will capture only: > > SELECT * FROM foo whee bar = :id; > > Are there any existing tools that do this already for me? I'm considering > setting up a server that can have logs forwarded to it and only logging > unique queries like this, but I don't want to build anything that may already > exist out there. pgbadger (http://pgbadger.darold.net/#about) will do that and much more. Depending on what you want to achieve maybe pg_stat_statements (https://www.postgresql.org/docs/current/pgstatstatements.html) is also an alternative.
Re: Using of --data-checksums
On Fri, Apr 10, 2020 at 04:37:46PM -0400, Stephen Frost wrote: > There's definitely a lot of reasons to want to have the ability to > change an existing cluster. Considering the complications around > running pg_upgrade already, I don't really think that changing the > default of initdb would be that big a hurdle for folks to deal with- > they'd try the pg_upgrade, get a very quick error that the new cluster > has checksums enabled and the old one didn't, and they'd re-initdb the > new cluster and then re-run pg_upgrade to figure out what the next issue > is.. We discussed that a couple of months ago, and we decided to keep that out of the upgrade story, no? Anyway, if you want to enable or disable data checksums on an existing cluster, you always have the possibility to use pg_checksums --enable. This exists in core since 12, and there is also a version on out of core for older versions of Postgres: https://github.com/credativ/pg_checksums. On apt-based distributions like Debian, this stuff is under the package postgresql-12-pg-checksums. -- Michael signature.asc Description: PGP signature
