Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: libintl is already coming in via frontend_stlib_code, so that's fine. So now I'm wondering if any other static clients of libpq-int.h (if there are any) need the ssl dependency too, for correctness, or if it's just me. Looks like it's just me. And using partial_dependency for th

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: (The [2] link is missing, I think.) Ah, sry. This is the link: https://github.com/wolfgangwalther/nixpkgs/commits/postgresql-libpq-curl/ It's the last two commits on that branch. I'm confused by this -- the build produces staticlibs alongside the dynamically linked ones, so

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: libpq.a libpq-oauth-18.a The libpq.a file has no references to dlopen, but plenty of references to curl stuff. Which references? libpq-oauth should be the only thing using Curl symbols: $ nm src/interfaces/libpq/libpq.a | grep --count curl 0 $ nm src/interfaces/

Re: [PoC] Federated Authn/z with OAUTHBEARER

Wolfgang Walther: > So yes, not related to your patch. I do understand that PostgreSQL's > autoconf build system is not designed for "static only", I am certainly > not expecting you to fix that. > > I think meson will do better here, but I was not able to make t

Re: Buildfarm: Enabling injection points on basilisk/dogfish (Alpine / musl)

Andrew Dunstan: On 2025-04-12 Sa 10:10 PM, Noah Misch wrote: On Sat, Apr 12, 2025 at 07:51:06PM +0200, Wolfgang Walther wrote: With injection points enabled, I get the following errors in test_aio: [15:14:45.408](0.000s) not ok 187 - worker: first hard IO error is reported: expected

Buildfarm: Enabling injection points on basilisk/dogfish (Alpine / musl)

I recently enabled more features on my two buildfarm animals basilisk and dogfish, which are running on Alpine with musl-libc in a docker container. --with-libnuma and --with-liburing seemed to work fine and have been enabled for the last few runs, but --enable-injection-points does not [1].

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: On Wed, Apr 9, 2025 at 4:42 PM Jelte Fennema-Nio wrote: I think your suggestion of not using any .so files would best there (from w user perspective). I'd be quite surprised if a static build still resulted in me having to manage shared library files anyway. Done this way in

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: It allows packagers to ship the OAuth library separately, so end users that don't want the additional exposure don't have to install it at all. Ah, this came in after I sent my other mail, with this foot-note: > Currently, the two build systems don't handle the "please build on

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: On Tue, Apr 8, 2025 at 9:32 AM Wolfgang Walther wrote: And that should also not be a problem for distributions - they could offer a libpq and a libpq_oauth package, where only one of them can be installed at the same time, I guess? * My outsider understanding is that

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: However, if the other deps are considered problematic as well, then the ship has already sailed, and there is not point for a special case here anymore. I think this line of argument is unlikely to find traction. Upthread there were people asking if we could maybe split out other

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: The currently proposed patch would have you package and install a separate .so module implementing OAuth, which the staticlib would load once when needed. Similarly to how you still have to somehow dynamically link your static app against Curl. As a staticlib user, how do you fee

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion: The above is discussing a patch to split this into its own loadable module. Wasn't sure where to put this exactly, the thread is long and I couldn't find any discussion around it: How does the proposal with a loadable module affect a static libpq.a? I have not tried, yet, bu

Re: Fwd: Re: proposal: schema variables

Bruce Momjian: Now, can people give feedback that they would want this committed to PostgreSQL? From a user's perspective: Yes! I've been waiting for this for a long time and I really hope this can go through, eventually. Best, Wolfgang

Re: Proposal: Role Sandboxing for Secure Impersonation

Jelte Fennema-Nio: I am extremely skeptical of something like SET ROLE WITH . Totally agreed on the security concerns here. We don't want to provide passwords in a SQL command. For the same reasons explained by Robert, we also tell people not to set user passwords using SQL, but to use the \pas

Re: Proposal: Role Sandboxing for Secure Impersonation

Michał Kłeczek: PostgREST does not know alice's password as it performs JWT based authentication. Yes, that's why I proposed an extension to support JWTs in the next sentence. Then PostgREST would not need to do any auth at all anymore. Best, Wolfgang

Re: Proposal: Role Sandboxing for Secure Impersonation

