Re: Log Unique Queries without Params?

[Chris Morris]

Thx!

On Sat, Apr 11, 2020 at 11:55 PM Julien Rouhaud  wrote:

> 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.
>

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

[AC Gomez]

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!

Re: Which commands are guaranteed to drop role

[Andrus]

Hi!


You cannot write such a script, but you will have to REVOKE and change ownership

and ALTER DEFAULT PRIVILEGES until no more dependencies on the role exist.

I ran script as superuser. In this case more detailed information appears:

ERROR: role "roletodelete" cannot be dropped because some objects depend on it

DETAIL:  privileges for default privileges on new relations belonging to role 
currentuser in schema public
privileges for default privileges on new relations belonging to role 
currentuser schema firma1

I changed script to 


do $$
   DECLARE r record;
   begin
 for r in select * from pg_views where schemaname IN ('public','firma1')
 loop
   execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| 
quote_ident(r.viewname) || ' from roletodelete cascade';
 end loop;
   end $$;
GRANT roletodelete TO currentuser;
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 all functions in schema public,firma1 from roletodelete cascade;
revoke all on schema public,firma1 from roletodelete cascade;
REVOKE CONNECT ON DATABASE mydb from roletodelete cascade;
revoke all on database mydb from roletodelete cascade;

revoke mydb_owner  from roletodelete  cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from 
roletodelete cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  revoke all ON sequences from 
roletodelete cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  revoke all ON functions from 
roletodelete cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  revoke all ON types from 
roletodelete cascade;
ALTER DEFAULT PRIVILEGES revoke all ON schemas from roletodelete cascade;

REVOKE USAGE ON SCHEMA public,firma1 FROM roletodelete cascade;
reassign owned by roletodelete to mydb_owner;
drop owned by roletodelete;
drop role roletodelete;

In this case it deleted user if was run under superuser postgres.

Non-superuser still cannot delete user using this script. How to allow 
non-superuser to dete user also ?


That is why you are well advised not to grant permissions to a role that you
plan to drop.


Role represents person. ODBC connection is used. Person rights should be 
restricted in database in this case.

How to simplify this script so that user will always deleted ?
Maybe some parts of script are not necessary.

Why postgres does not have simple command like 


drop role roletodelete reassign owned to currentuser cascade

but requires 25-line script for this.
Some parts of this script will not work in Postgres 9.0 probably. How to add 9.0+ support for it. 
Revoking privileges from view in not required in earlier releases.


Andrus.

Re: Using of --data-checksums

[Michael Paquier]

On Sun, Apr 12, 2020 at 10:23:24AM -0400, Tom Lane wrote:
> Magnus Hagander  writes:
>> And FWIW, I do think we should change the default. And maybe spend some
>> extra effort on the message coming out of pg_upgrade in this case to make
>> it clear to people what their options are and exactly what to do.
> 
> Is there any hard evidence of checksums catching problems at all?
> Let alone in sufficient number to make them be on-by-default?

I don't know if that's a sufficient number, but I have dealt with
corruption cases on virtual environments where these have been really
essential to find out proof that the origin of the problem was not
Postgres because those bugs created wild and incorrect block
overwrites.  With the software stack getting more complicated, making
them the default would make sense IMO.  Now the case of upgrades is
more tricky than it is, no?  There is a copy of the file so we may be
able to do a block-to-block copy and update of the checksum, but you
cannot do that with the --link mode.
--
Michael


signature.asc
Description: PGP signature

Re: Which commands are guaranteed to drop role

[Laurenz Albe]

On Sun, 2020-04-12 at 00:25 +0300, Andrus wrote:
> User "currentuser"  tries to delete role "roletodelete" from this database 
> using 
> 
> 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 ?

You cannot write such a script, but you will have to REVOKE and change ownership
and ALTER DEFAULT PRIVILEGES until no more dependencies on the role exist.

That is why you are well advised not to grant permissions to a role that you
plan to drop.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Using of --data-checksums

[Tom Lane]

Magnus Hagander  writes:
> And FWIW, I do think we should change the default. And maybe spend some
> extra effort on the message coming out of pg_upgrade in this case to make
> it clear to people what their options are and exactly what to do.

Is there any hard evidence of checksums catching problems at all?
Let alone in sufficient number to make them be on-by-default?

regards, tom lane

Re: Using of --data-checksums

[Magnus Hagander]

On Sun, Apr 12, 2020 at 8:05 AM Michael Paquier  wrote:

> 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.
>

The fact that this tool exists, and then in the format of pg_checksums
--disable, I think is what makes the argument to turn on checksums by
default possible. Because it's now very easy and fast to turn it off even
if you've accumulated sizable data in your cluster. (Turning it on in this
case is easy, but not fast).

And FWIW, I do think we should change the default. And maybe spend some
extra effort on the message coming out of pg_upgrade in this case to make
it clear to people what their options are and exactly what to do.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Using of --data-checksums

[Michael Paquier]

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