Re: Schema variables - new implementation for Postgres 15

Pavel Stehule: Sure there is more possibilities, but I don't want to lost the possibility to write code like CREATE TEMP VARIABLE _x; LET _x = 'hello'; DO $$ BEGIN   RAISE NOTICE '%', _x; END; $$; So I am searching for a way to do it safely, but still intuitive and user friendly. Maybe

Re: Schema variables - new implementation for Postgres 15

Alvaro Herrera: Perhaps the solution to all this is to avoid having the variables be implicitly present in the range table of all queries. Instead, if you need a variable's value, then you need to add the variable to the FROM clause; +1 This should make it easier to work with composite type

Re: [PATCH] Add --syntax to postgres for SQL syntax checking

Tom Lane: This is really what is missing for the ecosystem. A libpqparser for tools to use: Formatters, linters, query rewriters, simple syntax checkers... they are all missing access to postgres' own parser. To get to that, you'd need some kind of agreement on what the syntax tree is. I

Re: [PATCH] Add --syntax to postgres for SQL syntax checking

Tom Lane: The thing that was bothering me most about this is that I don't understand why that's a useful check. If I meant to type UPDATE mytab SET mycol = 42; and instead I type UPDATEE mytab SET mycol = 42; your proposed feature would catch that; great. But if I type

Re: Building with meson on NixOS/nixpkgs

Peter Eisentraut: On 29.03.24 19:47, walt...@technowledgy.de wrote: > -    uuid = dependency('ossp-uuid', required: true) > +    # upstream is called "uuid", but many distros change this to "ossp-uuid" > +    uuid = dependency('ossp-uuid', 'uuid', required: true) How would this behave if

Re: Building with musl in CI and the build farm

Tom Lane: You'd have to commit a failing patch first to break CI for all other developers. No, what I'm more worried about is some change in the environment causing the build to start failing. When that happens, it'd better be an environment that many of us are familiar with and can test/fix.

Re: Building with musl in CI and the build farm

Tom Lane: That is not the concern here. What I think Peter is worried about, and certainly what I'm worried about, is that a breakage in SanityCheck comprehensively breaks all CI testing for all Postgres developers. You'd have to commit a failing patch first to break CI for all other

Re: Building with musl in CI and the build farm

Peter Eisentraut: On 31.03.24 15:34, walt...@technowledgy.de wrote: I'd rather adapt one of the existing tasks, to avoid increasing CI costs unduly. I looked into this and I think the only task that could be changed is the SanityCheck. I think SanityCheck should run a simple, "average"

Re: RFC: Additional Directory for Extensions

Alvaro Herrera: I support the idea of there being a second location from where to load shared libraries ... but I don't like the idea of making it runtime-configurable. If we want to continue to tighten up what superuser can do, then one of the things that has to go away is the ability to load

Re: Security lessons from liblzma

I looked through the repositories of 19 linux distros [1] to see what kind of patches are applied often. Many of them share the same package managers / repositories and thus the same patches. I made sure to look at some smaller, "other" distros as well, to see what kind of problems appear

Re: Building with musl in CI and the build farm

108672From 4a69d9851e7bbd7cd521d236847af9ebf5e6253b Mon Sep 17 00:00:00 2001 From: Wolfgang Walther Date: Sun, 31 Mar 2024 15:17:43 +0200 Subject: [PATCH] Build SanityCheck against musl --- .cirrus.tasks.yml | 9 + 1 file changed, 9 insertions(+) diff --git a/.cirrus.tasks.yml b/.cirrus.tasks.yml index 72f553e69f4..5815c51a

Re: Building with musl in CI and the build farm

Here's an update on the progress to run musl (Alpine Linux) in the buildfarm. Wolfgang Walther: The animal runs in a docker container via GitHub Actions in [2]. Right now it's still running with --test, until I get the credentials to activate it. The animals have been activated

Re: Building with meson on NixOS/nixpkgs

Wolfgang Walther: To build on NixOS/nixpkgs I came up with a few small patches to meson.build. All of this works fine with Autoconf/Make already. In v3, I added another small patch for meson, this one about proper handling of -Dlibedit_preferred when used together with -Dreadline=enabled

Building with musl in CI and the build farm

The need to do $subject came up in [1]. Moving this to a separate discussion on -hackers, because there are more issues to solve than just the LD_LIBRARY_PATH problem. Andres Freund: FWIW, except for one small issue, building postgres against musl works on debian and the tests pass if I

Re: Building with meson on NixOS/nixpkgs