Eric Hanson: a) Transaction ("local") Sandbox: - SET LOCAL ROLE alice NO RESET; - SET LOCAL ROLE alice WITHOUT RESET; - BEGIN AS ROLE alice; Transaction-level sandboxes have the benefit that a pooler can simply start a new sandboxed transaction for each request and never have to worry about re

Re: proposal: schema variables

Pavel Stehule: (global (temp)) table can hold 0, 1 or more rows (and rows are always composite of 0..n fields). The variable holds a value of some type. Proposed session variables are like plpgsql variables (only with different scope). In Postgres there is a difference between a scalar variabl

Re: proposal: schema variables

Dmitry Dolgov: This sounds to me like an argument against allowing name clashing between variables and tables. It makes even more sense, since session variables are in many ways similar to tables. +1 My mental model of a session variable is similar to a single-row, optionally global temporary

Re: Fix port/pg_iovec.h building extensions on x86_64-darwin

Thomas Munro: Out of curiosity, is nixos deliberately using an old macOS deployment target or SDK, 10.something? I'm just wondering if our feature detection is working correctly on macOS/x86, because I'd expect real preadv/pwritev to be there from 11 onwards, and 11 is already out of support by

Fix port/pg_iovec.h building extensions on x86_64-darwin

enerated. The attached patch fixes those. Hopefully this can make it into the minor release next week. Best, Wolfgang [1]: https://hydra.nixos.org/build/276421287/nixlog/1 [2]: https://hydra.nixos.org/build/276419879/nixlog/1From c906b182b5503c9ceebf58e25e3496157c29dedb Mon Sep 17 00:00:00 2001 Fro

Re: Regression tests fail with tzdata 2024b

Tom Lane: I was wondering whether the timezone used by pg_regress could be made configurable. Yes, I understood that you were suggesting that. My point is that it wouldn't do you any good: you will still have to change any regression test cases that depend on behavior PST8PDT has/had that is d

Re: Regression tests fail with tzdata 2024b

Tom Lane: Also, as a real place to a greater extent than "PST8PDT" is, it's more subject to historical revisionism when somebody turns up evidence of local law having been different than TZDB currently thinks. I now tried all versions of tzdata which we had in tree back to 2018g, they all work

Regression tests fail with tzdata 2024b

mp - - Fri Feb 01 11:12:13 0044 PST BC + Fri Feb 01 11:12:13 0044 LMT BC (1 row) SELECT to_timestamp('-44-02-01 11:12:13','-MM-DD HH24:MI:SS'); to_timestamp - - Fri Feb 01 11:12:13 0044 PST BC + Fri

Re: Meson far from ready on Windows

Andres Freund: That's not necessarily true. The nix package manager and thus NixOS track all dependencies for a piece of software. If any of the dependencies are updated, all dependents are rebuilt, too. So the security concern doesn't apply here. There is a "static overlay", which builds ever

Re: Building with meson on NixOS/nixpkgs

Tristan Partin: On Fri Aug 9, 2024 at 11:14 AM CDT, Andres Freund wrote: commit 4d8de281b5834c8f5e0be6ae21e884e69dffd4ce Author: Heikki Linnakangas Date:   2024-07-27 13:53:11 +0300     Fallback to clang in PATH with meson [..] I think this is a bad change unfortunately - this way clang and l

Re: Building with meson on NixOS/nixpkgs

Tristan Partin: On Fri Aug 9, 2024 at 11:14 AM CDT, Andres Freund wrote: [..] commit a00fae9d43e5adabc56e64a4df6d332062666501 Author: Heikki Linnakangas Date:   2024-07-27 13:53:08 +0300     Fallback to uuid for ossp-uuid with meson [..] I think this is a redundant change with commit 2416fdb

Re: Meson far from ready on Windows

Andres Freund: FWIW, dynamic linking has a noticeable overhead on other platforms too. A non-dependencies-enabled postgres can do about 2x the connections-per-second than a fully kitted out postgres can (basically due to more memory mapping metadata being copied). But on windows the overhead is

Docs: Order of json aggregate functions

