Re: ERROR: could not find pathkey item to sort
fyi. The issue was fixed with this PR. https://github.com/adjust/parquet_fdw/pull/60 Thanks, Vijay On Fri, Oct 28, 2022 at 7:59 PM Vijaykumar Sampat Jain < vijaykumar.sampat.j...@adjust.com> wrote: > On Fri, Oct 28, 2022 at 7:15 PM Tom Lane wrote: > > > > > > I think it's most likely a bug in parquet_fdw. We have tests of similar > > cases in postgres_fdw, and they work fine. > > > Thanks Tom. > I'll lookup the net to see how similar errors were solved in the past > to understand what was being sent to postgres that it did not like :) >
Looking for onchain EVM Postgres port
Hello, it’s Myles over at https://CanaDAO.org. We’re planning to port and adapt https://liquidfeedback.org on top of the Ethereum Virtual Machine. We’re looking for a full PostGres port on top of Radix/Notoros (running in a write once read many paradigm of course, due to on chain immutability). Are you aware of a port of this kind? https://www.public-software-group.org/mercurial/liquid_feedback_core/file/82387194519b/core.sql Thanks, Sent from my iPhone
Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
First, I wanted to send this to the developers (hackers) list. But the instructions are clear that initial posts don't go there (try elsewhere first). So playing by the rules here... *Background:* We have our team of Windows developers, leveraging PSQL. But honestly, it feels crippled after using PSQL in Linux for any length of time. Losing auto-complete sucks (multi-line query recall/editing is lost as well). In researching this problem, it appears that the decision was made like 17yrs ago, when windows did not have a realistic "terminal" type interface. Assuming we target Windows 8.1 or higher, I believe this goes away. We are currently "testing" a solution to uses readline in windows, with a few modifications to make it work properly (some escape handling, custom termcaps, etc). Our goal is selfish... We want to fix PSQL for TAB completion for ourselves, first. But this begs the question. Is the PG community at large interested in potentially addressing this issue? This will SIGNIFICANTLY increase the effort on our end... But we would like to hear back first, because this is GNU and modifications could cause "hiccups". Also, we have bantered around the idea that this could be fixed as a complete "Windows Readline" solution, basically plug-compatible and that could help even more projects! But we do NOT understand what would be required to meet the requirements of PG Devs/Licensing, or your level of interest in having this solved. (We are on the cusp of having it working, but TOTALLY a proof of concept). *GOAL of this email:* What we are looking for is a simple bullet point list of what would make such a contribution acceptable. And how far should we get through that list on our own, before getting some help, especially from the build teams? [Our goal would be an NEW Config type flag: READLINE_FOR_WINDOWS (you guys name it, and control the default setting)] And you may well want a new switch -y (yes-readline-windows) or some such for PSQL, so it could always be off, unless set on for a session for the first couple of years? Thanks!
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
Kirk Wolak writes: > We have our team of Windows developers, leveraging PSQL. But honestly, > it feels crippled after using PSQL in Linux for any length of time. Losing > auto-complete sucks (multi-line query recall/editing is lost as well). > In researching this problem, it appears that the decision was made like > 17yrs ago, when windows did not have a realistic "terminal" type > interface. Assuming we target Windows 8.1 or higher, I believe this goes > away. It'd certainly be nice if we could use Readline on Windows. I do not think we want to buy into maintaining our own fork of Readline, if that's what you're trying to suggest. If it "just works" now, that'd be great. Otherwise, maybe you should be speaking to the Readline maintainers about what changes are needed in it? regards, tom lane
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
On Mon, Nov 21, 2022 at 4:58 PM Tom Lane wrote: > Kirk Wolak writes: > > We have our team of Windows developers, leveraging PSQL. But honestly, > > it feels crippled after using PSQL in Linux for any length of time. Losing > > auto-complete sucks (multi-line query recall/editing is lost as well). > > > In researching this problem, it appears that the decision was made like > > 17yrs ago, when windows did not have a realistic "terminal" type > > interface. Assuming we target Windows 8.1 or higher, I believe this goes > > away. > > It'd certainly be nice if we could use Readline on Windows. I do not > think we want to buy into maintaining our own fork of Readline, if that's > what you're trying to suggest. If it "just works" now, that'd be great. > Otherwise, maybe you should be speaking to the Readline maintainers > about what changes are needed in it? FWIW, I've been using https://github.com/arangodb/linenoise-ng for Linux and Windows, but that's C++ and no longer supported (but worked well enough for me). The main https://github.com/antirez/linenoise itself does not build as-is on Windows still, I believe. --DD
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
On Mon, Nov 21, 2022 at 10:58 AM Tom Lane wrote: > It'd certainly be nice if we could use Readline on Windows. I do not > think we want to buy into maintaining our own fork of Readline, if that's > what you're trying to suggest. If it "just works" now, that'd be great. > Okay, we should know shortly if it works and to what extent. We will take the approach that if we don't need to maintain a fork we are fine. [The latter could make it work only for English, not sure] FYI, On Mon, Nov 21, 2022 at 11:01 AM Dominique Devienne wrote: > FWIW, I've been using https://github.com/arangodb/linenoise-ng for Linux and Windows, I've look at linenoise and without tab/autocomplete it's not worth the effort, IMO. But I appreciate the pointer... regards
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
> adrian.kla...@aklaver.com wrote: > >> karsten.hilb...@gmx.net: >> >>> adrian.kla...@aklaver.com wrote: >>> b...@yugabyte.com Thanks to all who offered their views on my question. It seems that different people will reach different conclusions. I’ll take this as permission to reach my own conclusion. >>> >>> Not sure why you think you need permission to take whatever action you >>> desire on a database whose only usage stipulation is that you maintain a >>> copy of the license. >> >> Adrian, I think Bryn's speaking metaphorically there. > > It is hard to tell with him. He makes much of his Oracle background and I > think misses an overlord that lays down the rules. I didn’t mean to speak metaphorically. But I made a bad word choice when I used “permission”. A couple of turns back, David Johnston wrote this: > there is no good blanket recommendation to give to someone else as to how > their [security] policy should be written. Security, especially of this > sort, needs to be architected. And some time ago, in a different thread, he wrote this: > You only need superuser once to configure the system in such a way, through > role and grants and possibly default permissions, that from then on most > everything an application user would want to do can be done by the role(s) > you have created. That second quote reads like a recommendation—which puts it at odds with the first quote. (But doubtless I’m reading it wrongly.) Then there’s this (from the doc): > It is good practice to create a role that has the CREATEDB and CREATEROLE > privileges, but is not a superuser, and then use this role for all routine > management of databases and roles. This approach avoids the dangers of > operating as a superuser for tasks that do not really require it. That, too, reads like a recommendation that intends to inform a security policy. But, I suppose, one could argue that saying something “is good practice” is very different from making a recommendation. Consider this wording. It also uses “good practice”. « It is good practice to limit the number of superuser roles that exist in a cluster to exactly one: the inevitable bootstrap superuser. This recognizes the fact that, once the initial configuration of a cluster has been done immediately after its creation (which configuration is done while still in self-imposed single-user mode), there are then very few, and infrequent, tasks that require the power of the superuser role. » Nobody supports it! I’m puzzled why the good practice statement about a role with the CREATEDB and CREATEROLE attributes earns a place in the doc while nobody at all is prepared to make a practice statement about how many superusers is good. I’d like very much to understand the critical parts that I’m missing of the essential mental model in this general space.
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
On Mon, Nov 21, 2022 at 6:12 PM Kirk Wolak wrote: > On Mon, Nov 21, 2022 at 11:01 AM Dominique Devienne > wrote: > > FWIW, I've been using https://github.com/arangodb/linenoise-ng for Linux > > and Windows, > > I've look at linenoise and without tab/autocomplete it's not worth the > effort, IMO. Not sure to understand... Linenoise* has completion hooks. Completion is something done in the app, not readline/linenoise. Only the app has the context to know what to complete the text with. But perhaps I'm missing something? --DD https://github.com/arangodb/linenoise-ng/blob/master/include/linenoise.h
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
On 11/21/22 9:40 AM, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: Then there’s this (from the doc): It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require it. That, too, reads like a recommendation that intends to inform a security policy. But, I suppose, one could argue that saying something “is good practice” is very different from making a recommendation. Consider this wording. It also uses “good practice”. « It is good practice to limit the number of superuser roles that exist in a cluster to exactly one: the inevitable bootstrap superuser. This recognizes the fact that, once the initial configuration of a cluster has been done immediately after its creation (which configuration is done while still in self-imposed single-user mode), there are then very few, and infrequent, tasks that require the power of the superuser role. » Nobody supports it! I went back through the thread and don't anywhere when you made the above statement, correct me if I am wrong. In that case there was nothing to support or not support until now. What people where responding to the title of the thread: "Seeking practice recommendation: is there ever a use case to have two or more superusers?" That is a different ask. I’m puzzled why the good practice statement about a role with the CREATEDB and CREATEROLE attributes earns a place in the doc while nobody at all is prepared to make a practice statement about how many superusers is good. I’d like very much to understand the critical parts that I’m missing of the essential mental model in this general space. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
On Mon, Nov 21, 2022 at 10:40 AM Bryn Llewellyn wrote: > > Consider this wording. It also uses “good practice”. > > « > It is good practice to limit the number of superuser roles that exist in a > cluster to exactly one: the inevitable bootstrap superuser. This recognizes > the fact that, once the initial configuration of a cluster has been done > immediately after its creation (which configuration is done while still in > self-imposed single-user mode), there are then very few, and infrequent, > tasks that require the power of the superuser role. > » > > Nobody supports it! > > I’m puzzled why the good practice statement about a role with the CREATEDB > and CREATEROLE attributes earns a place in the doc while nobody at all is > prepared to make a practice statement about how many superusers is good. > I’d like very much to understand the critical parts that I’m missing of the > essential mental model in this general space. > My policy would be that no one is supposed to login to the database cluster using the postgres role. Period. Upon initialization whomever is responsible for creating the cluster gets their personal user credentials installed into the cluster as superuser and from that point on never uses postgres. They will, however, in the interest of business continuity, create additional superusers for any others who share the superuser responsibility. In short, there is very little room to argue against the principle of least privilege. I don't see where that principle supports "only have one superuser" nor does it seem better than another security principle: "everyone must have their own credentials". I suppose the suggestion I would be willing to consider is: only have the postgres superuser, never grant superuser to login roles explicitly, instead if those persons require superuser grant them membership in the postgres role. Except I don't think that actually works in a desirable way today. Having multiple roles in service of least-privilege while retaining users must use personal login credentials is my suggested starting point absent some more improvements in the authorization systems (or a better understanding of existing ones by your truly). So yes I, like everyone else, is going to end up forming their own generalities. Ideas that I cannot wholly discredit as bad, but that don't fit into my generality, get the "if the specific circumstances warrant it" treatment. My own presuppositions ultimately should get the same treatment by whomever is implementing such policies. David J.
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com: >> >> Consider this wording. It also uses “good practice”. >> « >> It is good practice to limit the number of superuser roles that exist in a >> cluster to exactly one: the inevitable bootstrap superuser. This recognizes >> the fact that, once the initial configuration of a cluster has been done >> immediately after its creation (which configuration is done while still in >> self-imposed single-user mode), there are then very few, and infrequent, >> tasks that require the power of the superuser role. >> » >> >> Nobody supports it! > > I went back through the thread and don't anywhere when you made the above > statement, correct me if I am wrong. In that case there was nothing to > support or not support until now. What people where responding to the title > of the thread: > >> "Seeking practice recommendation: is there ever a use case to have two or >> more superusers?" > > That is a different ask. I didn't mean to say that I'd already written the sentence that starts "It is good practice to limit the number of superuser roles that exist in a cluster...". Sorry if I gave that impression. It was just a strawman version of what I practice recommendation might look like that used the same general wording style as the one about "a role that has the CREATEDB and CREATEROLE".
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
On 11/21/22 11:46, Bryn Llewellyn wrote: Nobody supports it! I went back through the thread and don't anywhere when you made the above statement, correct me if I am wrong. In that case there was nothing to support or not support until now. What people where responding to the title of the thread: "Seeking practice recommendation: is there ever a use case to have two or more superusers?" That is a different ask. I didn't mean to say that I'd already written the sentence that starts "It is good practice to limit the number of superuser roles that exist in a cluster...". Sorry if I gave that impression. It was just a strawman version of what I practice recommendation might look like that used the same general wording style as the one about "a role that has the CREATEDB and CREATEROLE". Still, why the "Nobody supports it!" statement for a recommendation that only appeared at the same time? I for one have a poor record of mind reading and/or predicting the future:) -- Adrian Klaver adrian.kla...@aklaver.com
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Consider this wording. It also uses “good practice”. >> >> « >> It is good practice to limit the number of superuser roles that exist in a >> cluster to exactly one: the inevitable bootstrap superuser. This recognizes >> the fact that, once the initial configuration of a cluster has been done >> immediately after its creation (which configuration is done while still in >> self-imposed single-user mode), there are then very few, and infrequent, >> tasks that require the power of the superuser role. >> » >> >> Nobody supports it! > > ...why the "Nobody supports it!" statement for a recommendation that only > appeared at the same time? I for one have a poor record of mind reading > and/or predicting the future:) Here’s what I wrote in the post that started this thread, archived at this URL: https://www.postgresql.org/message-id/290ef7b8-d150-4ae1-8ffe-a38912cd1...@yugabyte.com > The implication is clear: you should allow a cluster to have just a single > superuser, the inevitable bootstrap superuser, and you should think very > carefully indeed before ever starting a session as this role because of the > risks that doing so brings. Rather, you should realize that there are hardly > any tasks that cannot be carried out by an appropriately configured role with > "nosuperuser”. The essential content of each (what I wrote in my opening post and what stands between « ... » above) is the same: allow maximum one superuser. Each is a strawman. And, as such, carries its own implicit invitation for challenge or support. The outcome was all challenge and no support. I don’t know why observing that this was the outcome has, itself, become controversial. In fact, David Johnston did unequivocally challenge my strawman a couple of turns back, thus: > no one is supposed to login to the database cluster using the postgres role. > Period. Upon initialization whomever is responsible for creating the cluster > gets their personal user credentials installed into the cluster as superuser > and from that point on never uses postgres. That’s actionable advice. I mentioned that I had implemented that scheme and then, later, abandoned it. I can easily re-implement it. Because PG allows a cluster to have as many superusers as you please, and because any one of these can create or drop another, any convention in this space needs some extra mechanisms to enforce it.. I believe that the fact that a superuser's ability to start a session can be limited by what the "hba_file" says is critical here—together with the fact that the ability to edit this file is governed by the regime of O/S users and file privileges. Maybe this is the key to the effectively tamper-proof implementation of the scheme that David recommends. (Having said this, there's always the "set role" backdoor.) There's also the caveat that a "drop" attempt by a superuser for a single object owned by the bootstrap superuser (say, the "pg_catalog.pg_terminate_backend()" function) in some database causes an error with the message "cannot drop function... because it is required by the database system". (At least, this is what my tests have shown with a smallish sample of drop targets.) This seems to be a Very Good Thing. But the fact that this is the behavior makes me wonder what harm can be done by a session that authorizes as the bootstrap superuser that cannot be done by a session that authorizes as a regular superuser. I'll try to find out.
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
On Mon, Nov 21, 2022 at 4:05 PM Bryn Llewellyn wrote: > > I believe that the fact that a superuser's ability to start a session can > be limited by what the "hba_file" says is critical here—together with the > fact that the ability to edit this file is governed by the regime of O/S > users and file privileges. Maybe this is the key to the effectively > tamper-proof implementation of the scheme that David recommends. (Having > said this, there's always the "set role" backdoor.) > If you are worried about back-doors here you gave the wrong people superuser. That may be unavoidable, but this scheme really isn't about bullet-proofing security. It's about ease of administration and knowing just who all has permission do what on a server by inspecting its role table. Yes, you should lock-down pg_hba.conf to avoid other people without superuser from being able to easily hack into the system using one of these accounts (admittedly, a decent reason to limit how many there are, but all of them should be equally/maximally secure so it isn't that strong an argument). David J.
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
On 22 Nov 2022, at 10:05, Bryn Llewellyn wrote: > Because PG allows a cluster to have as many superusers as you please, and > because any one of these can create or drop another, any convention in this > space needs some extra mechanisms to enforce it.. > > … effectively tamper-proof implementation of the scheme … > Somewhat interesting thread so far but seems to be asking more than one question — Q1. Is there ever a use case to have two or more superusers? Answer: Yes, but entirely depending on the use case. Q2. [IMPLIED] How to make the database tamper-proof since at least one superuser is unavoidable? Answer: Not possible, ever — see below. It is best to consider a database security system’s design objectives to be tamper proof from the outside (ie., general client access perspective), and tamper evident from within the database. As far as the server is concerned one person’s superuser tampering is another person’s maintenance. There is no way to configure login credentials to prevent malicious or mistaken changes when you need to have the occasional superuser role that can repair a serious fault or process a system upgrade. If an upgrade or repair can be anticipated it should already be done, the superuser is needed for the things that were not expected or too complex to pre-automate. AFAICT minimal tamper evident criteria will include logs being kept of changes made and these on a system the database superuser cannot change. At worst the logs will still have recorded when they were turned off. And the logs should have recorded the credentials used to assume the superuser role. After that it is basic business management — was the person acting badly, were the credentials stolen, what damage was done, and are the backups useful? The full security analysis is complex and searches around “threat landscape” will help widen the perspective. The question becomes one of identifying what is possible to prevent, what can only be mitigated, and what can only be repaired after the fact. Database security is a lot more complicated than just trying to restrict the superuser role. Gavan Schneider —— Gavan Schneider, Sodwalls, NSW, Australia Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong. — H. L. Mencken, 1920
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
On 11/21/22 15:05, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: ...why the "Nobody supports it!" statement for a recommendation that only appeared at the same time? I for one have a poor record of mind reading and/or predicting the future:) Here’s what I wrote in the post that started this thread, archived at this URL: https://www.postgresql.org/message-id/290ef7b8-d150-4ae1-8ffe-a38912cd1...@yugabyte.com The implication is clear: you should allow a cluster to have just a single superuser, the inevitable bootstrap superuser, and you should think very carefully indeed before ever starting a session as this role because of the risks that doing so brings. Rather, you should realize that there are hardly any tasks that cannot be carried out by an appropriately configured role with "nosuperuser”. The essential content of each (what I wrote in my opening post and what stands between « ... » above) is the same: allow maximum one superuser. Each is a strawman. And, as such, carries its own implicit invitation for challenge or support. The outcome was all challenge and no support. I don’t know why observing that this was the outcome has, itself, become controversial. You must be reading a different thread. What I saw in the replies was people answering '...is there ever a use case to have two or more superusers?' with, maybe but in the end it is up to you to decide what works in your case. In fact, David Johnston did unequivocally challenge my strawman a couple of turns back, thus: no one is supposed to login to the database cluster using the postgres role. Period. Upon initialization whomever is responsible for creating the cluster gets their personal user credentials installed into the cluster as superuser and from that point on never uses postgres. You left out the preface to the above, 'My policy would be that ...` And the equivocal additions later in the post: "I suppose the suggestion I would be willing to consider is: only have the postgres superuser, never grant superuser to login roles explicitly, instead if those persons require superuser grant them membership in the postgres role." and " So yes I, like everyone else, is going to end up forming their own generalities. Ideas that I cannot wholly discredit as bad, but that don't fit into my generality, get the "if the specific circumstances warrant it" treatment. My own presuppositions ultimately should get the same treatment by whomever is implementing such policies." That’s actionable advice. I mentioned that I had implemented that scheme and then, later, abandoned it. I can easily re-implement it. Because PG allows a cluster to have as many superusers as you please, and because any one of these can create or drop another, any convention in this space needs some extra mechanisms to enforce it.. I believe that the fact that a superuser's ability to start a session can be limited by what the "hba_file" says is critical here—together with the fact that the ability to edit this file is governed by the regime of O/S users and file privileges. Maybe this is the key to the effectively tamper-proof implementation of the scheme that David recommends. (Having said this, there's always the "set role" backdoor.) There's also the caveat that a "drop" attempt by a superuser for a single object owned by the bootstrap superuser (say, the "pg_catalog.pg_terminate_backend()" function) in some database causes an error with the message "cannot drop function... because it is required by the database system". (At least, this is what my tests have shown with a smallish sample of drop targets.) This seems to be a Very Good Thing. But the fact that this is the behavior makes me wonder what harm can be done by a session that authorizes as the bootstrap superuser that cannot be done by a session that authorizes as a regular superuser. I'll try to find out. Superuser is superuser, there is no magic associated with the bootstrap superuser. FYI, the answer is won't make a difference. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
On Mon, Nov 21, 2022 at 5:30 PM Adrian Klaver wrote: > On 11/21/22 15:05, Bryn Llewellyn wrote: > > > > In fact, David Johnston did unequivocally challenge my strawman a couple > of turns back, thus: > > > > > And the equivocal additions later in the post: > Yeah, even when I try to be unequivocal I tend to fail...Devil's Advocate is another role I enjoy playing. > > > > There's also the caveat that a "drop" attempt by a superuser for a > single object owned by the bootstrap superuser (say, the > "pg_catalog.pg_terminate_backend()" function) in some database causes an > error with the message "cannot drop function... because it is required by > the database system". (At least, this is what my tests have shown with a > smallish sample of drop targets.) There are some protections in place to prevent the superuser from shooting themselves in the foot accidentally. They are readily disabled though, through a simple SET command in the session. > Superuser is superuser, there is no magic associated with the bootstrap > superuser. > > Not quite true, it is magical in that every initdb thing that needs an owner has it as an owner. And, at least not easily or desirably, that ownership cannot be transferred, which makes dropping said role problematic. David J.
