Re: [HACKERS] Database schema diff
Few years ago I developed a tool called fsgateway ( https://github.com/mk8/fsgateway) that show metadata (table, index, sequences, view) as normal files using fuse. In this way to yout can get differences between running db instance using diff, meld or what do you prefear. Unfortunally at the moment not all you need is supported, yet. Best regards P.S. I think that this is the wrong list for questione like this one. On Wed, Oct 14, 2015 at 10:26 AM, Shulgin, Oleksandr < oleksandr.shul...@zalando.de> wrote: > On Tue, Oct 13, 2015 at 5:48 PM, Michal Novotny < > michal.novo...@trustport.com> wrote: > >> Hi guys, >> >> I would like to ask you whether is there any tool to be able to compare >> database schemas ideally no matter what the column order is or to dump >> database table with ascending order of all database columns. >> >> For example, if I have table (called table) in schema A and in schema B >> (the time difference between is 1 week) and I would like to verify the >> column names/types matches but the order is different, i.e.: >> >> Schema A (2015-10-01) | Schema B (2015-10-07) >> | >> id int| id int >> name varchar(64) | name varchar(64) >> text text | description text >> description text | text text >> >> Is there any tool to compare and (even in case above) return that both >> tables match? Something like pgdiff or something? >> >> This should work for all schemas, tables, functions, triggers and all >> the schema components? >> > > I've used pg_dump --split for this purpose a number of times (it requires > patching pg_dump[1]). > > The idea is to produce the two database's schema dumps split into > individual files per database object, then run diff -r against the schema > folders. This worked really well for my purposes. > > This will however report difference in columns order, but I'm not really > sure why would you like to ignore that. > > -- > Alex > > [1] > http://www.postgresql.org/message-id/AANLkTikLHA2x6U=q-t0j0ys78txhfmdtyxjfsrsrc...@mail.gmail.com > >
Re: [HACKERS] pg_cancel_backend by non-superuser
Hi Greg 2011/12/13 Greg Smith g...@2ndquadrant.com: On 12/11/2011 05:29 PM, Torello Querci wrote: I will try to adjust the patch and submit for the next Commit Fest if this is ok for you. I don't think we'll need this, it will take a bit to explain why though. First, thanks for returning this topic to discussion and keeping up with all the controversy around it. You said back in February this was your first post here, and I doubt you expected that 10 months later this would still be active and argued over. The fact that you're still here and everyone knows your name now is itself an accomplishment, many people just give up on their submission ideas under far less negative feedback. Why. I need one feature, can spend some time to try to get it and I try. This is only way to lean that I know. I just took a long look at all three of the submissions in this area we've gotten. The central idea that made yours different was making the database owner the person allowed to cancel things. That hadn't been suggested as a cancellation requisite before that I know of, and this code may wander in that direction one day. It's just a bit too much to accept right now. You seem to need that specific feature for your environment. If that's the case, you might want to develop something that works that way, but handles the concerns raised here. The fact that it's not acceptable for a database owner to cancel a superuser query is the biggest objection, there were some others too. Ultimately it may take a reworking of database permissions to really make this acceptable, which is a larger job than I think you were trying to get involved with. Probably you have right :( Unfortunately, when I look at the new spec we have now, I don't see anything from what you did that we can re-use. It's too specific to the owner-oriented idea. The two other patches that have been submitted both are closer to what we've decided we want now. What I'm going to do here is mark your submission returned with feedback. Again no problem. The only thing that I need (not only me obviusly) is give the permission to one or more users to kill session and query owned by other users. Have a kind of ACL where is specify who can kill and which is the right way. My problem is related to production environment where an application server access the database server and I am the database owner, not the DBA. So I need to kill the application server sessions (again I not the root of application server so I not able to stop and restart it). I hope to explain my scenario if not before. Rather than wait for something new from you, I'm going to review and rework the other two submissions. That I can start on right now. It's taken so long to reach this point that I don't want to wait much longer for another submission here, certainly not until over a month from now when the next CF starts. We need to get the arguments around a new version started earlier than that. Thanks for offering to work on this more, and I hope there's been something about this long wandering discussion that's been helpful to you. As I said, you did at least make a good first impression, and that is worth something when it comes to this group. Thanks Greg. I hope to meet you at Fosdem if you wil go there. Best Regards, Torello -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_cancel_backend by non-superuser
2011/12/6 Magnus Hagander mag...@hagander.net: On Sun, Oct 2, 2011 at 23:32, Tom Lane t...@sss.pgh.pa.us wrote: Noah Misch n...@leadboat.com writes: On Sun, Oct 02, 2011 at 06:55:51AM -0400, Robert Haas wrote: On Sat, Oct 1, 2011 at 10:11 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: I see. What about passing this decision to DBA? I mean a GUC can_cancel_session = user, dbowner (default is '' -- only superuser). You can select one or both options. This GUC can only be changed by superuser. Or how about making it a grantable database-level privilege? I think either is overkill. You can implement any policy by interposing a SECURITY DEFINER wrapper around pg_cancel_backend(). I'm with Noah on this. If allowing same-user cancels is enough to solve 95% or 99% of the real-world use cases, let's just do that. There's no very good reason to suppose that a GUC or some more ad-hoc privileges will solve a large enough fraction of the rest of the cases to be worth their maintenance effort. In particular, I think both of the above proposals assume way too much about the DBA's specific administrative requirements. +1. Torello, are you up for updating your patch to do this, for now? If not, I'll be happy to create an updated patch that does just this, but since you got started on it... Sorry for the long delay. I will try to adjust the patch and submit for the next Commit Fest if this is ok for you. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
2011/11/15 Pavel Stehule pavel.steh...@gmail.com: Hello, there is a request on enhancing of pg_backup to produce a conditional DROPs. A reason for this request is more simple usage in very dynamic production - cloud BI solution. pg_backup can have a new option --conditional-drops and then pg_dump will produce a DROP IF EXISTS statements instead DROP statements. Ideas, comments? I think that if there is other way to get the same result in other way is better to use it without add new options. In this case, if you are on unix environment, I suppose that you can use external batch to manipulate the output file, like sed I suppose. Obviusly this is my personal opinion. Best Regards Torello Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_cancel_backend by non-superuser
I like this idea +1 Il giorno 02/ott/2011 12:56, Robert Haas robertmh...@gmail.com ha scritto: On Sat, Oct 1, 2011 at 10:11 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: On 01-10-2011 17:44, Daniel Farina wrote: On Fri, Sep 30, 2011 at 9:30 PM, Tom Lanet...@sss.pgh.pa.us wrote: ISTM it would be reasonably non-controversial to allow users to issue pg_cancel_backend against other sessions logged in as the same userID. The question is whether to go further than that, and if so how much. In *every* case -- and there are many -- where we've had people express pain, this would have sufficed. I see. What about passing this decision to DBA? I mean a GUC can_cancel_session = user, dbowner (default is '' -- only superuser). You can select one or both options. This GUC can only be changed by superuser. Or how about making it a grantable database-level privilege? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_cancel_backend by non-superuser
2011/10/1 Tom Lane t...@sss.pgh.pa.us: Daniel Farina dan...@heroku.com writes: This patch would appear(?) to have languished: https://commitfest.postgresql.org/action/patch_view?id=541 I'd really like to see it included. In the last comments of the review, there seem to be problems in *terminate* backend, but even just pg_cancel_backend as non-superuser would be just a huge improvement. What are the things blocking non-superuser pg_cancel_backend from being accepted? I think the reason the patch stalled is that we have not got consensus on how far to extend the conditions under which these operations should be allowed. For instance, in the last comment attached to that commitfest entry, Noah alleges that a non-superuser database owner should be allowed to kill a superuser's session, if it's connected to his database. My reaction to that is somewhere between no and hell no; IMO superusers can mess up non-superusers, never vice versa. If I recall the discussion correctly, there were other points of contention too. Hi, the original patch allow only for the DB Owner to kill sessions owner by other users. This because in real world I have some production database where I'm not the DBA, but only the DB owner. I think that is not a good idea that a normal users is able to kill session from the same user because, unfortunally, in some real environment there are a lots of application that need to access to the same database and the same user is used. I know that is not a good practise but it is on the field For this reason I suppose that allow only to DB onwer to kill other sessions it is a good compromize between functionality and security, but is my personal opinion ... I don't think we need more coding right now ... we need somebody to write a spec that everyone can agree to. ISTM it would be reasonably non-controversial to allow users to issue pg_cancel_backend against other sessions logged in as the same userID. The question is whether to go further than that, and if so how much. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_terminate_backend and pg_cancel_backend by not administrator user
2011/6/2 Noah Misch n...@leadboat.com: On Wed, Jun 01, 2011 at 10:26:34PM -0400, Josh Kupershmidt wrote: On Wed, Jun 1, 2011 at 5:55 PM, Noah Misch n...@leadboat.com wrote: On Sun, May 29, 2011 at 10:56:02AM -0400, Josh Kupershmidt wrote: Looking around, I see there were real problems[1] with sending SIGTERM to individual backends back in 2005 or so, and pg_terminate_backend() was only deemed safe enough to put in for 8.4 [2]. So expanding pg_terminate_backend() privileges does make me a tad nervous. The documentation for the CREATE USER flag would boil down to omit this flag only if you're worried about undiscovered PostgreSQL bugs in this area. ?I'd echo Tom's sentiment from the first thread, In any case I think we have to solve it, not create new mechanisms to try to ignore it. I do agree with Tom's sentiment from that thread. But, if we are confident that pg_terminate_backend() is safe enough to relax permissions on, then I take it you agree we should plan to extend this power to all users? Yes; that's what I was trying to say. Having thought about this some more, I do now see a risk. Currently, a SECURITY DEFINER function (actually any function, but that's where it matters) can trap query_canceled. By doing so, the author can ensure that only superusers and crashes may halt the function during a section protected in this way. One might use it to guard a series of updates made over dblink. pg_terminate_backend() breaks this protection. I've never designed something this way; it only suffices when you merely sort-of-care about transactional integrity. Perhaps it's an acceptable loss for this feature? And if so, is this patch a good first step on that path? Understand that the pg_terminate_backend() is able to kill process that need not to be killed. I suppose that looking inside the internal postgreql table in order to not allow a normal db owner to kill a superuser connection can avoid this problem? Yes. Reading through those old threads made me realize this patch would give database owners the ability to kill off autovacuum workers. Seems like we'd want to restrict that power to superusers. Would we? ?Any old user can already stifle VACUUM by holding a transaction open. This is true, though it's possible we might at some point want a backend process which really shouldn't be killable by non-superusers (if vacuum/autovacuum isn't one already.) Actually, I could easily imagine a superuser running an important query on a database getting peeved if a non-superuser were allowed to cancel/terminate his queries. That's really a different level of user isolation than we have. If your important query runs on a database owned by someone else, calls functions owned by someone else, or reads tables owned by someone else, you're substantially at the mercy of those object owners. That situation probably is unsatisfactory to some folks. Adding the possibility that a database owner could cancel your query seems like an extension of that codependency more than a new exposure. If I am the database owner I need to be able to manage my DB. Ok for superuser connection (and internal administrative process like autovacuum) I am the developer, not the DBA, so sometimes, when I wrong something, I need to kill my session if I wrong something Can we suppose, in a more generic case, that an user can kill connection only from the same user even if this is not the database owner? Best Regards, Torello -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_terminate_backend and pg_cancel_backend by not administrator user
Hi,
this is the first time that I post here, so if I wrong please don't kill me ...
I see that pg_terminate_backend and pg_cancel_backend can be execute
only by admin users.
This approach seems to be too restrictive in a lots of real situation.
In dept, I have a situation where it is created one database machine
for all the postgresql database.
This database machine is managed by IT staff that have created two
user for each application.
One user is the owner db user that create, drop, grant on this db,
while the other user is the application db.
In this situation I (the developer) not able to disconnect any client
and stop any high weight queries.
Unfortunately the application run on application server that is
manager, again, by IT staff and I not have the right to stop it.
I suppose that give the right to the owner db user to terminate or
cancel other session connected to the database which it is owner is a
good thing.
I not see any security problem because this user can cancel or
terminate only the session related with the own database,
but if you think that this is a problem, a configuration parameter can be used.
Of course I can create a function with admin right that do the same
thing but the IT staff need to install, configure, and give the right
grant.
So, I suppose, that this can to be only a workaround, not the solution.
Sorry for my English.
I attach a path for this
Best Regards, Torello
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 5bda4af..5327447 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -33,6 +33,7 @@
#include storage/procarray.h
#include utils/builtins.h
#include tcop/tcopprot.h
+#include pgstat.h
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
@@ -75,9 +76,33 @@ static bool
pg_signal_backend(int pid, int sig)
{
if (!superuser())
- ereport(ERROR,
-(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg(must be superuser to signal other server processes;
+ {
+bool haveRight = false;
+PgBackendStatus *backend;
+
+ /* If the user not is the superuser, need to be the db owner. */
+ if (pg_database_ownercheck(MyDatabaseId, GetUserId())) {
+
+/* Check for the specify backend in the stat info table */
+int nBackend = pgstat_fetch_stat_numbackends();
+int i;
+for (i = 1; i=nBackend; ++i) {
+backend = pgstat_fetch_stat_beentry(i);
+if (backend-st_procpid == pid) {
+if (backend-st_databaseid == MyDatabaseId)
+haveRight = true;
+break;
+}
+}
+}
+
+if (!haveRight)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+(errmsg(must be superuser or database destination owner to signal other server processes;
+ }
+
+
if (!IsBackendPid(pid))
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