Nazir Bilal Yavuz: 0001 & 0002: Adding code comments to explain why they have fallback could be nice. 0003: Looks good to me. Added some comments in the attached. Best, WolfgangFrom 2d271aafd96a0ea21710a06ac5236e47217c36d1 Mon Sep 17 00:00:00 2001 From: Wolfgang Walther Date: Sat, 2

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Tom Lane: Actually, roles_is_member_of sucks before v16 too; the new thing is only that it's being invoked during GRANT ROLE. Using the roles created by the given test case, I see in v15: [...] So it takes ~3.5s to populate the roles_is_member_of cache for "acc" given this membership set.

Re: Possibility to disable `ALTER SYSTEM`

Greg Sabino Mullane: On Tue, Mar 19, 2024 at 12:05 PM Tom Lane > wrote: If you aren't willing to build a solution that blocks off mods using COPY TO FILE/PROGRAM and other readily-available-to-superusers tools (plpythonu for instance), I think you

Building with meson on NixOS/nixpkgs

To build on NixOS/nixpkgs I came up with a few small patches to meson.build. All of this works fine with Autoconf/Make already.From 24ae72b9b0adc578c6729eff59c9038e6b4ac517 Mon Sep 17 00:00:00 2001 From: Wolfgang Walther Date: Sat, 2 Mar 2024 17:18:38 +0100 Subject: [PATCH 1/3] Fallback to uuid

Re: MERGE ... RETURNING

Jeff Davis: To summarize, most of the problem has been in retrieving the action (INSERT/UPDATE/DELETE) taken or the WHEN-clause number applied to a particular matched row. The reason this is important is because the row returned is the old row for a DELETE action, and the new row for an INSERT

Re: psql: add \create_function command

Pavel Stehule: looks a little bit obscure - why do you need to do it from psql? And how frequently do you do it? I store all my SQL code in git and use "psql -e" to "bundle" it into an extension, which is then deployed to production. The code is spread over many files, which include other

Re: psql: add \create_function command

Tom Lane: Or we could cut out the intermediate variable altogether by inventing something that works like :'...' but reads from a file not a variable. That might be too specialized though, and I'm not sure about good syntax for it either. Maybe like CREATE FUNCTION foo() RETURNS whatever AS

Re: fixing CREATEROLE

Robert Haas: 1) Automatically install an additional membership grant, with the CREATEROLE user as the grantor, specifying INHERIT OR SET as TRUE (I personally favor attaching these to ALTER ROLE, modifiable only by oneself) Hmm, that's an interesting alternative to what I actually

Re: fixing CREATEROLE

Robert Haas: And the result is that I've got like five people, some of whom particulated in those discussions, showing up to say "hey, we don't need the ability to set defaults." Well, if that's the case, then why did we have hundreds and hundreds of emails within the last 12 months arguing

Re: fixing CREATEROLE

Mark Dilger: Isn't this just GRANT .. WITH SET FALSE, INHERIT FALSE, ADMIN TRUE? That should allow role administration, without actually granting membership in that role, yet, right? Can you clarify what you mean here? Are you inventing a new syntax? +GRANT bob TO alice WITH SET FALSE,

Re: fixing CREATEROLE

Mark Dilger: Robert's patch tries to deal with the (possibly unwanted) role membership by setting up defaults to mitigate the effects, but that is more confusing to me than just de-conflating role membership from role administration, and giving role creators administration over roles they

Re: fixing CREATEROLE

Robert Haas: In my proposal, the "object" is not the GRANT of that role. It's the role itself. So the default privileges express what should happen when the role is created. The default privileges would NOT affect a regular GRANT role TO role_spec command. They only run that command when a role

Re: fixing CREATEROLE

David G. Johnston: A quick tally of the thread so far: No Defaults needed: David J., Mark?, Tom? Defaults needed - attached to role directly: Robert Defaults needed - defined within Default Privileges: Walther? s/Walther/Wolfgang The capability itself seems orthogonal to the rest

Re: fixing CREATEROLE

Robert Haas: I don't know if changing the syntax from A to B is really getting us anywhere. I generally agree that the ALTER DEFAULT PRIVILEGES syntax looks nicer than the CREATE/ALTER ROLE syntax, but I'm not sure that's a sufficient reason to move the control over this behavior to ALTER

Re: fixing CREATEROLE

Robert Haas: I have to admit that when I realized that was the natural place to put them to make the patch work, my first reaction internally was "well, that can't possibly be right, role properties suck!". But I didn't and still don't see where else to put them that makes any sense at all, so I

Re: fixing CREATEROLE

Wolfgang Walther: Tom Lane: No, we don't support partial indexes on catalogs, and I don't think we want to change that.  Partial indexes would require expression evaluations occurring at very inopportune times. I see. Is that the same for indexes *on* an expression? Or would those be ok

