SV: Foreign tables, user mappings and privilege setup

[Niels Jespersen]

>>On Mon, 2020-09-21 at 08:21 +, Niels Jespersen wrote:
>> create user mapping for current_user server s... (user 'remoteuser', 
>> password 'remotepassword');
>> 
>> create foreign table t   ( a int) server s... options (table_name 
>> 't_remote');
>> 
>> This works fine. Except only the user who created the user mapping can 
>> select from foreign table, even if other users have select privilege on the 
>> table. They will get a "user mapping not found for...".
>
>You have to create a user mapping for every user that is to access the foreign 
>table.
>
>If several users should have the same credentials, use a group.

Thank you for replying. 

I can create a user mapping for a group (role). This works, but it is not 
enough to grant the role to the end user (that has inherit defined). The end 
user must explicitly use "set role" before accessing the foreign table.

Is this what Oracle Corporation would call "Intended Behavior"  ;=) ? 

Regards Niels

Re: SV: Foreign tables, user mappings and privilege setup

[Tom Lane]

Niels Jespersen  writes:
> I can create a user mapping for a group (role). This works, but it is not 
> enough to grant the role to the end user (that has inherit defined). The end 
> user must explicitly use "set role" before accessing the foreign table.

Seems right to me.  If the user is a member of two groups, each of which
has a mapping for that server, how could the server choose one?

regards, tom lane

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Fabio Ugo Venchiarutti]

On 21/09/2020 17:53, Joshua Drake wrote:
3. The ability to embed PG to run in an automatic, quiet manner as part 
of something else. I know about SQLite, but it's extremely limited to 
the point of being virtually useless IMO, which is why I cannot use that 
for anything nontrivial. I want my familiar PostgreSQL, only not require 
it to be manually and separately installed on the machine where it is to 
run as part of some "application". If I could just "embed" it, this 
would allow me to create a single EXE which I can simply put on a 
different machine to run my entire "system" which otherwise takes *tons* 
of tedious, error-prone manual labor to install, set up and maintain. Of 
course, this is probably much easier said than done, but I don't 
understand why PG's architecture necessarily dictates that PG must be a 
stand-alone, separate thing. Or rather, why some "glue" cannot enable it 
to be used just like SQLite from a *practical* perspective, even if it 
still is a "server-client model" underneath the hood. (Which doesn't 
matter at all to me, nor should it matter to anyone else.)


It depends what you mean by "embedded".
If you want sqlite's linked library approach, where the database calls 
literally run your process' address space, then that's a no go, as 
postgres is a multi-user database server with its own process hierarchy.



However, postgres also is a rather agnostic command that does not detach 
from the parent's terminal/stdio unless instructed to, so nothing stops 
your bespoke application from launching and managing its own postmaster 
as a directly managed child process - started as part of application 
initialisation - and having that listen on a local socket only available 
to the application itself; this is what we implemented in some of our 
installations where postgres is enslaved to the cluster control system - 
the configuration is re-generated at every restart (IIRC Patroni does 
something similar minus the unix socket part).



A plethora of systems are built around the notion of programs calling 
other programs and managing the process' life cycle. The limiting factor 
to such architecture tends to be the OS's process control semantics and 
API (notoriously weaker or more contrived on non-unix-like OSs), but 
that's not postgres' fault.






--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--


Notice: 
This email is confidential and may contain copyright material of 
members of the Ocado Group. Opinions and views expressed in this message 
may not necessarily reflect the opinions and views of the members of the 
Ocado Group.


If you are not the intended recipient, please notify us 
immediately and delete all copies of this message. Please note that it is 
your responsibility to scan this message for viruses.


References to the 
"Ocado Group" are to Ocado Group plc (registered in England and Wales with 
number 7098618) and its subsidiary undertakings (as that expression is 
defined in the Companies Act 2006) from time to time. The registered office 
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, 
Hatfield, Hertfordshire, AL10 9UL.

Problems with PostgreSQL on Google compute engine

[Josef Machytka]

Hi guys,

I am not sure if this problem is really related to PostgreSQL but maybe
someone could have some idea?