and json_agg_strict are out of place. Attached patch puts them in the right spot. This is the same down to v16. Best, WolfgangFrom ad857a824d893a3e421c6c577c1215f71c1ebfe3 Mon Sep 17 00:00:00 2001 From: Wolfgang Walther Date: Wed, 19 Jun 2024 19:40:49 +0200 Subject: [PATCH v1] Fix order of json

Re: RFC: adding pytest as a supported test framework

Jelte Fennema-Nio: As scripting languages go, the ones that are still fairly heavily in use are Javascript, Python, Ruby, and PHP. I think all of those could probably work, but my personal order of preference would be Python, Ruby, Javascript, PHP. Finally, I'm definitely biased towards using Py

Re: Build with LTO / -flto on macOS

Peter Eisentraut: On 04.06.24 18:41, Tom Lane wrote: Relevant to this: I wonder what we think the supported macOS versions are, anyway.  AFAICS, the buildfarm only covers current (Sonoma) and current-1 (Ventura) major versions, and only the latest minor versions in those OS branches. For other

Re: Build with LTO / -flto on macOS

ant to touch it. Fair enough! Hopefully my testing convinces more than the man pages ;) Best, WolfgangFrom 3ca5357bbdb9aae29a1785d5ca2179d6cca15cdd Mon Sep 17 00:00:00 2001 From: Wolfgang Walther Date: Sun, 2 Jun 2024 10:46:56 +0200 Subject: [PATCH v2] Make building with clang's LTO work on macOS When bu

Re: Build with LTO / -flto on macOS

Wolfgang Walther: Peter: I don't think we explicitly offer LTO builds as part of the make build system, so anyone trying this would do it sort of self-service, by passing additional options to configure or make.  In which case they might as well pass the -export_dynamic option along i

Re: Build with LTO / -flto on macOS

Peter Eisentraut: It's probably worth clarifying that this option is needed on macOS only if LTO is also enabled.  For standard (non-LTO) builds, the export-dynamic behavior is already the default on macOS (otherwise nothing in PostgreSQL would work). Right, man page say this: > Preserves al

Build with LTO / -flto on macOS

:00 2001 From: Wolfgang Walther Date: Sun, 2 Jun 2024 10:46:56 +0200 Subject: [PATCH v1] Make building with clang's LTO work on macOS When building with -flto the backend binary must keep many otherwise unused symbols to make them available to dynamically loaded modules / extensions. This has

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule: When you write RAISE NOTICE '%', x, then PLpgSQL parser rewrite it to RAISE NOTICE '%', SELECT $1 There is no parser just for expressions. That's why my suggestion in [1] already made a difference between: SELECT var; and SELECT col, var FROM table, var; So the "only requir

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule: The session variables can be used in queries, but should be used in PL/pgSQL expressions, and then the mandatory usage in FROM clause will do lot of problems and unreadable code like DO $$ BEGIN   RAISE NOTICE '% %', (SELECT x FROM x), (SELECT a,b FROM y); END $$ This require

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule: But in this case you could make variables and tables share the same namespace, i.e. forbid creating a variable with the same name as an already existing table. It helps, but not on 100% - there is a search path I think we can ignore the search_path for this discussi

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule: 2. But my main argument is, it is not really safe - it solves Peter's use case, but if I use a reverse example of Peter's case, I still have a problem. I can have a variable x, and then I can write query like `SELECT x FROM x`; but if somebody creates table x(x int), then the q

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 a

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 s

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 doubt

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 yo

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 develope

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" envi

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 s

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 outsi

Re: Building with musl in CI and the build farm

eaders [2]: https://cirrus-ci.com/task/5741892590108672From 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.y

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 activat

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 instal

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. This

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 shouldn't

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 or

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 fi

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 :{s

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 implemented

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 about

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, INHE

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 crea

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 i

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 of the

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 DEFAULT

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 th

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 operato

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 uniq

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 values

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, l

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 pg

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 u

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 i

Add ON CONFLICT DO RETURN clause

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 tha

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 pe

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 expression

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 a

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 expression

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 tr

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 rel

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 whethe

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 terminol

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 e

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 and*no

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 have

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 patc

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 refere

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. Eve

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 the

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 no

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 create

  1   2   >