Re: fixing CREATEROLE

Tom Lane: No, we don't support partial indexes on catalogs, and I don't think we want to change that. Partial indexes would require expression evaluations occurring at very inopportune times. I see. Is that the same for indexes *on* an expression? Or would those be ok? With a custom

Re: fixing CREATEROLE

Robert Haas: 2. There are some serious implementation challenges because the constraints on duplicate object names must be something which can be enforced by unique constraints on the relevant catalogs. Off-hand, I don't see how to do that. It would be easy to make the cluster roles all have

Re: fixing CREATEROLE

Robert Haas: It seems to me that the root of any fix in this area must be to change the rule that CREATEROLE can administer any role whatsoever. Agreed. Instead, I propose to change things so that you can only administer roles for which you have ADMIN OPTION. [...] > I'm curious to hear what

Re: Make ON_ERROR_STOP stop on shell script failure

Fujii Masao: One concern about this patch is that some applications already depend on the current behavior of ON_ERROR_STOP, i.e., psql doesn't stop even when the shell command returns non-zero exit code. If so, we might need to extend ON_ERROR_STOP so that it accepts the following setting

Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas: Scratch my previous suggestion. A new, less fuzyy definition would be: Ownership is not a privilege itself and as such not inheritable. [...] If I'm understanding correctly, this would amount to a major redefinition of what it means to inherit privileges, and I think the chances of

Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas: I don't think we're going to be very happy if we redefine inheriting the privileges of another role to mean inheriting only some of them. That seems pretty counterintuitive to me. I also think that this particular definition is pretty fuzzy. Scratch my previous suggestion. A new,

Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas: This shows that if rhaas (or whoever) performs DML on a table owned by pg_read_all_settings, he might trigger arbitrary code written by alice to run under his own user ID. Now, that hazard would exist anyway for tables owned by alice, but now it also exists for any tables owned by

Re: Allow foreign keys to reference a superset of unique columns

James Coleman: As I was reading through the email chain I had this thought: could you get the same benefit (or 90% of it anyway) by instead allowing the creation of a uniqueness constraint that contains more columns than the index backing it? So long as the index backing it still guaranteed the

Re: Allow foreign keys to reference a superset of unique columns

James Coleman: So the broader point I'm trying to make is that, as I understand it, indexes backing foreign key constraints is an implementation detail. The SQL standard details the behavior of foreign key constraints regardless of implementation details like a backing index. That means that the

Re: Add ON CONFLICT DO RETURN clause

Peter Geoghegan: On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther wrote: The attached patch adds a DO RETURN clause to be able to do this: INSERT INTO x (id) VALUES (1) ON CONFLICT DO RETURN RETURNING created_at; Much simpler. This will either insert or do nothing - but in both cases

Re: Allow foreign keys to reference a superset of unique columns

James Coleman: If we have a declared constraint on x,y where x is unique based on an index including on x I do not think we should have that fk constraint work differently than a constraint on x,y where there is a unique index on x,y. That would seem to be incredibly confusing behavior (even if

Add ON CONFLICT DO RETURN clause

) ON CONFLICT DO RETURN RETURNING created_at; Much simpler. This will either insert or do nothing - but in both cases return a row. Thoughts? Best Wolfgang>From 83a0031ed2ded46cbf6fd130bd76680267db7a5e Mon Sep 17 00:00:00 2001 From: Wolfgang Walther Date: Sun, 25 Sep 2022 16:20:44 +0200 Subj

Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas: Well, maybe. Suppose that role A has been granted pg_read_all_settings WITH INHERIT TRUE, SET TRUE and role B has been granted pg_read_all_settings WITH INHERIT TRUE, SET FALSE. A can create a table owned by pg_read_all_settings. If A does that, then B can now create a trigger on

Re: Allow foreign keys to reference a superset of unique columns

James Coleman: If I'm following properly this sounds like an overengineered EAV schema, and neither of those things inspires me to think "this is a use case I want to support". That being said, I know that sometimes examples that have been abstracted enough to share aren't always the best, so

Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas: Fairly obviously, my thinking here is biased by having written the patch to allow restricting SET ROLE. If alice can neither inherit bob's privileges nor SET ROLE bob, she had better not be able to create objects owned by bob, because otherwise she can make a table, add an

Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas: I think to change the owner of an object from role A to role B, you just need a different "privilege" on that role B to "use" the role that way, which is distinct from INHERIT or SET ROLE "privileges". It's not distinct, though, because if you can transfer ownership of a table to

Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas: Fairly obviously, my thinking here is biased by having written the patch to allow restricting SET ROLE. If alice can neither inherit bob's privileges nor SET ROLE bob, she had better not be able to create objects owned by bob, because otherwise she can make a table, add an