We run several Debian instances with PostgreSQL on Google compute engine
and lately we have already seen several occurrences of the following
problem.

Instance becomes suddenly non responsive. We cannot ssh it and we cannot
connect to the database. Internal monitoring using telegraf is also not
running during that period, no monitoring data collected.

Google monitoring of CPU activity shows very low usage during that period.
GCP logs do not show any migration in fact do not show anything at all.
Also all internal logs for instance - postgresql log, syslog, logs from
periodical cronjobs - show the same gap. Looks like the instance was sort
of frozen during that time. We so far noticed it only with PostgreSQL
instances since these are heavily used.

Instances run these variants of OS and PG
Debian 9 with PG 11.9
Debian 9 with PG 10.13

These incidents usually take 10-15 minutes, but in one case it was 1:20
hours. At the end of the incident some PG process is killed by an OOM
killer but activity on the database immediately before the incident starts
is usually relatively low, CPU usage and memory usage too. So it looks more
like an instance has limited resources when it starts again? If it is even
possible...

Any idea what could be the cause of these issues or what shall we look for?
As I mentioned generally no info in internal logs on Debian during the
period of the incident.

Thanks.

Josef Machytka

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Jonathan Strong]

Joshua - adding to what Fabio said (with which I agree wholeheartedly!) -

It will be worthwhile for you to itemize the attributes and needed features
for making a final decision about the architecture of the application you
plan to deploy.

While you are familiar with PostgreSQL and like its features, it may not
wind up being the best match for what you are trying to accomplish. I'm not
sure your concerns about SQLite are true roadblocks. If you are looking to
deploy an executable supported by one or more DLLs but no external
processes, you won't find many mature and truly embeddable DBMSs out there.
SQLite is widely used and well supported, and some of the functionality
you'd normally consider putting in stored procedures might fit in with the
idea of creating custom functions. A few more steps than creating a
PostgreSQL stored procedure or function, but perhaps this approach would
make SQLite a reasonable choice for you. See:

https://stackoverflow.com/questions/2108870/how-to-create-custom-functions-in-sqlite

Back to the question about attributes and features of the DBMS, and Fabio's
question to you re: your definition of an embedded database. An embedded
database is typically used by a single user and is only available within
the context of the current application and user / memory space. It should
also be self-tuning and self-managing, as a typical end user can't and
shouldn't be expected to also handle any DBA tasks. Several databases are
considered "embedded", but the criteria used for this label may not match
your own. If you can spell out your specific expectations (e.g., the app
and database can be deployed in a single exe, etc.), this will help
tremendously in homing in on the right choice.

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Tue, Sep 22, 2020 at 10:18 AM Fabio Ugo Venchiarutti <
f.venchiaru...@ocado.com> wrote:

>
> On 21/09/2020 17:53, Joshua Drake wrote:
> > 3. The ability to embed PG to run in an automatic, quiet manner as part
> > of something else. I know about SQLite, but it's extremely limited to
> > the point of being virtually useless IMO, which is why I cannot use that
> > for anything nontrivial. I want my familiar PostgreSQL, only not require
> > it to be manually and separately installed on the machine where it is to
> > run as part of some "application". If I could just "embed" it, this
> > would allow me to create a single EXE which I can simply put on a
> > different machine to run my entire "system" which otherwise takes *tons*
> > of tedious, error-prone manual labor to install, set up and maintain. Of
> > course, this is probably much easier said than done, but I don't
> > understand why PG's architecture necessarily dictates that PG must be a
> > stand-alone, separate thing. Or rather, why some "glue" cannot enable it
> > to be used just like SQLite from a *practical* perspective, even if it
> > still is a "server-client model" underneath the hood. (Which doesn't
> > matter at all to me, nor should it matter to anyone else.)
>
> It depends what you mean by "embedded".
> If you want sqlite's linked library approach, where the database calls
> literally run your process' address space, then that's a no go, as
> postgres is a multi-user database server with its own process hierarchy.
>
>
> However, postgres also is a rather agnostic command that does not detach
> from the parent's terminal/stdio unless instructed to, so nothing stops
> your bespoke application from launching and managing its own postmaster
> as a directly managed child process - started as part of application
> initialisation - and having that listen on a local socket only available
> to the application itself; this is what we implemented in some of our
> installations where postgres is enslaved to the cluster control system -
> the configuration is re-generated at every restart (IIRC Patroni does
> something similar minus the unix socket part).
>
>
> A plethora of systems are built around the notion of programs calling
> other programs and managing the process' life cycle. The limiting factor
> to such architecture tends to be the OS's process control semantics and
> API (notoriously weaker or more contrived on non-unix-like OSs), but
> that's not postgres' fault.
>
>
>
>
>
> --
> Regards
>
> Fabio Ugo Venchiarutti
> OSPCFC Network Engineering Dpt.
> Ocado Technology
>
> --
>
>
> Notice:
> This email is confidential and may contain copyright material of
> members of the Ocado Group. Opinions and views expressed in this message
> may not necessarily reflect the opinions and views of the members of the
> Ocado Group.
>
> If you are not the intended recipient, please notify us
> immediately and delete all copies of this message. Please note that it is
> your responsibility to scan this message for vi

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[tutiluren]

