is there still a memory leak with hash joins in PG 12.11 ?
Hi, We are running on PG 12.11. We recognized an evergrowing memory usage. work_mem = 50M there are some regularly hash joins. I found this blog post talking about a memory leak having hash joins due to a larger work_mem. https://gist.github.com/luhn/2b35a9b31255e3a6a2e6a06d1213dfc9 Does anyone know if this problem is still there in PG 12.11 ? Thanks, Markus
Re: is there still a memory leak with hash joins in PG 12.11 ?
On 2022-Sep-28, Zwettler Markus (OIZ) wrote: > I found this blog post talking about a memory leak having hash joins due to a > larger work_mem. > https://gist.github.com/luhn/2b35a9b31255e3a6a2e6a06d1213dfc9 Oh dear, is that what passes for a blog post these days? > Does anyone know if this problem is still there in PG 12.11 ? It wasn't there in 9.5, so it seems safe to believe it isn't in 12.11. > We are running on PG 12.11. > We recognized an evergrowing memory usage. > > work_mem = 50M > there are some regularly hash joins. I think it's more likely that this is a new, different bug (assuming it is a bug at all). But you'd have to provide more details in order to track it down. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "El Maquinismo fue proscrito so pena de cosquilleo hasta la muerte" (Ijon Tichy en Viajes, Stanislaw Lem)
Streaming wal from primiry terminating
Hi, After failover all stand by nodes could not start streaming wal recovery. Streaming recovery start from 1473/A500, but standby start at 1473/A5FFEE08, this seems to be the problem. What can we do in this case to restore? Is it possible to shift wal streaming recovery point on primary? Can checkpoint on primary help in this situation? 2022-09-26 14:08:23.289 [3747796] LOG: consistent recovery state reached at 1473/A5FFEE08 2022-09-26 14:08:23.289 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:08:23.290 [3747793] LOG: database system is ready to accept read-only connections 2022-09-26 14:08:23.292 [3747867] LOG: starting bgworker pgsentinel 2022-09-26 14:08:23.672 [3747868] LOG: started streaming WAL from primary at 1473/A500 on timeline 18 2022-09-26 14:08:24.363 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:08:24.366 [3747868] FATAL: terminating walreceiver process due to administrator command 2022-09-26 14:08:24.366 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:08:24.366 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:08:24.366 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 ... ... ... 2022-09-26 14:12:24.055 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:12:29.060 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:12:34.064 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:12:39.068 [3747796] LOG: invalid record length at 1473/A5FFEE08: wanted 24, got 0 2022-09-26 14:12:39.910 [3747793] LOG: received fast shutdown request 2022-09-26 14:12:39.911 [3747793] LOG: aborting any active transactions 2022-09-26 14:12:39.911 [3747867] LOG: bgworker pgsentinel signal: processed SIGTERM 2022-09-26 14:12:39.914 [3747797] LOG: shutting down 2022-09-26 14:12:39.922 [3747793] LOG: database system is shut down
table inheritance and privileges
I'm dynamically creating tables that inherit from another table but they don't inherit the access privileges. Is there a way to have the new tables inherit their parent tables access privileges? Ted
Re: Limiting the operations that client-side code can perform upon its database backend's artifacts
On 2022-09-28 09:13:53 +0800, Julien Rouhaud wrote: > On Tue, Sep 27, 2022 at 05:27:22PM -0700, Bryn Llewellyn wrote: > > > hjp-pg...@hjp.at wrote: > > >> rjuju...@gmail.com wrote: > > >>> b...@yugabyte.com wrote: > > >>> My demo seems to show that when a program connects as "client", it can > > >>> perform exactly and only the database operations that the database > > >>> design > > >>> specified. Am I missing something? In other words, can anybody show me a > > >>> vulnerability? > > >> > > >> What exactly prevents the client role from inserting e.g. > > >> > > >> - 'robert''); drop table students; --' > > > > > > It can do this but it won't do any harm since the client role doesn't have > > > permission to drop the table. > > FTR it's a reference to https://xkcd.com/327/ > > Both of you are saying it's harmless because you're assuming that only the > client role may read the data and act on it, but the whole point of SQL > injection is to try to do actions that the role you have access to can't > already do. No. The point of SQL injection is to get client code (typically some web application, but could be anything not directly under the attacker's control) to execute SQL which it wasn't designed to execute. So for example the student management system in the cartoon may not contain a "drop" statement anywhere in its code. But because of the SQL injection it will execute one anyway. This doesn't elevate the privileges of the client role. The attacker can merely leverage privileges the client role already has (which may be more than it strictly needs). If the attacker has direct access to the database then SQL injection is moot since they can just execute the SQL anyway. > > > There are often several layers of defense. The database frequently won't > > > be > > > accessible from the open internet (or even the company network) directly. > > > Only a middle tier of application servers running vetted client code will > > > connect directly. Even those servers may not be accessible directly to end > > > users. There may be a layer of proxy servers above them. Each of these > > > layers may implement additional checks, rate limits and monitoring. > > If no one has direct SQL access to the database, then there's no problem with > a > role being able to pg_terminate_backend() session for the same role, and this > thread shouldn't exist to begin with. The attacker might use SQL injection to execute pg_terminate_backend(). But yes, as Tom already pointed out, if they can do that, that's probably the least of your worries. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: table inheritance and privileges
Ted Toth writes: > I'm dynamically creating tables that inherit from another table but > they don't inherit the access privileges. Is there a way to have the > new tables inherit their parent tables access privileges? No, but do you need that? When accessing the parent table, there's no need for privileges on individual children --- we only check tables(s) directly named in the query. regards, tom lane
Re: Limiting the operations that client-side code can perform upon its database backend's artifacts
On 2022-09-27 19:29:39 -0700, Bryn Llewellyn wrote: > I interpreted what Tom wrote to mean that he flatly rejected the idea > that a database design was possible that prevented a client session > that authorized as a role, that's designed for that purpose, from > dropping tables and otherwise arbitrarily corrupting stuff. I expect > that I completely misunderstood his point. I think you did. What he was saying was that a database design which uses only one single role which both owns all the objects and executes all code on behalf of the user can not be secure. At the very least you need two roles: One which owns the objects and one which can only use the objects in a way allowed by the business logic and is not allowed to change that logic (This is the case you demonstrated). In many cases this should be even more fine-grained, and at the extreme end every user could actually have several roles, each with only the minimal privileges required. (I have often observed that we tend to throw away and build permission systems at every layer of the stack: The OS has a system of users and permissions. But the database server runs as a single user (postgres) which has access to all the data files. So it has to implement its own system of roles and permissions. Then an application developer comes along and writes an app which uses a single database role which has access to all the data. So it again has to implement its own system of users and permissions ...) hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: table inheritance and privileges
I have written a 'before insert' trigger using a similar technique to that described in section 5.11.3 of : https://www.postgresql.org/docs/14/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE However my trigger queries to see if the partition table exists, creates it if it doesn't then inserts into it and returns 'SKIP' (plpython3u). On Wed, Sep 28, 2022 at 2:49 PM Tom Lane wrote: > > Ted Toth writes: > > I'm dynamically creating tables that inherit from another table but > > they don't inherit the access privileges. Is there a way to have the > > new tables inherit their parent tables access privileges? > > No, but do you need that? When accessing the parent table, there's > no need for privileges on individual children --- we only check > tables(s) directly named in the query. > > regards, tom lane
Re: Streaming wal from primiry terminating
At Wed, 28 Sep 2022 08:50:12 +, "Lahnov, Igor" wrote in > Hi, > After failover all stand by nodes could not start streaming wal recovery. > Streaming recovery start from 1473/A500, but standby start at > 1473/A5FFEE08, this seems to be the problem. It's not a problem at all. It is quite normal for standby to start streaming from the beginning of a WAL segment. > What can we do in this case to restore? > Is it possible to shift wal streaming recovery point on primary? > Can checkpoint on primary help in this situation? > 2022-09-26 14:08:23.672 [3747868] LOG: started streaming WAL from > primary at 1473/A500 on timeline 18 > 2022-09-26 14:08:24.363 [3747796] LOG: invalid record length at > 1473/A5FFEE08: wanted 24, got 0 > 2022-09-26 14:08:24.366 [3747868] FATAL: terminating walreceiver > process due to administrator command This seems to mean someone emtpied primary_conninfo. > 2022-09-26 14:08:24.366 [3747796] LOG: invalid record length at > 1473/A5FFEE08: wanted 24, got 0 > 2022-09-26 14:08:24.366 [3747796] LOG: invalid record length at > 1473/A5FFEE08: wanted 24, got 0 I don't fully understand the situation. A situation that leads the this state I can come up with is that somehow the standby restored an incomplete WAL segment from the primary. For example, in a case wheresomeone copied the current active WAL file from pg_wal to archive on the primary, or a case where restore_command on the standby fetches WAL files from pg_wal on the primary instead of its archive. Both are not normal operations. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