Re: Updatable Views and INSERT INTO ... ON CONFLICT

Joel Jacobson: I note it's not yet possible to INSERT INTO an Updatable View using the ON CONFLICT feature. To be clear, it seems to be supported for AUTO-updatable views and for views with manually created RULES, but not for views with INSTEAD OF triggers. Not saying it is desired, just

Re: Allow foreign keys to reference a superset of unique columns

Kaiting Chen: I'd like to propose a change to PostgreSQL to allow the creation of a foreign key constraint referencing a superset of uniquely constrained columns. +1 Tom Lane: TBH, I think this is a fundamentally bad idea and should be rejected outright. It fuzzes the semantics of the FK

Re: allowing for control over SET ROLE

Robert Haas: Beginning in e3ce2de09d814f8770b2e3b3c152b7671bcdb83f, the inheritance behavior of role-grants can be overridden for individual grants, so that some grants are inherited and others are not. That's a great thing to have! However, there is no similar facility for controlling

Re: [PATCH] Add reloption for views to enable RLS

Dean Rasheed: That is also the main reason I preferred naming it "security_invoker" - it is consistent with other databases and eases transition from such systems. [...] For my part, I find myself more and more convinced that "security_invoker" is the right name, because it matches the

Re: [PATCH] Add reloption for views to enable RLS

Laurenz Albe: I'd be happy with "check_as_owner", except it is unclear *what* is checked. Yeah, that could be associated with WITH CHECK OPTION, too, as in "do the CHECK OPTION stuff as the owner". "check_permissions_as_owner" is ok with me, but a bit long. check_permissions_as_owner is

Re: [PATCH] Add reloption for views to enable RLS

Laurenz Albe: I converted the option to run_as_owner=true|false in the attached v7. It now definitely seems like the right way to move forward and getting more feedback. I think we are straying from the target. "run_as_owner" seems wrong to me, because it is all about permission checking

Re: [PATCH] Add reloption for views to enable RLS

Christoph Heiss: xxx_owner=true would be the default and xxx_owner=false could be set explicitly to get the behavior we are looking for in this patch? I'm not sure if an option which is on by default would be best, IMHO. I would rather have an off-by-default option, so that you explicitly

Re: [PATCH] Add reloption for views to enable RLS

Laurenz Albe: So even though the view owner "duff" has no permissions on the schema "viewtest", we can still select from the table. Permissions on the schema containing the table are not checked, only permissions on the table itself. I am not sure how to feel about this. It is not what I would

Re: faulty link

leads to https://www.postgresql.org/docs/release/14.2/ which gives 'Not Found' for me (Netherlands) Same here: Not Found. (Germany)

Re: [PATCH] Add reloption for views to enable RLS

Laurenz Albe: So even though the view owner "duff" has no permissions on the schema "viewtest", we can still select from the table. Permissions on the schema containing the table are not checked, only permissions on the table itself. [...] If not, I don't know if it is the business of this

Re: [PATCH] Add reloption for views to enable RLS

Christoph Heiss wrote: As part of a customer project we are looking to implement an reloption for views which when set, runs the subquery as invoked by the user rather than the view owner, as is currently the case. The rewrite rule's table references are then checked as if the user were

Suggestion: optionally return default value instead of error on failed cast

Hi, currently a failed cast throws an error. It would be useful to have a way to get a default value instead. T-SQL has try_cast [1] Oracle has CAST(... AS .. DEFAULT ... ON CONVERSION ERROR) [2] The DEFAULT ... ON CONVERSION ERROR syntax seems like it could be implemented in PostgreSQL.

Re: extension patch of CREATE OR REPLACE TRIGGER

osumi.takami...@fujitsu.com: * I'm a little bit concerned about the semantics of changing the tgdeferrable/tginitdeferred properties of an existing trigger. If there are trigger events pending, and the trigger is redefined in such a way that those events should already have been fired, what

Re: Allow an alias to be attached directly to a JOIN ... USING

Peter Eisentraut: On 2019-12-31 00:07, Vik Fearing wrote: One thing I notice is that the joined columns are still accessible from their respective table names when they should not be per spec.  That might be one of those "silly restrictions" that we choose to ignore, but it should probably be

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Tom Lane: We don't generally act that way in other ALTER commands and I don't see a strong argument to start doing so here. [...] In short, I'm inclined to argue that this variant of ALTER TABLE should replace *all* the fields of the constraint with the same properties it'd have if you'd