Sep 21, 2020, 7:53 PM by j...@commandprompt.com:

> I have to agree that pg_dump is largely a step child backup program. It has 
> consistently been found over the years to be lacking in a number of areas. 
> Unfortunately, working on pg_dump isn't sexy and it is difficult to get 
> volunteers or even paid resources to do such a thing. The real solution for 
> pg_dump is a complete refactor which includes pg_dumpall and it is not a 
> small undertaking. It should be noted that it is also a less and less used 
> program. On our team it is normally used for only very specific needs 
> (grabbing a schema) and we use binary backups or logical replication to 
> receive specific data.
>
Huh? Are you saying that there is another, superior way to back up PostgreSQL 
databases other than pg_dump? I re-read the manual on it just now, but didn't 
see a single word about it being "legacy" or "deprecated" or even that there's 
any other way to do it. What do you mean?


>> 3. The ability to embed PG to run in an automatic, quiet manner as part of 
>> something else. I know about SQLite, but it's extremely limited to the point 
>> of being virtually useless IMO, which is why I cannot use that for anything 
>> nontrivial. I want my familiar PostgreSQL, only not require it to be 
>> manually and separately installed on the machine where it is to run as part 
>> of some "application". If I could just "embed" it, this would allow me to 
>> create a single EXE which I can simply put on a different machine to run my 
>> entire "system" which otherwise takes *tons* of tedious, error-prone manual 
>> labor to install, set up and maintain. Of course, this is probably much 
>> easier said than done, but I don't understand why PG's architecture 
>> necessarily dictates that PG must be a stand-alone, separate thing. Or 
>> rather, why some "glue" cannot enable it to be used just like SQLite from a 
>> *practical* perspective, even if it still is a "server-client model" 
>> underneath the hood. (Which doesn't matter at all to me, nor should it 
>> matter to anyone else.)
>>
>>
>
> This is really using the wrong tool for the job type of issue. PG was never 
> designed for such a scenario.
>
I hate the "wrong tool for the job" argument. It assumes that everyone has 
infinite time, energy and brain capacity to learn endless redundant tools just 
to "use the right tool for the job" rather than "use what you actually know". I 
know PG. I don't know SQLite. They are very different. So obviously, I want to 
use PG.

What exactly makes PG unsuitable for this? I don't get it. But at the same 
time, I also realize that it's not going to happen at this point. The entire 
concept of a desktop computer appears to be phased out as we speak...


