Re: Repeatable Read Isolation Level "transaction start time"
"Peter J. Holzer" writes: > Again, I'm not arguing for such a change, but I'm wondering if recording > transaction_timestamp just after the snapshot might be a safe change or > whether that might break some assumption that programmers can currently > make. As I mentioned upthread, we currently promise that xact_start matches the query_start of the transaction's first statement. (I'm not sure how well that's documented, but the code goes out of its way to make it so, so somebody thought it was important.) Another issue with redefining things like that is that presumably, for a session that has issued BEGIN but not yet taken a snapshot, xact_start would have to read as NULL, misleadingly implying that the session doesn't have an open transaction. Lastly, wouldn't postponing the collection of the timestamp like that break the very property you want to promise, by making other transactions' timestamps nontrivially later than they are now? I think if we wanted to do something here, it'd make more sense to keep xact_start as it stands and introduce a new variable snapshot_timestamp or something like that. Then maybe we could have some guarantees about what you get when comparing other sessions' xact_start to your own snapshot_timestamp. But I'm not convinced we can really guarantee anything without reading the snapshot_timestamp within the snapshot-collecting critical section, and I'm not for that. regards, tom lane
Re: Repeatable Read Isolation Level "transaction start time"
Greg Sabino Mullane writes: > All we can guarantee > via pg_stat_activity is that if xact_start and query_start *are* identical, > no snapshot has been granted yet, Surely that's not true either. xact_start = query_start implies that the current statement is the first in its transaction (assuming sufficiently fine-grained clock timestamps, something I'm not sure is an entirely safe assumption). But if that statement is not simply a BEGIN, it's likely obtained its own transaction snapshot after a few microseconds. As long as "read the system clock" is a distinct operation from "read a snapshot", there are going to be skew issues here. We could maybe eliminate that by reading the clock while holding the lock that prevents commits while reading a snapshot, but I doubt that anybody is going to accept that on performance grounds. Adding a not-guaranteed-cheap syscall inside that extremely hot code path seems unsatisfactory. Also, we currently do guarantee that xact_start matches query_start for the first statement of the transaction (the converse of what I said above). Removing that guarantee in order to add some other one wouldn't necessarily please everybody. regards, tom lane
Re: grant connect to all databases
Matt Zagrabelny writes: > ...but I still cannot connect: > $ psql -d test -U alice > psql: error: connection to server on socket > "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: permission denied for > database "test" > DETAIL: User does not have CONNECT privilege. This shouldn't be happening, since as mentioned upthread our default for newly-created databases is that they have CONNECT granted to PUBLIC. It works fine for me, even for a user with no special permissions: postgres=# create user alice; CREATE ROLE postgres=# create database test; CREATE DATABASE postgres=# \c test alice You are now connected to database "test" as user "alice". If I explicitly revoke the privilege, I can duplicate your results: test=> \c postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# revoke connect on database test from public; REVOKE postgres=# \c test alice connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: permission denied for database "test" DETAIL: User does not have CONNECT privilege. I wonder if your puppet recipe is revoking that behind your back, or if you are using some modified version of Postgres with different ideas about default privileges. Looking at psql \l output for the test DB might be informative. regards, tom lane
Re: Error Building Postgres 17.0 from Source
Christophe Pettus writes:
> On Oct 4, 2024, at 12:05, Tom Lane wrote:
>> Yeah, that's what it looks like. I'm a bit confused though because
>> 16.x should have failed the same way: building our docs without local
>> DTDs has failed for well over a year now [1].
> To add confusion to the fire, I was successfully building pre-17 versions
> ("make world") locally on MacOS fine without having to install the
> documentation tools. It wasn't until 17 that it failed (and installing the
> doc tools as recommended fixed it).
If you had a locally installed copy of the required DTDs, it should've
worked, but otherwise I don't see how ... oh, wait. Were you building
from a source tarball? If so, the difference is that up to v16 we
included prebuilt HTML docs in the tarballs, but as of v17 we don't.
So "make world" now triggers a local docs build where before it
didn't.
You can restore things to the status quo ante if you also download
the now-separate docs tarball, eg
https://ftp.postgresql.org/pub/source/v17.0/postgresql-17.0-docs.tar.gz
and unpack that into the same place as the main source tarball.
regards, tom lane
Re: Error Building Postgres 17.0 from Source
Christophe Pettus writes: > On Oct 4, 2024, at 09:35, Corbin Cavolt wrote: >> I'm having a problem building Postgres 17.0 from source. I'm able to build >> all the 16.x versions just fine; I'm running into an error specifically with >> version 17.0. > You might try installing the Debian packages for the documentation tools; the > lack of them is the usual reason for errors of that sort: > > https://www.postgresql.org/docs/devel/docguide-toolsets.html#DOCGUIDE-TOOLSETS-INST-DEBIAN Yeah, that's what it looks like. I'm a bit confused though because 16.x should have failed the same way: building our docs without local DTDs has failed for well over a year now [1]. Perhaps you weren't trying to build the docs before? regards, tom lane [1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=969509c3f
Re: How to handle "could not find function xml_is_well_formed" when restoring database in Version 17
George Weaver writes: > I am testing upgrading from Version 13 to Version 17. I am getting the > following error when trying to restore a database in Version 17 (the > database was backed up from Version 13 using the Version 17 pg_dump): > pg_Restore: error: could not execute query: ERROR: could not find > function "xml_is_well_formed" in file "C:/Program > Files/PostgreSQL/17/lib/pgxml.dll" > Command was: CREATE FUNCTION public.xml_is_well_formed(text) RETURNS > boolean > LANGUAGE c IMMUTABLE STRICT > AS '$libdir/pgxml', 'xml_is_well_formed'; What you appear to have here is a pre-9.1 version of the xml2 extension. That is so old that you're going to have difficulty modernizing it. We used to provide scripts for converting those loose objects into extensions, but we got rid of them in v13, figuring that after ten years their usefulness had passed. I think what you will have to do is manually drop all the xml2 functions (look for pg_proc entries with '$libdir/pgxml' in probin) from the v13 database, then upgrade, then install the xml2 extension if you still want it. Fortunately that module only provided functions not datatypes, so this shouldn't be too painful. (Another way could be to manually remove those CREATE FUNCTION commands from the dump script.) I'm betting that this database has a lot of other deferred maintenance that you ought to think about while you're at it. If there are any other old-style extensions in there, better fix them up. regards, tom lane
Re: Reading execution plan - first row time vs last row time
=?iso-8859-2?Q?Pecs=F6k_J=E1n?= writes:
> We see significant difference in explain analyze Actual time in the first
> line of execution plan and Execution time in the last line of execution plan.
> What can be the reason?
The time reported for the top query node is just the time taken to
compute all the rows returned by the query. It doesn't account
for what might be done with the data afterward. A normal EXPLAIN
just drops that data on the floor, so there's not much time left
unaccounted-for. But if you're writing the data into a table via
EXPLAIN CREATE TABLE AS, that I/O would be extra.
regards, tom lane
Re: Failing GSSAPI TCP when connecting to server
Ron Johnson writes:
> On Sun, Sep 29, 2024 at 2:00 PM Peter wrote:
>> My application is trying to connect the database server, and meanwhile
>> tries to talk to the KDC server for a service ticket.
>> A configuration problem on the machine(s) can be ruled out,
> Famous last words.
The TCP trace looks like the client side is timing out too quickly
in the unsuccessful case. It's not clear to me how the different
Discourse version would lead to the Kerberos library applying a
different timeout. Still, it seems like most of the moving parts
here are outside of Postgres' control --- I don't think that libpq
itself has much involvement in the KDC communication.
I concur with looking at the Discourse release notes and maybe asking
some questions in that community.
regards, tom lane
Re: Regarding use of single column as primary key on partitioned table
Ron Johnson writes: > On Sat, Sep 28, 2024 at 12:39 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: >> On Friday, September 27, 2024, Durgamahesh Manne < >>> Can't we use primary key on singal column(betid) on partitioned table >>> rather than using composite key (placedon,betid)? >> No. It would be misleading to allow such a thing because a unique index >> can only span a single partition. > That is IMO a serious (and probably unfixable, given how PG stores tables) > flaw in PG's partitioning design. You can call it a flaw if you want, but it's an intentional design limitation. The only way to relax it would be to invent global indexes (that is, single indexes covering the entire partitioning tree), which would basically throw away every advantage of making a partitioned structure in the first place. If that's what you want, don't partition your table. regards, tom lane
Re: Request for Insights on ID Column Migration Approach
"Peter J. Holzer" writes:
> As you can see, adding the primary key takes just as much time as
> creating the unique index. So it doesn't look like PostgreSQL is able to
> take advantage of the existing index (which makes sense since it still
> has to create a new index).
No, but you can attach an existing unique index as a primary key:
regression=# create table t1 (f1 int not null);
CREATE TABLE
regression=# create unique index t1_f1 on t1(f1);
CREATE INDEX
regression=# alter table t1 add primary key using index t1_f1;
ALTER TABLE
If you build the unique index concurrently, this needn't involve
much downtime.
regards, tom lane
Re: Repeatable Read Isolation Level "transaction start time"
"Peter J. Holzer" writes: > On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote: >> This might well be a failure of imagination on my part, but when would >> it pragmatically matter that the snapshot is taken at the first >> statement as opposed to at BEGIN? > It may make a difference if you're comparing timestamps. > For example, if you're using isolation level REPEATABLE READ and > (mistakenly) assume that the snapshot is taken at BEGIN, you would > expect any transaction_timestamp() written by a different transaction > and readable by this transaction to be earlier than the > transaction_timestamp() of this transaction. > But that's wrong because the other transaction could have happened > entirely in the time between your BEGIN and the statement which actually > triggers the snapshot. I don't find that hugely compelling, because there's always going to be some skew between the time we read the clock for the timestamp and the time we obtain the snapshot. Admittedly, that would normally not be a very long interval if BEGIN did both things ... but on a busy system you could lose the CPU for awhile in between. regards, tom lane
Re: Repeatable Read Isolation Level "transaction start time"
Ron Johnson writes:
> But why does "SELECT 1;" need a snapshot? Heck, why does "SELECT
> ;" need a snapshot?
Because we're not going to analyze the statement in the amount of
depth needed to make that distinction before we crank up the
transactional machinery. If it says SELECT, it gets a snapshot.
regards, tom lane
Re: Issues with PostgreSQL Source Code Installation
Ayush Vatsa writes: > So actually I wanted to modify and playaround with the code, > hence I am using the github repo and building postgres from it. Seems reasonable. The tip of master branch is occasionally broken, but seldom for very long, and none of our buildfarm animals are reporting failures like this. It looks to me like there is something wrong with your libicu installation --- perhaps headers out of sync with shared library? regards, tom lane
Re: Repeatable Read Isolation Level "transaction start time"
Wizard Brony writes:
> But in my testing, I find that according to that statement, the transaction
> start time is actually "the start of the first non-transaction-control
> statement in the transaction" (as mentioned earlier in the section). Is my
> conclusion correct, or am I misunderstanding the documentation?
It's even looser than that, really: it's the first statement that
requires an MVCC snapshot. From memory, LOCK TABLE is an important
exception --- you can acquire table locks before pinning down
a snapshot, and this is important in some scenarios.
regards, tom lane
Re: Customize psql prompt to show current_role
Adrian Klaver writes:
> On 9/23/24 08:07, Dominique Devienne wrote:
>> I often resort to \conninfo, but it's less automatic and
>> harder to visually parse (IMHO) compared to a custom ad-hoc prompt.
> For me that shows the user that connected(session_user) not the
> current_user.
Worse than that: what it reports is libpq's PQuser(), that is the
name that was used to log in with. But if what you want is the
session_user, psql prompts already have %n for that.
regards, tom lane
Re: Customize psql prompt to show current_role
Laurenz Albe writes:
> To get the current role, psql would have to query the database whenever
> it displays the prompt. That would be rather expensive...
See previous discussion:
https://www.postgresql.org/message-id/flat/CAFj8pRBFU-WzzQhNrwRHn67N0Ug8a9-0-9BOo69PPtcHiBDQMA%40mail.gmail.com
At the time I didn't like the idea too much, but now that we've seen
a second independent request, maybe our opinion of its value should
go up a notch.
regards, tom lane
Re: IO related waits
veem v writes:
> Able to reproduce this deadlock graph as below. Now my question is , this
> is a legitimate scenario in which the same ID can get inserted from
> multiple sessions and in such cases it's expected to skip that (thus "On
> conflict Do nothing" is used) row. But as we see it's breaking the code
> with deadlock error during race conditions where a lot of parallel threads
> are operating. So how should we handle this scenario?
Do you have to batch multiple insertions into a single transaction?
If so, can you arrange to order them consistently across transactions
(eg, sort by primary key before inserting)?
regards, tom lane
Re: Why no pg_has_role(..., 'ADMIN')?
Robert Haas writes: > On Fri, Sep 20, 2024 at 2:34 PM Tom Lane wrote: >> I'm now inclined to add wording within the pg_has_role entry, along >> the lines of >> >> WITH ADMIN OPTION or WITH GRANT OPTION can be added to any of >> these privilege types to test whether ADMIN privilege is held >> (all six spellings test the same thing). > I don't have an opinion about the details, but +1 for documenting it > somehow. I also think it's weird that we have six spellings that test > the same thing, none of which are $SUBJECT. pg_has_role seems a little > half-baked to me... Yeah. I think the original idea was to make it as parallel to has_table_privilege and friends as we could (but why did we then stick a pg_ prefix on it?). So that led to MEMBER WITH GRANT OPTION, and then the other spellings seem to have come along later. regards, tom lane
Re: Why no pg_has_role(..., 'ADMIN')?
I wrote:
> Robert Haas writes:
>> I think this already exists. The full list of modes supported by
>> pg_has_role() is listed in convert_role_priv_string(). You can do
>> something like pg_has_role('alice', 'USAGE WITH ADMIN OPTION'). This
>> is not new: it worked in older releases too, but AFAIK it's never been
>> mentioned in the documentation.
> Surely that's a bad documentation omission.
Actually, it's not true that it's entirely undocumented, because the
text above the table that describes pg_has_role mentions
Optionally, WITH GRANT OPTION can be added to a privilege type to
test whether the privilege is held with grant option.
But I concur that it's not immediately obvious that that applies
to role membership, since we don't use the "grant option" terminology
for roles.
I'm now inclined to add wording within the pg_has_role entry, along
the lines of
WITH ADMIN OPTION or WITH GRANT OPTION can be added to any of
these privilege types to test whether ADMIN privilege is held
(all six spellings test the same thing).
regards, tom lane
Re: Why no pg_has_role(..., 'ADMIN')?
Robert Haas writes:
> I think this already exists. The full list of modes supported by
> pg_has_role() is listed in convert_role_priv_string(). You can do
> something like pg_has_role('alice', 'USAGE WITH ADMIN OPTION'). This
> is not new: it worked in older releases too, but AFAIK it's never been
> mentioned in the documentation.
Surely that's a bad documentation omission. Do we want to document
all the variants convert_role_priv_string allows? They appear
functionally equivalent, so I'd be inclined to document just one.
'USAGE WITH ADMIN OPTION' seems a reasonable choice.
regards, tom lane
Re: Need assistance in converting subqueries to joins
Siraj G writes: > Please find below the query in the format and its execution plan: [ blink... ] I'm not sure what you are using there, but it is *not* Postgres. There are assorted entries in the execution plan that community Postgres has never heard of, such as > -> Remove duplicate (P0, IS_SEC_FILT) rows using temporary table > (weedout) (cost=2085.53 rows=1988) (actual time=0.321..22600.652 > rows=10298 loops=1) > -> Single-row index lookup on P0 using IS_PROJ_PK > (IS_PROJ_GUID=T0.IS_PROJ_GUID, IS_REPOSITORY_ID=R0.REP_ID) (cost=0.63 > rows=1) (actual time=0.000..0.000 rows=1 loops=50) Maybe this is RDS, or Aurora, or Greenplum, or one of many other commercial forks of Postgres? In any case you'd get more on-point advice from their support forums than from the PG community. It looks like this is a fork that has installed its own underlying table engine, meaning that what we know about performance may not be terribly relevant. regards, tom lane
Re: glibc updarte 2.31 to 2.38
Paul Foerster writes: > we have SLES 15.5 which has glibc 2.31. Our admin told us that he's about to > install the SLES 15.6 update which contains glibc 2.38. > I have built our PostgreSQL software from source on SLES 15.5, because we > have some special requirements which the packages cannot fulfill. So I have > questions: > 1) Do I have to build it again on 15.6? No, I wouldn't expect that to be necessary. > 2) Does the glibc update have any impact? Maybe. We don't really track glibc changes, so I can't say for sure, but it might be advisable to reindex indexes on string columns. regards, tom lane
Re: CREATE DATABASE command concurrency
Christophe Pettus writes: >> On Sep 17, 2024, at 14:52, Wizard Brony wrote: >> What are the concurrency guarantees of the CREATE DATABASE command? For >> example, is the CREATE DATABASE command safe to be called concurrently such >> that one command succeeds and the other reliably fails without corruption? > The concern is that two different sessions issue a CREATE DATABASE command > using the same name? In that case, it can be relied upon that one will > succeed (unless it fails for some other reason, like lacking permissions), > and the other will receive an error that the database already exists. This is true, but it's possibly worth noting that the specific error message you get could vary. Normally it'd be something like regression=# create database postgres; ERROR: database "postgres" already exists but in a race condition it might look more like "duplicate key value violates unique constraint". In the end we rely on the system catalogs' unique indexes to detect and prevent race conditions of this sort. regards, tom lane
Re: question on plain pg_dump file usage
"Zwettler Markus (OIZ)" writes: > I have to do an out-of-place Postgres migration from PG12 to PG16 using: > pg_dump -F p -f dump.sql ... > sed -i "s/old_name/new_name/g" > psql -f dump.sql ... > Both databases are on UTF-8. > I wonder if there could be character set conversion errors here, as the data > is temporarily written to a plain text file. The dump script will include a "SET client_encoding" command to prevent that. regards, tom lane
Re: post-bootstrap init : permission denied pg_description
=?iso-8859-1?Q?Fran=E7ois?= SIMON writes: > So the problem seems to come from xlc, and only at initdb step. > I can see that initdb runs a backend postgres in single user mode. > And this is this backend, when compiled with xlc, that shows : > FATAL: permission denied for table pg_description Yeah. This seems like it must be an xlc code-generation bug. It's got nothing to do with filesystem permissions: you're failing an ACL check at the logical "table" level, which you should not be because single-user mode implies superuser permissions. We do have one buildfarm member building with 64-bit xlc on AIX 7.1: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hornet&dt=2024-09-11%2003%3A51%3A02 I see that that uses a bunch of odd CFLAGS, but they look like they are mostly to suppress compiler warnings. Another important bit of the recipe for AIX is -D_LARGE_FILES=1, but I'm not sure that that would have any impact during initdb (since we're not dealing with any large files at that point). You could perhaps try different -O optimization levels. Also make sure your xlc is up-to-date. regards, tom lane
Re: RLS and Table Inheritance
Sanjay Minni writes:
> Do RLS policies defined at the parent, work on the child (in Table
> inheritance).
> At the parent I have a column 'site_id' with an RLS policy that only rows
> with site_id = current_setting(curr_site_id) would be accessible.
> However the policy defined at the parent does not work for me in inherited
> tables. Is it intended that way ?
If you mean when accessing the child tables directly, yes.
regards, tom lane
Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC
Andreas Joseph Krogh writes: > Motivation: I have PowerBI users, with a separate ‘reporting’-role, accessing > a database and I want to prevent them from listing all tables, users, > databases > and view-definitions (to not see the underlying query). Postgres is not designed to support this requirement. > I'm evaluating this: > REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE SELECT > ON > ALL TABLES IN SCHEMA information_schema FROM PUBLIC; > Will this affect “normal behaviour”, ie. prevent the planner, or other > internal mechanisms, from working properly for sessions logged in with the > ‘reporting’-role? Probably 95% of that stuff will still work. By the same token, there are plenty of information-leaking code pathways that will still leak. For instance, your restricted user will have no trouble discovering the OIDs and names of all extant tables, using something like do $$ begin for tid in 1..100 loop if tid::regclass::text != tid::text then raise notice 'tid % is %', tid, tid::regclass; end if; end loop; end $$; Functions such as pg_describe_object still work fine, too. Experimenting with psql, a lot of stuff is broken as expected: busted=> \d mytable ERROR: permission denied for table pg_class but some things still work: busted=> \sf sin CREATE OR REPLACE FUNCTION pg_catalog.sin(double precision) RETURNS double precision LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$dsin$function$ This is pretty much the other side of the same coin. The reason you can still parse and plan a query is that it does not occur to large parts of the backend that there should be any reason to refuse to read a system catalog. That carries over to these operations as well. This recent thread might be enlightening: https://www.postgresql.org/message-id/flat/18604-04d64b68e981ced6%40postgresql.org If you have a requirement like this, I think the only safe way to meet it is to not give those users direct SQL access. Put some kind of restrictive app in front of the database. regards, tom lane
Re: Backward compat issue with v16 around ROLEs
Dominique Devienne writes:
> Hi. I'm going around in circles trying to solve an issue with our
> system when running against a PostgreSQL v16 server. Which is linked
> to the weakening of CREATEROLE to have more granular permissions.
I'm not entirely sure, but I think the relevant v16 change is that
CREATEROLE used to imply having ADMIN on every (non-superuser) role.
Now it doesn't, and you have to actually have a WITH ADMIN OPTION
grant. You do automatically get WITH ADMIN OPTION on roles you
create yourself --- but in this example, dd_owner did not create
dd_admin.
regards, tom lane
Re: Check used privilege in a statment
Ahmed Ibrahim writes:
> I am trying to know which privileges are used in a specific query but I am
> facing problems when I have nested queries. Where is the best place/hook I
> can check for all needed permissions for a query in the source? Currently,
> trying it in the exectuter start but the nested queries are problematic.
Nested queries are irrelevant, because the query is flat (or at least
the rangetable is) by the time it gets to the executor. See
ExecCheckPermissions.
regards, tom lane
Re: postgresql FDW vs dblink for DDL
Adrian Klaver writes: > On 9/9/24 03:24, Achilleas Mantzios - cloud wrote: >> And the thing is that this creation via DDL is inside our design. >> Certain users create some backup tables of the public data in their own >> schema (via our app), then do some manipulations on the public data, >> then restore to the public or merge with the backups. When done, those >> backup tables are dropped. So the DDL is inside the app. And the >> question was if dblink is my only option, in the sense of doing this in >> a somewhat elegant manner. (and not resort to scripts, etc) > My sense is yes, if you want to encapsulate all of this within the > database/app you will need to use dblink. postgres_fdw certainly can't do it, nor any other FDW -- the FDW APIs simply don't cover issuance of DDL. If you don't like dblink, you could consider writing code within plperlu or plpythonu or another "untrusted" PL, making use of whatever Postgres client library exists within that PL's ecosystem to connect to the remote server. It's also possible that there's some third-party extension that overlaps dblink's functionality. dblink sure seems like the path of least resistance, though. regards, tom lane
Re: infinite loop in an update statement
Fabrice Chapuis writes:
> why this update does not return instantly?
> UPDATE table_a a
> SET col1 = (SELECT MIN(b.col1)
> FROM table_b b
> WHERE b.col2 = a.col2)
Maybe query is waiting for a lock on one of those tables?
regards, tom lane
Re: Strange permission effect depending on DEFERRABILITY
Achilleas Mantzios - cloud writes:
> As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger. This
> function mariner_update_availability_date is supposed to be run by a
> user : cbt_results_import strippedof any privileges to the rest of the
> system. Here is what we get : when we SET the constraint of the last
> trigger to IMMEDIATE, the function runs on behalf of its owner
> (postgres) who has all needed privileges (as superuser) to run the
> update on mariner table and also run the triggers . However, when we run
> with this CONSTRAINT as DEFERRED then it seems to NOT run the last
> deferrable trigger as postgres.
AFAIR the trigger mechanisms do not change the execution environment.
If they did, then for example a trigger that stuffs CURRENT_USER into
a last_updated_by column would not give the desired results.
I'd suggest marking the problem trigger function as SECURITY DEFINER
if you want it to run as its owner.
regards, tom lane
Re: Foreign Data Wrappers
Gus Spier writes:
> If I understand the concepts correctly, FDW not only makes other databases
> available, FDW also offers access to .csv files, plain text, or just about
> anything that can be bullied into some kind of query-able order. Has anyone
> ever tried to connect to redis or elasticache? If so, how did it work out?
Looks like it's been done:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
(No warranty expressed or implied on the quality of these
particular FDWs.)
regards, tom lane
Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors
raf writes: > On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" > wrote: >> You specified “-1” so I don’t get why you believe pg_restore should be >> continuing to execute in the face of the SQL error. > The reason I believe pg_restore should be continuing to execute in the face of > the SQL error is because I didn't supply the -e option which is described > thusly in the pg_restore manual entry: But you'd better also read the para about -1: -1 --single-transaction Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN/COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error. regards, tom lane
Re: How to grant role to other user
Andrus writes: > Postgres 16 has user ingmar which is marked as superuser and has create > role rights: > CREATE ROLE ingmar WITH > LOGIN > SUPERUSER > INHERIT > CREATEDB > CREATEROLE > NOREPLICATION > BYPASSRLS > ENCRYPTED PASSWORD 'md5aaa790012b7aa47017f124e263d8'; > GRANT "240316_owner" TO ingmar; > GRANT eeva_owner TO ingmar WITH ADMIN OPTION; Those GRANTs are quite unnecessary when the grantee is a superuser. Superuser roles always pass every privilege check. > User ingmar creates role "ingmar.e" using > CREATE ROLE "ingmar.e" LOGIN > and tries to grant eeva_owner role to it using > GRANT "eeva_owner" TO "ingmar.e" > This command throws error > ERROR: permission denied to grant role "eeva_owner" Works for me. For that matter, given the GRANT WITH ADMIN OPTION, it works even if "ingmar" isn't a superuser. I'm betting you weren't actually operating as the "ingmar" role when you did that, but since you didn't show your steps in any detail, it's hard to say where you went wrong. regards, tom lane
Re: Remedial C: Does an ltree GiST index *ever* set recheck to true?
Morris de Oryx writes: > From what I've seen in the wild, and can sort out from the source, I think > that ltree does *not* need to load rows from heap. The comment in ltree_consistent is pretty definitive: /* All cases served by this function are exact */ *recheck = false; > I wonder because an ltree GiST index is "lossy" and this behavior is more > like a lossless strategy. I think that's either because I've misunderstood > what "lossy" means in this case, or it's because ltree GiST index *pages *are > based on a signature (lossy), while ltree GiST index *leaf entries* contain > the full tree/path (lossless.) Yeah, the code is not terribly well commented but this bit in ltree.h appears to be saying that leaf entries contain the original ltree: * type of index key for ltree. Tree are combined B-Tree and R-Tree * Storage: *Leaf pages *(len)(flag)(ltree) *Non-Leaf * (len)(flag)(sign)(left_ltree)(right_ltree) *ALLTRUE: (len)(flag)(left_ltree)(right_ltree) and that seems consistent with the fact that ltree_consistent does different things at leaf and non-leaf levels. regards, tom lane
Re: Analytic Function Bug
Rumpi Gravenstein writes: > Is this a PostgreSQL bug? Maybe, but you haven't provided enough information to let anyone else reproduce the behavior. Personally I'm suspicious that because your lag() calls are over partition by p.logical_partition_key, p.txt order by indx but then you filter by where logical_partition_key='TEST_DATA' and usage_text='F(T61)(EXPORT)'; that the lag() functions are seeing some rows that don't show up in the final output. (This'd require that some output rows from "parse" share txt values but not usage_text values, but that certainly looks like it's possible.) So IMO you have not actually demonstrated that there is any bug. regards, tom lane
Re: tsvector limitations - why and how
Stanislav Kozlovski writes: > I was aware of the limitations of > FTS<https://www.postgresql.org/docs/17/textsearch-limitations.html> and tried > to ensure I didn't hit any - but what I missed was that the maximum allowed > lexeme position was 16383 and everything above silently gets set to 16383. I > was searching for a phrase (two words) at the end of the book and couldn't > find it. After debugging I realized that my phrase's lexemes were being set > to 16383, which was inaccurate. > ... > The problem I had is that it breaks FOLLOWED BY queries, essentially stopping > you from being able to match on phrases (more than one word) on large text. Yeah. FOLLOWED BY didn't exist when the tsvector storage representation was designed, so the possible inaccuracy of the lexeme positions wasn't such a big deal. > Why is this still the case? Because nobody's done the significant amount of work needed to make it better. I think an acceptable patch would have to support both the current tsvector representation and a "big" version that's able to handle anything up to the 1GB varlena limit. (If you were hoping for documents bigger than that, you'd be needing a couple more orders of magnitude worth of work.) We might also find that there are performance bottlenecks that'd have to be improved, but even just making the code cope with two representations would be a big patch. There has been some cursory talk about this, I think, but I don't believe anyone's actually worked on it since the 2017 patch you mentioned. I'm not sure if that patch is worth using as the basis for a fresh try: it looks like it had some performance issues, and AFAICS it didn't really improve the lexeme-position limit. (Wanders away wondering if the expanded-datum infrastructure could be exploited here...) regards, tom lane
Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
David Rowley writes: > On Tue, 27 Aug 2024 at 13:40, Tom Lane wrote: >> Yeah, it looks like that condition on "table_name" is not getting >> pushed down to the scan level anymore. I'm not sure why not, >> but will look closer tomorrow. > So looks like it was the "Make Vars be outer-join-aware." commit that > changed this. Yeah, I got that same result by bisecting. It seems like it's somehow related to the cast to information_schema.sql_identifier: we are able to get rid of that normally but seem to fail to do so in this query. There was a smaller increase in the runtime at dfb75e478 "Add primary keys and unique constraints to system catalogs", but that seems to just be due to there being more rows in the relevant catalogs. (That's from testing the query in an empty database; probably the effect of dfb75e478 would be swamped in a production DB anyway.) regards, tom lane
Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
nikhil raj writes:
> I've encountered a noticeable difference in execution time and query
> execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when
> running a query on information_schema tables. Surprisingly, PostgreSQL 16
> is performing slower than PostgreSQL 13.
Yeah, it looks like that condition on "table_name" is not getting
pushed down to the scan level anymore. I'm not sure why not,
but will look closer tomorrow.
regards, tom lane
Re: Where is my app installed?
Adrian Klaver writes: > On 8/22/24 17:36, Arbol One wrote: >> After installing PostgreSQL on my Debian-12 machine, I typed 'postgres >> --version' and got this msg: >> *bash: postgres: command not found* >> 'psql --version', however, does work and gives me this message : >> *psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)* >> Obviously postgres is not in the path, but I don't know where the >> 'apt-get' installed it or why it did not add it to the path. > As to where the postgres command is: > ls -al /usr/lib/postgresql/16/bin/ Theory 1: postgres is packaged in a "postgresql-server" package and the OP only installed the base (client-side) package. Theory 2: postgres is installed into some directory not in the OP's PATH, such as /usr/sbin. Since it's primarily used as a daemon, this'd be a reasonable thing for a packager to do. I'd bet a nickel on #1, though, because I've not seen too many packagers put postgres somewhere other than where they put psql. "Separate server package" is extremely common though. regards, tom lane
Re: Is there a way to translate pg_amop.amopstrategy into a description?
Morris de Oryx writes: > What I'm hoping for is a function like > get_opt_class_strategy_description(optclass, straregy_number) I've > looked at the source a bit, and it seems that there is no such > function, and that it might well be difficult to implement. The > strategy numbers are, as far as I can see, local to the specific > opt_class, which has no requirement to label them in any particular > way. That's correct. For btree and hash, the meanings of the strategy numbers are determined by the index AM; but for (IIRC) all of our other index AMs they're determined by the individual opclass. So anything like this would have to be implemented by dedicated code in each opclass. Perhaps that's worth doing, but it'd be a fair amount of work. regards, tom lane
Re: unable to upgrade postgres extensions
plsqlvids01 plsqlvids01 writes: > AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per > https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x > am > trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the > same error, how to upgrade them? You seem to be assuming that PG extensions have versioning matching the core server. There's no requirement for that, and it doesn't look like these extensions attempt to keep those numbers in sync. The underlying compiled modules (.so files) do need to be updated for each major server version. But if they load and work then that's happened. The "extension version" is just an arbitrary identifier for the version of the extension's SQL-level declarations, which frequently don't need to change for a server update. regards, tom lane
Re: security invoker review need full select (all columns) to do DML?
Dean Rasheed writes: > The user must have select permissions on all columns selected by the > subquery/view, because we don't go through the outer query to check > which columns are actually referred to. Now maybe we could, but I > suspect that would be quite a lot of effort because you'd need to be > sure that the column wasn't referred to anywhere in either the outer > query or the subquery itself (e.g., in WHERE clauses, etc.). I'd argue that we should check that permission regardless, and are probably required to by the SQL spec. You don't normally get to escape permission checks when bits of the query are optimized away. (This is why permission checks are done on the range table not the plan tree.) regards, tom lane
Re: Looking for pg_config for postgresql 13.16
Ron Johnson writes: > On Tue, Aug 20, 2024 at 11:56 AM H wrote: >> I am looking for pg_config for postgresql 13.16 that I run under Rocky >> Linux 9. It seems RL appstream latest version is pg_config in >> libpq-devel-13.11-1.el9.x86_64 but dnf complains: >> installed package postgresql13-"devel-13.16-2PGDG.rhel9.x86_64 obsoletes >> libpq-devel <= 42.0 provided by libpq-devel-13.11-1.el9.x86_64 from >> appstream" > That doesn't make sense. /usr/pgsql-13/bin/pg_config should be in plain > old postgresql13. I don't think the error is complaining that pg_config appears in both packages, it's just telling you that they are marked as being incompatible with each other. (There might be other files that are in both of those packages.) The easiest fix is likely to remove the libpq-devel package, expecting postgresql13-devel to provide whatever you needed from that. regards, tom lane
Re: use of postgres reg* datatypes in user tables?
plsqlvids01 plsqlvids01 writes:
> What is the use of postgres regclass datatype in user tables? When would
> one want to use them when regular data types are available? If any tables
> have these data types, since pg_upgrade would fail, what data types should
> those be converted to?
Indeed, storing reg* types in user tables isn't particularly
recommended because of the pg_upgrade problem. However, casting
to a reg* type is plenty useful in queries on the system catalogs.
There is more info and examples in the manual:
https://www.postgresql.org/docs/current/datatype-oid.html
regards, tom lane
Re: array_agg() does not stop aggregating according to HAVING clause
Dimitrios Apostolou writes: > I have a query that goes through *billions* of rows and for the columns > that have an infrequent "datatag" (HAVING count(test_datatag_n)<10) it > selects all the IDs of the entries (array_agg(run_n)). Here is the full > query: > INSERT INTO infrequent_datatags_in_this_chunk >SELECT datatag, datatags.datatag_n, array_agg(run_n) > FROM runs_raw > JOIN datatags USING(datatag_n) > WHERE workitem_n >= 295 >AND workitem_n < 714218 >AND datatag IS NOT NULL > GROUP BY datatags.datatag_n > HAVING count(datatag_n) < 10 >AND count(datatag_n) > 0 -- Not really needed because of the JOIN > above > ; > The problem is that this is extremely slow (5 hours), most likely because > it creates tens of gigabytes of temporary files as I see in the logs. I > suspect that it is writing to disk the array_agg(run_n) of all entries and > not only those HAVING count(datatag_n)<10. Well, yes: the two aggregates (array_agg and count) are computed concurrently in a single Aggregate plan node scanning the output of the JOIN. There's no way to apply the HAVING filter until after the aggregation is finished. I think this approach is basically forced by the SQL standard's semantics for grouping/aggregation. > How do I tell postgres to stop aggregating when count>=10? The only way to do this would be to do two separate passes of aggregation in separate sub-queries. Perhaps like WITH rare AS ( SELECT datatag_n FROM runs_raw WHERE workitem_n >= 295 AND workitem_n < 714218 AND datatag IS NOT NULL GROUP BY datatag_n HAVING count(datatag_n) < 10 AND count(datatag_n) > 0 ) INSERT INTO infrequent_datatags_in_this_chunk SELECT datatag, datatags.datatag_n, array_agg(run_n) FROM runs_raw JOIN datatags USING(datatag_n) JOIN rare USING(datatag_n) GROUP BY datatags.datatag_n ; I can't tell from what you said which level the workitem_n and datatag conditions go at, so this is just a draft-quality query. But I think the structure is basically okay, given that you said datatag_n is unique in datatags (so there's no need to join it in the WITH step). regards, tom lane
Re: PostgreSQL Upgrade Issue - Undefined Symbol Error
Vivek Gadge writes:
> I am encountering an error after completion of PostgreSQL upgradation from
> version 13.8 to 15.6. Trying to describe a single table, I am receiving an
> error.
> psql: symbol lookup error: psql: undefined symbol: PQmblenBounded
This indicates that your psql executable is linking to an old version
of libpq.so. (Running "ldd" on the psql file should confirm that.)
You may have a problem with the library search path being used by the
dynamic loader.
regards, tom lane
Re: Problem with a Query
Siraj G writes: > We migrated a PgSQL database from Cloud SQL to compute engine and since > then there is a SQL we observed taking a long time. After some study, I > found that the SQL is using NESTED LOOP where the cost is too high. The core of your problem seems to be here: > -> Index Scan using marketing_a_cancel__55_idx on > marketing_app_leadhistory w0 (cost=0.57..4274.30 rows=1 width=8) (actual > time=46.678..51.232 rows=44 loops=1) >Index Cond: ((cancel_event_id IS NOT NULL) AND > (cancel_event_type = 1)) >Filter: ((status_id = 93) AND > ((followup_date)::date >= '2024-08-01'::date) AND ((followup_date)::date <= > '2024-08-07'::date)) >Rows Removed by Filter: 22268 >Buffers: shared hit=9170 read=19 If the planner had estimated 40-some rows out of this step, rather than one, it would certainly not have chosen to use nestloop joins atop this. So the big problem to focus on is making that estimate better. A secondary problem is that the choice of index seems poor: the index itself is selecting 44+22268 = 22312 rows and then the filter condition is throwing away 99.8% of those rows. Probably, using an index on (status_id, followup_date) would have worked better. I suspect that both of these things are tied to the non-normalization of your "cancel" condition. The planner probably believes that "cancel_event_id IS NOT NULL" is statistically independent of "cancel_event_type = 1"; but I'll bet it isn't, and thus the index condition selects many more rows than the planner guessed. You might be able to improve that estimate by creating extended stats on both of those columns, but really a better idea would be to take a step back and figure out if those two columns can't be merged into one. regards, tom lane
Re: libpq version macro to use or not PQsocketPoll
Dominique Devienne writes: > Hi. Now that v17beta2 is part of my dev-env, I'm trying out the new API. > And I discover there's no version macro for conditional compilation in > LIBPQ... Indeed, that's an oversight, and there's a number of other things we added to libpq-fe.h between 16 and 17 that probably deserve their own LIBPQ_HAS symbols. > I'm not sure what's so wrong with version macro as such. A couple of things: * It doesn't help if we forget to update it, so it's not really better than HAS_ symbols on that score. * Usage is error-prone (you might test for the wrong cutoff version) and not very readable. * We can't retroactively make such a symbol appear in old copies of libpq-fe.h. regards, tom lane
Re: UPDATE-FROM and INNER-JOIN
Dominique Devienne writes: > The reason I find the restriction damaging is that `FROM t1, t2 WHERE > t1.c1 = t2.c2` > is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2 > ON t1.c1 = t2.c2` > which IMHO better separates "filtering" from "joining" columns. FWIW. But the whole point of that syntax is to be explicit about which tables the ON clause(s) can draw from. If we had a more complex FROM clause, with say three or four JOINs involved, which part of that would you argue the UPDATE target table should be implicitly inserted into? The only thing that would be non-ambiguous would be to require the target table to be explicitly named in FROM (and not treat that as a self-join, but as the sole scan of the target table). Some other RDBMSes do it like that, but it seems like too much of a compatibility break for us. Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE, so they offer no guidance. But I doubt we are going to change this unless the standard defines it and does so in a way that doesn't match what we're doing. regards, tom lane
Re: UPDATE-FROM and INNER-JOIN
Dominique Devienne writes: > In https://sqlite.org/forum/forumpost/df23d80682 > Richard Hipp (Mr SQLite) shows an example of something > that used to be supported by SQLite, but then wasn't, to be > compatible with PostgreSQL. For the archives' sake: CREATE TABLE t1(aa INT, bb INT); CREATE TABLE t2(mm INT, nn INT); CREATE TABLE t3(xx INT, yy INT); UPDATE t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa; yields ERROR: column "aa" does not exist LINE 1: ... t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa; ^ DETAIL: There is a column named "aa" in table "t1", but it cannot be referenced from this part of the query. > Thus I'm curious as to why PostgreSQL refuses the first formulation. > Could anyone provide any insights? Thanks, --DD This seems correct to me. The scope of the ON clause is just the relations within the INNER JOIN, which does not include t1. You would get the same from SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa; ERROR: column "aa" does not exist LINE 1: SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa; ^ because again t1 is not part of the JOIN sub-clause. (MySQL used to get this wrong, many years ago, and it seems that has taught a lot of people some strange ideas about syntactic precedence within FROM clauses. Postgres' behavior agrees with the SQL spec here.) regards, tom lane
Re: Can't change tcp_keepalives_idle
"Abraham, Danny" writes: > Change tcp_keepalives_idle in postgresql.conf from 0 to 300 , then pg_ctl > reload but it stays with the old 0 value. > Tried restart and it did not work. PG Log says it was changed. tcp_keepalives_idle isn't implemented on all platforms. But I'd expect to see a LOG message in the postmaster log about that, either something about "setsockopt failed" or "WSAIoctl failed" or "setting the keepalive idle time is not supported". Also, if you inquire the value on a non-TCP connection, you'll get zero because it's not relevant. regards, tom lane
Re: Subscribe to mailing list - General Question
Adrian Klaver writes:
> On 8/1/24 20:53, Sindhu Selvaraj wrote:
>> I am reporting this as a bug. Please keep us updated.
> That should be done here:
> https://www.postgresql.org/account/submitbug/
> FYI, you will need to set up a community account to do that.
I think this is the same thing that was reported yesterday:
https://www.postgresql.org/message-id/flat/18562-46a7ee9a7b1ee153%40postgresql.org
regards, tom lane
Re: VACUUM on temp table blocks VACUUM on another...
Jim Vanns writes: > I have two sessions, each creating a temporary table of the same name - > 'foobar'. Why would a vacuum of 'foobar' from one session block the vacuum > of 'foobar' in the other session? That's fairly hard to believe. Can you provide a self-contained test case? regards, tom lane
Re: Issue while creating index dynamically
Ron Johnson writes: > On Tue, Jul 23, 2024 at 4:10 PM veem v wrote: >> But we are getting an error while executing saying it cant be executed in >> transaction block with "CONCURRENTLY". So I want to understand , is there >> any alternate way to get away with this? > I'd write that in bash, not in a DO block. Yeah. I thought for a bit about using contrib/dblink to carry out the commands in a different session, but I don't think that'll work: CREATE INDEX CONCURRENTLY would think it has to wait out the transaction running the DO block at some steps. Shove the logic over to the client side and you're good to go. regards, tom lane
Re: psql sslmode behavior and trace_connection_negotiation in PG17
Hao Zhang writes: > I tried to connect with psql + client sslmode = require + server requiring > ssl with PG17 and trace_connection_negotiation = "on". So "SSLRequest > accepted" is logged twice with two different PID. I believe the PID 15553 > is psql and 15554 is the PG backend. Certainly not: psql has no ability to write to the postmaster log. These PIDs are two different backend sessions. > How do you explain the two connections > with SSLRequest? > 2024-07-17 03:06:54.492 PDT [15553] LOG: connection received: > host=127.0.0.1 port=54002 > 2024-07-17 03:06:54.492 PDT [15553] LOG: SSLRequest accepted > 2024-07-17 03:06:59.982 PDT [15554] LOG: connection received: > host=127.0.0.1 port=54004 > 2024-07-17 03:06:59.982 PDT [15554] LOG: SSLRequest accepted > 2024-07-17 03:06:59.994 PDT [15554] LOG: connection authenticated: > identity="postgres" method=md5 (/usr/local/pgsql/data/pg_hba.conf:18) > 2024-07-17 03:06:59.994 PDT [15554] LOG: connection authorized: > user=postgres database=postgres application_name=psql SSL enabled > (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256) The first connection comes from psql trying to connect and discovering that a password is required. It gives up on that connection because it hasn't got a password, and asks the user (you) for that. We can see from the log that it took about five-n-a-half seconds for you to type your password, and then there was a second connection attempt that was successful. By default, the server doesn't log unceremonious client disconnections after the password challenge, precisely because psql behaves this way. So that's why we don't see any disconnection log entry from the ill-fated 15553 session. I kind of wonder if we could suppress these other log entries too, but it's not very clear how. If this behavior really annoys you, you can use psql's -W switch to force it to prompt for a password in advance of knowing whether the server will demand one. regards, tom lane
Re: Issue with configuration parameter "require_auth"
"Gaisford, Phillip" writes: > So what would a connection URI using require_auth properly look like? Here’s > what I have been trying: > url: > "postgres://postgres:postgres@localhost:5432/aioli?require_auth=password&application_name=aioli-controller&sslmode=disable&sslrootcert=", Hm, that looks right (and behaves as-expected here). What version of what client-side stack are you using? For me, libpq 16 and up recognize this parameter, while older versions fail with psql: error: invalid URI query parameter: "require_auth" If you're using some other client driver, maybe it doesn't know this parameter and guesses that it should be passed to the server. regards, tom lane
Re: Issue with configuration parameter "require_auth"
"Gaisford, Phillip" writes: > Using golang sqlx.Connect on the client side, the connection fails and my > Postgresql 16 server logs the following: > 2024-07-17 14:42:07.285 UTC [115] FATAL: unrecognized configuration > parameter "require_auth" require_auth is a connection parameter, like host or port or dbname. You seem to be trying to specify it as a server parameter, ie inside the "options" connection parameter. regards, tom lane
Re: Support of Postgresql 15 for Sles15Sp6
Adrian Klaver writes: > On 7/17/24 03:37, Akram Hussain wrote: >> Is there any planned release of postgresql 14 for Sles15SP6. > Your subject says 'Support of Postgresql 15 ...' which version do you want? The answer's the same either way. The prebuilt packages the PG community makes for SUSE are described here: https://www.postgresql.org/download/linux/suse/ If you don't want those, and you don't want the ones that SUSE themselves build, your third option is to build from source. Offhand I would think that the community RPMs would serve you fine. The page above says they are built for SLES 15 SP5, but SUSE would have to have screwed up pretty badly for a package built on SP5 to not run on SP6. regards, tom lane
Re: Running psql in a docker container
H writes:
> Understood but how should formulate the initdb statement to accomplish what I
> want on pgsql 16 since the syntax I used for pgsql 13 does not work in my
> container?
You still haven't shown us the actual error message, so we're all
just guessing.
I will offer a guess though. This *should* work, since Rocky 9
is a direct descendant of RHEL/CentOS 7. The only reason I can
think why it wouldn't is that you haven't installed the OS package
that defines en_US.UTF-8. Try doing "locale -a" and see if
en_US.UTF-8 is among the listed locales.
On my RHEL8 box, it looks like glibc-locale-source is what
provides most non-C locales.
regards, tom lane
Re: Running psql in a docker container
H writes: > Good question! ”No success” meant that I have not been able to figure out how > to have new databases default to en_US.UTF-8, instead they default to C.UTF-8. The default for an installation is determined at initdb time, either with an explicit locale switch or from the environment locale settings. regards, tom lane
Re: Query on partitioned table needs memory n_partitions * work_mem
Dimitrios Apostolou writes: > The TABLE test_runs_raw has 1000 partitions on RANGE(workitem_n). So don't do that. Adding partitions is not cost-free. regards, tom lane
Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE
"David G. Johnston" writes: > On Mon, Jul 8, 2024 at 3:08 PM Tom Lane wrote: >> That doesn't make it not a bug. > Fair, the code was from a time when membership implied SET permission which > apparently was, IMO, still is, a sufficient reason to allow a member of > that group to login. > By making SET optional we removed this presumption and broke this code and > now need to decide what is a valid setup to allow logging in. So, yes, a > bug. Yeah, that's what I'm thinking --- I also think the current behavior was an oversight rather than an intentional decision. Possibly Robert remembers differently though. > So long as we document that being a member of a group grants you the right > to login if that group is named in pg_hba.conf I don't see how that is an > invalid specification. It also isn't self-evidently correct. If we do > agree that the status quo is undesirable the change I'd expect is to > require at least one of the membership options to be true in order to be > allowed to login. I'd argue that INHERIT TRUE should be required. The point of SET TRUE with INHERIT FALSE is that you must *explicitly* do SET ROLE or equivalent in order to have access to the privileges of the referenced role. There is no opportunity to issue SET ROLE before logging in, but ISTM that means you don't get the privilege, not that you magically get it without asking. Otherwise, why did we build this additional level of grant specificity at all? I follow the use-case of wanting to be able to log into a database where your only useful privileges are locked behind SET requirements. But I'd still argue that you should need to be able to log into that database using your own privileges. An analogous case is that having some privileges on table(s) in some schema does not give you the right to bypass needing USAGE on the schema in order to access those tables. > I wish we could break even > allowing all-false as an active state but that seems too late to try and > do. I suppose that was intentional, but I too wonder about the use-case. I guess it's analogous to, for example, zero-column tables. That is, it's not terribly useful as a static state but it might be a state you pass through in a series of manipulations. Forbidding it would just create a roadblock that'd have to be worked around, for example by being very careful about the ordering of GRANT/REVOKE commands. regards, tom lane
Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE
"David G. Johnston" writes: > On Mon, Jul 8, 2024 at 2:16 PM Tom Lane wrote: >> Pavel Luzanov writes: > On 08.07.2024 22:22, Christophe Pettus wrote: >>>> This is more curiosity than anything else. In the v16 role system, is >>>> there actually any reason to grant membership in a role to a different >>>> role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role >>>> granted membership gain any ability it didn't have before in that case? >>> Looks like there is one ability. >>> Authentication in pg_hba.conf "USER" field via +role syntax. >> Hmm, if that check doesn't require INHERIT TRUE I'd say it's >> a bug. > The code doesn't support that claim. That doesn't make it not a bug. Robert, what do you think? If this is correct behavior, why is it correct? regards, tom lane
Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE
Pavel Luzanov writes: > On 08.07.2024 22:22, Christophe Pettus wrote: >> This is more curiosity than anything else. In the v16 role system, is there >> actually any reason to grant membership in a role to a different role, but >> with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted >> membership gain any ability it didn't have before in that case? > Looks like there is one ability. > Authentication in pg_hba.conf "USER" field via +role syntax. Hmm, if that check doesn't require INHERIT TRUE I'd say it's a bug. regards, tom lane
Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE
Christophe Pettus writes: >> On Jul 8, 2024, at 13:25, Laurenz Albe wrote: >> I didn't test it, but doesn't that allow the member rule to drop objects >> owned >> be the role it is a member of? > No, apparently not. IIUC, you need at least one of SET TRUE and INHERIT TRUE to be able to access the privileges of the role you are nominally a member of. This extends to ownership checks as well as grantable privileges. regards, tom lane
Re: Declaring a field that is also an out parameter in a function
Pavel Stehule writes:
> (2024-07-07 09:27:14) postgres=# select * from
> plpgsql_check_function('test_function');
> ┌───┐
> │plpgsql_check_function │
> ╞═══╡
> │ warning:0:10:statement block:parameter "d3" is overlapped │
> │ Detail: Local variable overlap function parameter.│
Nice! FWIW, I think the standard terminology is "local variable
shadows function parameter".
regards, tom lane
Re: Declaring a field that is also an out parameter in a function
Michael Nolan writes:
> Shouldn't declaring a field that is also an OUT parameter throw an error?
No. The DECLARE is a block nested within the function,
and the parameter is declared at function scope.
So this is a standard case of an inner declaration masking
an outer one.
Possibly plpgsql_check can be set to complain about such cases,
but they're legal according to the language specification.
regards, tom lane
Re: Can a long running procedure detect when smart shutdown is pending?
Laurenz Albe writes: > On Fri, 2024-07-05 at 14:12 -0400, Dennis White wrote: >> My project's DB has a mutli-step stored procedure using Transaction Control >> that may take 30 minutes or more to complete. >> I am curious if there is a way to make it more smart shutdown friendly so it >> can stop between steps? > I don't think there is a direct way to do that in SQL; that would require a > new > system function that exposes canAcceptConnections() in SQL. It's worse than that: the state variables involved are local to the postmaster, so you wouldn't get the right answer in a backend even if the function were reachable. > What you could do is use the dblink extension to connect to the local > database. > If you get an error "the database system is shutting down", there is a smart > shutdown in progress. This'd probably work. Ugly, but ... regards, tom lane
Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC
"Tefft, Michael J" writes:
> I apologize, that was sloppy.
> I was using the acldefault() function with pg_roles, like this:
> => select rolname, acldefault('f',oid) from pg_roles where rolname like
> 'mjt%' order by 1;
Ah, yeah, that always shows the *built in* default privileges for a
given object kind and owner. If there's a relevant entry in
pg_default_acl, it overrides the built-in default during object
creation.
regards, tom lane
Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC
"Tefft, Michael J" writes:
> I was checking pg_roles.acl_default to see if my role-level ALTER DEFAULT
> PRIVILEGES had been effective. But I see the same content both before and
> after the ALTEr.
Er, what? There's no column named acl_default in pg_roles, nor any
other standard PG view.
psql's "\ddp" command is the most usual way to examine current
defaults:
regression=# create user joe;
CREATE ROLE
regression=# ALTER DEFAULT PRIVILEGES FOR USER joe REVOKE EXECUTE ON FUNCTIONS
FROM public;
ALTER DEFAULT PRIVILEGES
regression=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
---++--+---
joe || function | joe=X/joe
(1 row)
regards, tom lane
Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC
"Tefft, Michael J" writes: > I am trying to remove the default grant of EXECUTE on all > functions/procedures to PUBLIC. >> From my reading, there is no straightforward way to do this. For example, > ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; > Does not apply this across the entire cluster (or database) but only applies > to the role who issued it (and objects yet to be created by that role) . > So I am arriving at the conclusion that I need to alter the default > privileges for every existing role (which I expected), and ensure that > default privileges are altered for every new role that is created going > forward. > Have I analyzed this correctly? You'll also need to repeat the ALTERs in each database of your installation. regards, tom lane
Re: psql help
"David G. Johnston" writes:
> On Thursday, July 4, 2024, Murthy Nunna wrote:
>> pg_terminate_backend(pid) will not work as it expects only one pid at a
>> time.
> Interesting…I wouldn’t expect the function calls to interact that
> way
TBH, my reaction to that was that the OP doesn't understand SQL
semantics. As you previously said, simply removing the LIMIT clause
should work fine. (The ORDER BY looks kinda pointless, too, unless
there are operational constraints we weren't told about.)
There is a question of exactly what "$a'${TIMEOUT_MINS}'$a"
is supposed to mean, but that's independent of the LIMIT issue.
regards, tom lane
Re: printing PGresult content with gdb
clippe...@gmx.fr writes:
> I don't know if it is the right mailing list, but i was
> wondering if one could introspect via gdb the content of PGresult.
You might have better luck with that if you install the debuginfo
RPM corresponding to your libpq RPM. PGresult's innards are not
exposed to applications by libpq-fe.h, so your own app's debug
data is not going to contain the details of the struct. But I think
it would be available to gdb if libpq's debug symbols were installed.
regards, tom lane
Re: Passing a dynamic interval to generate_series()
Igal Sapir writes:
> But this throws an error (SQL Error [42601]: ERROR: syntax error at or near
> "'1 '"):
> SELECT generate_series(
> date_trunc('month', current_date),
> date_trunc('month', current_date + interval '7 month'),
> interval ('1 ' || 'month')::interval
> )
You're overthinking it.
SELECT generate_series(
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
('1 ' || 'month')::interval
);
generate_series
2024-06-01 00:00:00-04
2024-07-01 00:00:00-04
2024-08-01 00:00:00-04
2024-09-01 00:00:00-04
2024-10-01 00:00:00-04
2024-11-01 00:00:00-04
2024-12-01 00:00:00-05
2025-01-01 00:00:00-05
(8 rows)
It might help to read this:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
and to experiment with what you get from the constituent elements
of what you tried, rather than trying to guess what they are from
generate_series's behavior. For example,
select (interval '1 ');
interval
--
00:00:01
(1 row)
select (interval '1 ' || 'month');
?column?
---
00:00:01month
(1 row)
regards, tom lane
Re: dblink Future support vs FDW
Dhritman Roy writes:
> My organization is building something which is using dblink and what we are
> building shall take several months and then to be offered to a client.
> I just wanted to check what is the future/roadmap of dblink?
> Is it going to be obsolete/removed from PostGreSQL in upcoming versions?
I don't foresee us removing it any time soon, because there are things
you can do with it that aren't possible in the FDW model
(particularly, issue DDL to the remote server). Even if we did kick
it out of the core distribution at some point, it could live on as an
externally-maintained extension.
Having said that, it is a bit of a development backwater, so you
should probably not count on anyone being interested enough to add
new features to it.
regards, tom lane
Re: Issue with installing postgres extension
bhavani dba writes:
> thank you for tom for your response. i dont remember installing postgres 14
> version on the server and it seems like I have postgres 15 running.
> [postgres@postgres ~]$ psql
> psql (15.7, server 15.0)
> Type "help" for help.
Hmm ... after poking around a bit, it appears you are trying to load
extension code compiled against 15.1 or later into a 15.0 server.
Possibly we should have thought a bit harder about the compatibility
implications of commit f2f7e509e. Nonetheless, what are you doing
running a 15.0 server? That was many minor releases and bug fixes
ago.
regards, tom lane
Re: Issue with installing postgres extension
bhavani dba writes: > postgres=# CREATE EXTENSION pg_stat_statements; > ERROR: could not load library > "/home/postgres/bin/pgsql/15/lib/pg_stat_statements.so": > /home/postgres/bin/pgsql/15/lib/pg_stat_statements.so: undefined symbol: > InitMaterializedSRF This certainly looks like you are trying to load a v15 extension into a pre-v15 server (since the InitMaterializedSRF function didn't exist in prior versions). I'm going to go out on a limb and guess that you just installed v15 and forgot to restart the server, so that what's running is still v14 or before. If so, you probably also forgot the pg_upgrade step --- you can't simply start a new major version in an old one's data directory. regards, tom lane
Re: Can any_value be used like first_value in an aggregate?
Bruno Wolff III writes: > For example, is output of 10 guaranteed in the following: > bruno=> select any_value(x order by x desc) from generate_series(1,10) as x; > any_value > --- > 10 > (1 row) Not really. It will work that way in simple cases, but I think the behavior stops being predictable if the input gets large enough to induce the planner to use parallel aggregation. In any case, the example shown isn't amazingly efficient since it'll still perform a sort to meet the ORDER BY spec. > The use case is that I want to return a value of one column that is paired > with the maximum value of another column in each group when using GROUP BY. Use window functions (i.e. first_value). This is what they're for, and they are smart enough to do just one sort for functions sharing a common window spec. regards, tom lane
Re: schema privileges and drop role
Matt Zagrabelny writes: > On Mon, Jun 24, 2024 at 7:03 PM Adrian Klaver > wrote: >> "A role cannot be removed if it is still referenced in any database of >> the cluster; an error will be raised if so. Before dropping the role, >> you must drop all the objects it owns (or reassign their ownership) and >> revoke any privileges the role has been granted on other objects. The >> REASSIGN OWNED and DROP OWNED commands can be useful for this purpose; >> see Section 22.4 for more discussion." > Cool. I gave it a try, but came up with the same error: > test_db=# REASSIGN OWNED by legacy_owner TO new_owner; > REASSIGN OWNED > Time: 0.212 ms > test_db=# drop role legacy_owner; > ERROR: role "legacy_owner" cannot be dropped because some objects depend > on it You need to do DROP OWNED as well to get rid of those privileges. REASSIGN OWNED only changes the ownership of ownable objects. > I'd still like to see how to list the "privileges for schema > public", "\dn+ public" in psql would do that. regards, tom lane
Re: How to use createdb command with newly created user?
"David G. Johnston" writes: > On Sun, Jun 23, 2024, 11:43 毛毛 wrote: >> Then I run the following command on PowerShell on Windows 10: >> createdb -U Baba -W test_db >> But no mater how I tried, the password always failed. > You named the user "baba" all lower-case but your createdb command uses > Baba and in the OS the case-folding of identifiers does not happen. Baba > != baba is your issue. FWIW, I think using -W in interactive commands is a bad habit that you should drop. Because it forces a password prompt, it easily confuses people into thinking that their problem is password-related whether it actually is or not. (I notice that with createdb, it actually seems to force *two* password prompts when there is something wrong. That's unlike what happens with psql; maybe we should try to improve that. But on the other hand, it's hard to get excited about putting work into improving a behavior that we deprecate using at all.) regards, tom lane
Re: Stack Smashing Detected When Executing initdb
I wrote: > Xu Haorong writes: >> performing post-bootstrap initialization ... *** stack smashing detected >> ***: terminated >> Aborted (core dumped) >> child process exited with exit code 134 > No such problem is visible in our build farm [1], so what we have to > figure out is how your machine is different from all of those. Also, before you spend a lot of time chasing this, make sure it's not a mirage. Reset your source tree fully with "git clean -dfxq" then configure, make, make install; then see if problem still exists. If it does, the PG community's accumulated wisdom about getting stack traces is here: https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend Note that the "child process exited" message you show implies that the failure was not in initdb itself, but in the single-user postgres backend process that it spawns. This means that any core file would have been dumped into the created data directory, so you would have to use initdb's --no-clean option to prevent it from being removed immediately. Also, if you are using a systemd-based Linux distribution, you may have to negotiate with systemd-coredump to get back any core dump at all. "man 5 core" can be helpful reading here (personally I just disable systemd-coredump per the directions shown there). regards, tom lane
Re: Stack Smashing Detected When Executing initdb
Xu Haorong writes:
> Today I tried to build the latest devel version of PostgreSQL(commit
> 70a845c04a47645b58f8276a6b3ab201ea8ec426). The compilation was successful,
> but when I ran initdb an error occured:
> performing post-bootstrap initialization ... *** stack smashing detected ***:
> terminated
> Aborted (core dumped)
> child process exited with exit code 134
No such problem is visible in our build farm [1], so what we have to
figure out is how your machine is different from all of those.
You've provided exactly zero context:
* What platform is this on (be specific)? Is the system software
up-to-date?
* What C compiler are you using, and what version exactly?
* What configure options did you use?
regards, tom lane
[1] https://buildfarm.postgresql.org/cgi-bin/show_status.pl
Re: Password complexity/history - credcheck?
Martin Goodson writes: > Recently our security team have wanted to apply password complexity > checks akin to Oracle's profile mechanism to PostgreSQL, checking that a > password hasn't been used in x months etc, has minimum length, x special > characters and x numeric characters, mixed case etc. Don't suppose it would help to push back on whether your security team knows what they're doing. The really key reason why server-side password checks are not as bright an idea as they sound is that they cannot be implemented without forcing the client to transmit the password in cleartext. It's widely considered best practice if the server *never* sees the user's cleartext password, because then it can't leak, either from sniffing the connection or scraping the postmaster log. I believe that practices such as forcing a password change every X amount of time are not viewed as favorably as they once were, either. (The argument is that that discourages users from putting any serious thought into choosing an uncrackable password, and might well encourage them to write down their current and last few passwords somewhere.) Anyway, considerations like these are why there's not features of this sort in community PG. You can use an extension that applies some checks, but there's no good way around the "needs cleartext password" problem for that. regards, tom lane
Re: pg_dump restores as expected on some machines and reports duplicate keys on others
Shaheed Haque writes:
>- The one difference I can think of between deployment pairs which work
>ok, and those which fail is that the logic VM (i.e. where the psql client
>script runs) is the use of a standard AWS ubuntu image for the OK case,
>versus a custom AWS image for the failing case.
Please go to AWS for support for custom AWS stuff.
regards, tom lane
Re: Replication using mTLS issue
Drew Zoellner writes: > So the same user is able to connect using a non replication connection > using the same mtls certificate and pg_ident.conf map. So it seems like the > cert & map are working for this user. Hmph. I tried to reproduce your problem, and it works for me: I can create a replication connection that's authenticated by certificate and relies on a username map to map from the CN in the client certificate to the database username that's mentioned in the "hostssl replication" entry. All I can suggest at this point is to go over your configuration with a fine-tooth comb, looking for probably-silly mistakes such as inconsistent spellings. One thing I can think of to mention in particular is to be sure that the standby's primary_conninfo explicitly includes "user=pgrepmgr_nonprod", as that's likely not the user name it'd default to. Another idea could be to enable log_connections on the primary, and see if the incoming connection request looks different than you were expecting. regards, tom lane
Re: Replication using mTLS issue
Drew Zoellner writes: > Hi Postgres team, I’m receiving an issue matching pg_hba rules that I can’t > seem to sort out. I am trying to use mtls certificate authentication for > physical replication connections but keep receiving the following error… > pg_receivewal: error: FATAL: no pg_hba.conf entry for replication > connection from host "100.84.12.223", user "pgrepmgr_nonprod", SSL on > My pg_hba.conf file contains > hostssl replication pgrepmgr_nonprod 100.0.0.0/8 cert > map=pgrepmgr_nonprod_map Hm, the match failure must be on user name. What certificate are you using on the client side, and what user name does pgrepmgr_nonprod_map map it to? Does it succeed if you weaken the hba entry to hostssl replication all 100.0.0.0/8 cert map=pgrepmgr_nonprod_map > Is cert authentication supported for replication connections? Should be. But you might find it easier to debug the auth failure in a non-replication context, ie add hostssl all pgrepmgr_nonprod 100.0.0.0/8 cert map=pgrepmgr_nonprod_map and then see if you can connect with the same credentials from psql or your favorite other client. BTW, don't forget you have to signal the postmaster to reload configuration after any change in these files. regards, tom lane
Re: RowDescription for a function does not include table OID
"David G. Johnston" writes: > On Fri, Jun 21, 2024 at 8:51 AM Tom Lane wrote: >> The PG wire protocol specification [1] defines these fields thus: >> If the field can be identified as a column of a specific >> table, the object ID of the table; otherwise zero. > s/can be identified as/is/g ? > Experience shows people are inferring a lot from "can be identified" so we > should remove it. "is" maybe over-simplifies a bit but in the correct > direction. I dunno, that seems to me to be just as open to argument if not more so. Perhaps some phrasing like "can be directly identified"? The real point IMV is that it's based purely on parse analysis, without looking into the behavior of views or functions (which could change between parsing and execution, anyway). regards, tom lane
Re: RowDescription for a function does not include table OID
"David G. Johnston" writes: > Based upon that unargued point the only bug here is in the documentation, > leaving the reader to assume that some effort will be made to chain > together a function returns clause to a physical table through that table's > automatically-generated composite type. Hmm, I read the documentation as making minimal promises about how much effort will be expended, not maximal ones. But in any case, I repeat the point that you can't open this can of worms without having a lot of definitional slipperiness wriggle out. Here is an example: regression=# create table foo(a int, b int); CREATE TABLE regression=# create table bar(x int, y int, z int); CREATE TABLE regression=# create function f(int) returns setof foo stable begin atomic select y, z from bar where x = $1; end; CREATE FUNCTION What labeling would you expect for "select * from f(...)", and on what grounds? It is by no stretch of the imagination a select from table foo. Moreover, the system has fully enough information to perceive the query as a select from bar after inlining the function call: regression=# explain verbose select * from f(42); QUERY PLAN Seq Scan on public.bar (cost=0.00..35.50 rows=10 width=8) Output: bar.y, bar.z Filter: (bar.x = 42) (3 rows) In fact, if we implemented this labeling at the tail end of planning rather than early in parsing, it'd be fairly hard to avoid labeling the output columns as bar.* rather than foo.*. But we don't, and I'm not seeing an upside to redefining how that works. I've long forgotten the alleged JDBC connection that David mentions, but it's surely just the tip of the iceberg of client-side code that we could break if we change how this works. regards, tom lane
Re: RowDescription for a function does not include table OID
Maxwell Dreytser writes: > I am working on a meta-programming use-case where I need to scrape some > detailed information about the results of a function that "RETURNS TABLE > (LIKE physical_table)", which ends up with prorettype = > 'physical_table'::regtype. > The problem is that for the query "SELECT * FROM my_function()" the > RowDescription that is sent back shows 0 for Table OID and Column Index. Yes, that's expected. You're selecting from a function, not a table. > I would expect that the Table OID contains the relation OID of this > table, as it would do for a typical statement like "SELECT * FROM > my_table". The PG wire protocol specification [1] defines these fields thus: If the field can be identified as a column of a specific table, the object ID of the table; otherwise zero. If the field can be identified as a column of a specific table, the attribute number of the column; otherwise zero. My reading of that is that we should populate these fields only for the case of direct selection from a table. If you go further than that, then first off you have a ton of definitional issues (should it "look through" views, for example?), and second you probably break applications that are expecting the existing, longstanding definition. regards, tom lane [1] https://www.postgresql.org/docs/current/protocol-message-formats.html
Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
"David G. Johnston" writes:
> On Tuesday, June 18, 2024, Ron Johnson wrote:
>> What's the purpose? Legacy of not having procedures?
> So people can have a style guide that says always specify a returns clause
> on function definitions.
To my mind, the reason we allow RETURNS together with OUT parameter(s)
is so there's a place to write SETOF if you want that.
Yes, the RETURNS TABLE syntax is somewhat redundant with RETURNS
SETOF. Blame the SQL standard for that.
regards, tom lane
Re: DROP COLLATION vs pg_collation question
Laurenz Albe writes:
> On Sun, 2024-06-16 at 11:27 +0200, Karsten Hilbert wrote:
>> Which in turn means I cannot at all truly _remove_ collations
>> from a cluster at the SQL level, only make them invisible
>> (and thereby not-to-be-used) inside a particular database by
>> removing them from pg_collations via DROP COLLATION, right ?
> As far as PostgreSQL is concerned, you can remove them.
It's really kind of moot, since you can't change the encoding
of an existing database. So any pg_collation entries that are
for an incompatible encoding cannot be used for anything in that
database, and they might as well not be there. The reason they
are there is merely an implementation detail: CREATE DATABASE clones
those catalogs from the single copy of pg_collation in template0,
which therefore had better include all collations that might be
needed.
regards, tom lane
Re: Reserving GUC prefixes from a non-preloaded DB extension is not always enforced
Narek Galstyan writes: > I am an extension developer. I use `MarkGUCPrefixReserved` to reserve GUC > prefixes, which my extension uses to help avoid accidentally misspelled > config-file entries. > However, since the reservation happens in `_PG_init()` and `_PG_init()` is > not called until the first use of an API exposed by my extension, > misspelled config-file entries that get executed before the extension is > loaded will not throw an error. No, but a warning will be reported when the extension does get loaded. This seems in line to me with the general behavior of extension-defined GUCs: we cannot know anything about whether a value stored in the config file is sane until we have loaded the extension that defines the GUC's data type, allowed range, etc. > I'd expect GUC variables reserved by an extension to live more permanently > in Postgres catalogs (e.g., in pg_settings). How would they get there? What happens when the extension goes away? How would such an approach emulate C-code-enforced restrictions, that is checks made by a GUC check_hook? What happens if different databases in an installation have inconsistent catalog entries for a GUC? (You could eliminate such inconsistency by storing the data in a shared catalog, perhaps, but that brings some other concerns.) I don't really see the value for work expended here. regards, tom lane
Re: Syntax on BEFORE Trigger - Cascade?
David Barbour writes: > The files are appropriately deleted in Oracle, but Postgres is returning > the following: > *ERROR: Attempt to suppress referential action with before trigger. > CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1 > OPERATOR(pg_catalog.=) "import_job_oid"" * I don't know what you're running there, but there is no such error message in community Postgres. Having said that, maybe what you need is to *not* have any before trigger applied to the import_file table. regards, tom lane
Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program
Chema writes: > been banging my head against this one for a couple days. Googling and > StackExchange were just as useful, so you're my last hope. I've been > unable to get a non-admin user to run Copy From Program even after granting > pg_execute_server_program, and everything else I could think of. It always > fails with ERROR: permission denied to COPY to or from an external program. Works for me: regression=# create user joe; CREATE ROLE regression=# \c - joe You are now connected to database "regression" as user "joe". regression=> create table jt (t1 text); CREATE TABLE regression=> copy jt From Program 'echo "Buffa Testata"' CSV; ERROR: permission denied to COPY to or from an external program DETAIL: Only roles with privileges of the "pg_execute_server_program" role may COPY to or from an external program. HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone. regression=> \c - postgres You are now connected to database "regression" as user "postgres". regression=# GRANT pg_execute_server_program TO joe; GRANT ROLE regression=# \c - joe You are now connected to database "regression" as user "joe". regression=> copy jt From Program 'echo "Buffa Testata"' CSV; COPY 1 What PG version are you working with? regards, tom lane
Re: PG16.1 security breach?
Ron Johnson writes: > On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: >> I think my point is that a paragraph like the following may be a useful >> addition: >> >> If one wishes to remove the default privilege granted to public to execute >> all newly created procedures it is necessary to revoke that privilege for >> every superuser in the system > That seems... excessive. More to the point, it's wrong. Superusers have every privilege there is "ex officio"; we don't even bother to look at the catalog entries when considering a privilege check for a superuser. Revoking their privileges will accomplish nothing, and it does nothing about the actual source of the problem (the default grant to PUBLIC) either. What I'd do if I didn't like this policy is some variant of ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; Repeat for each schema that you think might be publicly readable (which is only public by default). BTW, in PG 15 and up, the public schema is not writable by default, which attacks basically the same problem from a different direction. regards, tom lane
Re: Question about UNIX socket connections and SSL
Casey & Gina writes: > So why can't I use SSL when connecting from a client to a UNIX socket? (1) It'd add overhead without adding any security. Data going through a UNIX socket will only pass through the local kernel, and if that's compromised then it's game over anyway. (2) I'm less sure about this part, but I seem to recall that openssl doesn't actually work if given a UNIX socket. Maybe there are reasons why those arguments are obsolete, but you haven't presented any. regards, tom lane
Re: libpq v17 PQsocketPoll timeout is not granular enough
Thomas Munro writes: > On Tue, Jun 11, 2024 at 2:36 AM Dominique Devienne > wrote: >> PQsocketPoll() being based on time_t, it has only second resolution, AFAIK. >> Despite the [underlying implementation in fe-misc.c][2] supporting at >> least milliseconds. > Yeah, that is not nice and your complaint is very reasonable, and we > should probably do something like what Tom suggested. > Hmm, but if what I speculated above is true, I wonder if the extern > function is even worth its bits... but I don't know how to determine > that completely. I think if we're going to change anything at all here, we should define the external API in microseconds not milliseconds. The lesson we need to be taking from this is that system calls come and go, but libpq API is forever ;-). Somebody could conceivably want sub-millisecond wait resolution within the lifespan of libpq. regards, tom lane
Re: libpq v17 PQsocketPoll timeout is not granular enough
Dominique Devienne writes: > PQsocketPoll() being based on time_t, it has only second resolution, AFAIK. > Despite the [underlying implementation in fe-misc.c][2] supporting at > least milliseconds. > ... > But I think it would a pity if that unreleased API couldn't be made to > accomodate sub-second timeouts and more use-cases, like above. > Especially given that libpq v17 isn't out yet. I may come late to the > game, but hopefully it is not too late. This is an interesting suggestion, but I think yes it's too late. We're already past beta1 and this'd require some fairly fundamental rethinking, since there's no easy substitute for type time_t that has millisecond resolution. (The callers do want to specify an end time not a timeout interval, since some of them loop around PQsocketPoll and don't want the end time to slip.) I guess conceivably we could use gettimeofday() and struct timeval instead of time() and time_t, but it'd touch a lot of places in libpq and it'd make some of the calculations a lot more complex. Maybe a better idea would be to convert to using our src/include/portability/instr_time.h abstraction? But that would be problematic for outside callers. In any case this doesn't seem like a sane thing to be redesigning post-beta. A few months ago maybe we'd have done it, but ... regards, tom lane
