Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Sat, Apr 23, 2016 at 1:03 AM, Shulgin, Oleksandr <
oleksandr(dot)shulgin(at)zalando(dot)de> wrote:

> On Apr 22, 2016 19:46, "Melvin Davidson" <melvin6925(at)gmail(dot)com> wrote:
> >
> >
> > First, tahnk you for your feedback Alex.
> >
> > "IMO, every time it was conclusively demonstrated that when you consider
> dump/restore semantics, this feature can have exactly zero value if
> implemented *inside pg_catalog*. And it would have to be a pretty invasive
> change (it's not enough to just add the attribute, you also need to touch
> probably a dozen of places where it will be populated or read), so without
> any positive effect it results in negative effect overall."
>
> I find yor lack of proper email quoting skills disturbing...
>
> > Actually, there is no harm in this. If a database is dumped and restored
> to a new database, then there is no need to reset the value of relcreate,
> as it is a copy of the original db.
>
> Yes, but that means that the timestamps must be part of the dump file,
> which means in turn they can be altered before the restore or--if
> implemented as separate commands like ALTER TABLE ... SET TIMESTAMP--at any
> point in time, so there is little to zero value in having the timestamps in
> the first place. This was already discussed in earlier threads.
>
> > If restoring to the same database. then, by definition, it is a data
> only restore, as objects are already in existence.
>
> OK
>
> > There is no need to touch anyplace other than pg_class to capture when
> an object is created, and
> > leaving relcreated NULL for existing objects has no negative effect.
>
> That is one way to think about it, I do not necessarily agree with it.
>
> > "don't buy the example of "DELETE/DROP TABLE" based on relcreated field.
> Do you, by chance, have any other use case?"
> >
> > Yes, it would greatly assist DBA's in tracking down objects created
> outside normal hours/days.
> > eg: SELECT * FROM pg_class WHERE EXTRACT(DOW FROM relcreated) IN (0,6);
>
> I hope you do not intend to drop the objects found in this way without a
> review? What if such an object was created by a user which is in a
> geographically distant location compared to the server and it was still
> Friday there, but it was already Saturday on the server's clock? You likely
> need to know the user name in addition to the timestamp, so you can verify
> the user's reasons.
>
> > I also mentioned that this is already in the catalogs of Oracle and SQL
> Server.
>
> This sort of argument doesn't help the discussion: there should be a good
> reason to add the feature and merely pointing out that others already doing
> that is not a good reason, IMO.
>
> > "Apart from created timestamp would you not like to also know the
> user/role who has created it? What about updates (using ALTER
> TABLE)--would you want to know when that *last* happened and who did that?
> Would you want to know what exactly was altered? Would you want to know
> the history *before* the last update? Finally, if someone drops the table,
> you can say good bye to its pg_catalog records and there's no hope to know
> who did that and when (or if that table has even existed to start with)."
> >
> > At this point, I am only interested in capturing the creation of
> unauthorized objects by rogue users.
>
> If rogue users can create objects in your schema you have bigger problems
> than tables created outside of working hours (and frankly, I don't see any
> harm in that). Can your rogue users also drop tables?
>
> Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.
>
> > If the query shows objects created at suspicious times. A further review
> of the logs would reveal necessary
> > additional info. Yes, it would be good to capture ALTER's also, but that
> complicates things, so I am only
> > looking for a simple, safe change.
>
> If you are going to review the logs anyway, why not just set
> log_statements=ddl and use logs as the source of timestamp data (together
> with user, connection details, etc.)?
>
> > I really wish people would stop focusing on when features will not work
> and consider more of the benefit they will gain from the situations where
> they do work.
> > I also cannot understand why people are paranoid about adding a simple
> nullable timestamp column to a system catalog,
>
> There yet to be found a good reason to do that. No one is going to make
> even a "simple change" just for you to try and see the idea fails in
> practice due to all the other things you didn't think about. But you can
> make such a change for yourself and run a patched version for a while if
> you want--no one can stop you here.
>
> > especially since no one gave any
> > thought to the adverse effect caused by renaming a column ( procpid to
> pid) in pg_stat_activity when going from 9.1 to 9.2. I bet more than a few
> DBA's had to
> > change their scripts that monitored activity.
>
> Well, I didn't participate in that change discussion and I agree it is a
> bit annoying then these view change the column names.
>
> --
> Alex
>

>I find yor lack of proper email quoting skills disturbing..

I am sorry you are disturbed, but thank you for pointing that out. I have
revised my style to make you feel more comfortable.

>Yes, but that means that the timestamps must be part of the dump file,
which means in turn they can be altered before the restore or--if
implemented as separate commands like ALTER TABLE ... SET TIMESTAMP--at any
point in time, so there is little to zero value in having the timestamps in
the first place. This was already discussed in earlier threads.

No, when restoring to a new database, then by definition, object creation
time is when they are loaded into the new database, which is essentially
after the new database is created.

>I hope you do not intend to drop the objects found in this way without a
review?

Of course not.

>> I also mentioned that this is already in the catalogs of Oracle and SQL
Server.
>This sort of argument doesn't help the discussion: there should be a good
reason to add the feature and merely pointing out that others already doing
that is not a good reason, IMO.

Actually, it does. One of the reasons PostgreSQL is growing in popularity
is that many companies are looking to switch away from Oracle and SQL
Server and switch to open source PostgreSQL to save money. In doing so,
consideratopn of the compatibilty and features is of prime concern.

>If rogue users can create objects in your schema you have bigger problems
than tables created outside of working hours
...
>Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.

So you are saying users never be allowed to have access at late hours, or
per business needs create their own sort tables? The president of a company
that works late would have a problem with that. Also, the same applies for
developers in the development database, but sometimes they forget to drop
experimental tables and/or document them. The point is to be able to review
without hindering.

>If you are going to review the logs anyway, why not just set
log_statements=ddl and use logs

That is already done, but a SQL query is faster than a grep, awk and sort
of a large log.

>No one is going to make even a "simple change" just for you to try

I am not asking for a change just for me, I am asking to consider the
benefit to the PostgreSQL community.

>the idea fails in practice due to all the other things you didn't think
about.

Really, please state how this will not work. I believe I have successfully
countered all negative arguments so far.

>you can make such a change for yourself and run a patched version for a
while if you want--no one can stop you here.

I have already stated I cannot. I am not a C coder and attempting to ALTER
the pg_class system catalog causes an error.
I am hoping the PostgreSQL developers
http://www.postgresql.org/community/contributors/ will review and either
say yea or provide a specific reason for rejection.

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.