>> 4. There is no built-in means to have PG manage (or even suggest) indexes on 
>> its own. Trying to figure out what indexes to create/delete/fine-tune, and 
>> determine all the extremely complex rules for this art (yes, I just called 
>> index management an *art*, because it is!), is just utterly hopeless to me. 
>> It never gets any easier. Not even after many years. It's the by far worst 
>> part of databases to me (combined with point five). Having to use 
>> third-party solutions ensures that it isn't done in practice, at least for 
>> me. I don't trust, nor do I want to deal with, external software and 
>> extensions in my databases. I still have nightmares from PostGIS, which I 
>> only keep around, angrily, out of absolute necessity. I fundamentally don't 
>> like third-party add-ons to things, but want the core product to properly 
>> support things. Besides, this (adding/managing indexes) is not even some 
>> niche/obscure use-case, but something which is crucial for basically any 
>> nontrivial database of any kind!
>>
>
> I think you are looking at this from a very windows centric way. Open Source 
> has its origins from the Unix paradigm where each tool was designed to solve 
> one type of problem and you used multiple tools to create a "solution". 
> Though we have strayed from that on some items due to the evolving nature of 
> software needs, that is still at our core and for good reason. Having tools, 
> flags etc... to do such things (including your point #3) creates complexity 
> best left to "vendors" not the software project.
>
While I understand what you mean, and even agree in theory, in practice, this 
always results in crappy third-party solutions which I don't want to deal with. 
PostGIS, for example, forces me to use "postgis" for its schema instead of 
"PostGIS" just because they arrogantly didn't construct their internal queries 
properly. "Little" things like that.


>> The practical end result of this is that I've always gone back to using the 
>> untouched default configuration file (except for the logging-related 
>> options), which, especially in the past on FreeBSD, *severely* crippled my 
>> PG database to not even come close to taking advantage of the full power of 

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Adrian Klaver]

On 9/22/20 3:28 PM, tutilu...@tutanota.com wrote:


Sep 21, 2020, 7:53 PM by j...@commandprompt.com:

I have to agree that pg_dump is largely a step child backup program.
It has consistently been found over the years to be lacking in a
number of areas. Unfortunately, working on pg_dump isn't sexy and it
is difficult to get volunteers or even paid resources to do such a
thing. The real solution for pg_dump is a complete refactor which
includes pg_dumpall and it is not a small undertaking. It should be
noted that it is also a less and less used program. On our team it
is normally used for only very specific needs (grabbing a schema)
and we use binary backups or logical replication to receive specific
data.

Huh? Are you saying that there is another, superior way to back up 
PostgreSQL databases other than pg_dump? I re-read the manual on it just 
now, but didn't see a single word about it being "legacy" or 
"deprecated" or even that there's any other way to do it. What do you mean?


It is not deprecated or legacy and it is still used by many. The issue 
is that people try to do 'partial' dumps without reading the docs on 
what those switches actually do and the potential conflicts therein. For 
large installations there it is often better to use some form of 
replication(binary and/or logical) to maintain a continuous backup. 
pg_dump can take a long time on very big databases, so it may not keep up.





This is really using the wrong tool for the job type of issue. PG
was never designed for such a scenario.

I hate the "wrong tool for the job" argument. It assumes that everyone 
has infinite time, energy and brain capacity to learn endless redundant 
tools just to "use the right tool for the job" rather than "use what you 
actually know". I know PG. I don't know SQLite. They are very different. 
So obviously, I want to use PG.


This comes down to what your definition of embedded is?  A matter of 
determining whether we are talking apples or oranges.





What exactly makes PG unsuitable for this? I don't get it. But at the 
same time, I also realize that it's not going to happen at this point. 
The entire concept of a desktop computer appears to be phased out as we 
speak...




While I understand what you mean, and even agree in theory, in practice, 
this always results in crappy third-party solutions which I don't want 
to deal with. PostGIS, for example, forces me to use "postgis" for its 
schema instead of "PostGIS" just because they arrogantly didn't 
construct their internal queries properly. "Little" things like that.


Huh? A schema is just a name space, why does it matter how the extension 
chooses to define it? I mean you could have number of permutations of 
postgis.




The practical end result of this is that I've always gone back
to using the untouched default configuration file (except for
the logging-related options), which, especially in the past on
FreeBSD, *severely* crippled my PG database to not even come
close to taking advantage of the full power of the hardware.
Instead, it felt like I was using maybe 1% of the machine's
power, even with a proper database design and indexes and all of
that stuff, simply because the default config was so
"conservative" and it couldn't be just set to "use whatever
resources are available".


Not to be unkind but this does seem lazy. There are literally
hundreds of "how to make postgres go fast", "how to optimize
postgres" if you take 15 minutes to Google. It is true that the
project (outside of the wiki) doesn't have much information in the
official documentation but that doesn't mean that the information is
not available.

Hundreds of crappy, outdated, confusing, badly written "web tutorialz" 
are worth nothing. A couple of clear, unambiguous documentation 
paragraphs are worth their (metaphorical) weight in gold.


The problem is the 'unambiguous' part. There are so many ways Postgres 
is used, writing a configuration doc that suited everyone would have so 
many if/and/or/buts that it would define ambiguity. If you want that 
information post an email here with the information on your server specs 
and proposed usage.




Claiming that "the information is out there" is just hand-waving. It's 
shifting the burden to the user to actively hunt for information, and 
very likely be misled by all the garbage articles out there. I learned 
some horrible practices early on from "web tutz" and it took me many 
years to unlearn that stuff.


I know that writing documentation isn't fun, but it's necessary. Also, 
my overall point was to not even have to deal with the specifics, but 
just be able to tell PG with a single config option that it's allowed to 
"use most of the machine's resources".


That would entail building an AI into  the code that would deal with all 
the possible OS(versions), Postgres(vers

I'm surprised that this worked

[raf]

Hi,

I just wrote a query that I didn't expect to work but I
was pleasantly surprised that it did. It looked
something like this:

  select
a.aaa,
c.ccc,
d.ddd1,
d.ddd2
  from
tbla a,
tblb b,
tblc c,
funcd(c.id) d
  where
a.something = something and
b.something = a.something and
c.something = b.something

How does it know which c.id to use for the function
without going all cartesian product on me? Maybe it
makes no sense for such a parameterised function to be
part of a cartesian product. Actually, that function
returns a setof record but only ever a single record.
That might be relevant.

I was sure I'd done something similar once that
(sensibly) didn't work, and I needed a loop to call the
function in, but I might be thinking of something in an
outer join's "on" clause. Does that make sense?

Even more impressed with Postgresql than usual. :-)

cheers,
raf

Re: I'm surprised that this worked

[David G. Johnston]

On Tue, Sep 22, 2020 at 6:34 PM raf  wrote:

> Hi,
>
> I just wrote a query that I didn't expect to work but I
> was pleasantly surprised that it did. It looked
> something like this:
>
>   select
> a.aaa,
> c.ccc,
> d.ddd1,
> d.ddd2
>   from
> tbla a,
> tblb b,
> tblc c,
> funcd(c.id) d
>   where
> a.something = something and
> b.something = a.something and
> c.something = b.something
>
> How does it know which c.id to use for the function
> without going all cartesian product on me?


Using the comma-separated from syntax doesn't force the planner to perform
a full multi-relation cartesian join (though conceptually that is what
happens) - it still only joins two relations at a time.  After it joins a,
b, and c it joins each row of that result with all of the rows produced by
evaluating funcd(c.id).

>From the SELECT docs for LATERAL:

"When a FROM item contains LATERAL cross-references, evaluation proceeds as
follows: for each row of the FROM item providing the cross-referenced
column(s), or set of rows of multiple FROM items providing the columns, the
LATERAL item is evaluated using that row or row set's values of the
columns. The resulting row(s) are joined as usual with the rows they were
computed from. This is repeated for each row or set of rows from the column
source table(s)."

That said, the planner would be within its rights to indeed evaluate
funcd for every single row in tblc - applying c.something=b.something to
the final result would still cause those rows from funcd where the
attribute something for the given c.id matches the where clause filter to
be excluded.

I was sure I'd done something similar once that
> (sensibly) didn't work, and I needed a loop to call the
> function in, but I might be thinking of something in an
> outer join's "on" clause. Does that make sense?
>

You probably tried it before we added LATERAL to our engine.

David J.