can't get psql authentication against Active Directory working
Hello all, so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via Active Directory. psql (Linux) -> postgres (Linux) with authentication against Active Directory does work. However the same with psql.exe on Windows does not. I get: D:\>C:\OSGeo4W\bin\psql.exe service=the_db psql: error: connection to server at "dbserver.example.lan (192.168.4.104), port 5432 failed: could not initiate GSSAPI security context: No credentials were supplied, or the credentials were unavailable or inaccessible: Internal credentials cache error psql.exe from the OSGeo4W QGIS Installer *does* include GSS support. (I have tried with a different psql.exe without GSS support and it would tell me that it does not support GSS). The .pg_service.conf file in the users $HOME directory looks like this: [the_db] host=dbserver.example.lan port=5432 user=u...@example.lan gssencmode=require This same pg_service.conf does work for psql (Linux). Also getting a Kerberos ticket for the service on Windows does work: D:\> klist get postgres/dbserver.example@example.lan [...] This will list the ticket But when using psql.exe it will not get a ticket for the service nor will it apparently use the existing service ticket. I have tried to trace psql.exe with Window's Process Monitor and I can't see it accessing no keytab file (I'm not sure whether a keytab file exists at all under Windows or if psql.exe doesn't instead need to access some Windows service). I see that psql.exe will open and close a TCP connection to dbserver.example.lan, however as far as I can see that connection is completely irrelevant for the aquisition of a Kerberos ticket for the service since that is a business purely between psql.exe and Active Directory or respectively between psql.exe and the credentials cache. And there is no other TCP connection being opened to anywhere from psql.exe. What I find suspicious about the error above is "... the credentials were unavailable or inaccessible: Internal credentials cache error", since that looks like either psql.exe can't access the (inexisting) keytab file, or it can't access Window's Kerberos service. Also, I see that psql.exe is trying to access a ccapiserver.exe which does not exist. Should psql.exe be able to access that ccapiserver.exe file? That means is the OSGeo4W QGIS installer, that also installs all things necessary for psql missing that ccapiserver.exe executable? * has anybody ever succeeded in authenticating with psql.exe against Active Directory? * can you maybe tell me what's wrong from the error message above? * how can I proceed from here? Thanks a lot for any pointers and/or help!!! *t PS: Any way to make GSS more talkative? At this moment all that I can get as logs is the above "computer says no".
Re: Does Postgres 14 have a query cache?
On 2023-02-18 06:46:59 -0800, Siddharth Jain wrote: > I think the answer is no but wanted to confirm here. this is what my best > friend told me. > > image.png ChatGPT is your best friend? It is correct. PostgreSQL doesn't have a query cache. I think the reason is that the use case (the exact same query is submitted repeatedly) is sufficiently rare that it isn't all that effective in practice. (The other techniques mentioned are of course also used by other databases.) hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Does Postgres 14 have a query cache?
> On Feb 18, 2023, at 06:59, Peter J. Holzer wrote: > I think the reason > is that the use case (the exact same query is submitted repeatedly) is > sufficiently rare that it isn't all that effective in practice. And, in this use case, a prepared statement is in effect a cache of the parsing and planning of the query. Detecting identical queries and caching their results while maintaining good invalidation is a much harder problem.
Re: can't get psql authentication against Active Directory working
> On 18/02/2023 15:02 CET Tomas Pospisek wrote: > > so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) > via Active Directory. > > psql (Linux) -> postgres (Linux) with authentication against Active > Directory does work. > > However the same with psql.exe on Windows does not. I get: > > D:\>C:\OSGeo4W\bin\psql.exe service=the_db > psql: error: connection to server at "dbserver.example.lan > (192.168.4.104), port 5432 failed: could not initiate GSSAPI > security context: No credentials were supplied, or the credentials > were unavailable or inaccessible: Internal credentials cache error > > psql.exe from the OSGeo4W QGIS Installer *does* include GSS support. (I > have tried with a different psql.exe without GSS support and it would > tell me that it does not support GSS). > > The .pg_service.conf file in the users $HOME directory looks like this: > > [the_db] > host=dbserver.example.lan > port=5432 > user=u...@example.lan > gssencmode=require > > This same pg_service.conf does work for psql (Linux). On Windows the service file is not read from $home/.pg_service.conf but $env:appdata/postgresql/.pg_service.conf (or %appdata%/postgresql/.pg_service.conf when using cmd.exe.) -- Erik
Re: can't get psql authentication against Active Directory working
On 18.02.23 17:16, Erik Wienhold wrote: On 18/02/2023 15:02 CET Tomas Pospisek wrote: so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via Active Directory. psql (Linux) -> postgres (Linux) with authentication against Active Directory does work. However the same with psql.exe on Windows does not. I get: D:\>C:\OSGeo4W\bin\psql.exe service=the_db psql: error: connection to server at "dbserver.example.lan (192.168.4.104), port 5432 failed: could not initiate GSSAPI security context: No credentials were supplied, or the credentials were unavailable or inaccessible: Internal credentials cache error psql.exe from the OSGeo4W QGIS Installer *does* include GSS support. (I have tried with a different psql.exe without GSS support and it would tell me that it does not support GSS). The .pg_service.conf file in the users $HOME directory looks like this: [the_db] host=dbserver.example.lan port=5432 user=u...@example.lan gssencmode=require This same pg_service.conf does work for psql (Linux). On Windows the service file is not read from $home/.pg_service.conf but $env:appdata/postgresql/.pg_service.conf (or %appdata%/postgresql/.pg_service.conf when using cmd.exe.) Thank you Erik, the location of the .pg_service.conf file is not the problem, as I am seeing that psql.exe is reacting to its contents, that is authenticating differently depending on whether gssencmode is set or not (maybe I noted its location wrongly, since i've written the post from my notes, as I do not have access to the machines in question all the time). Do you have authentication from psql.exe against Active Directory working? *t
Re: Does Postgres 14 have a query cache?
On Sat, 18 Feb 2023 12:43:42 -0600 Ron wrote: > > I think the answer is no but wanted to confirm here. this is what > > my best friend told me. There are caches for prepared statements, table rows, indexes. What about the caches are you interested in? -- Steven Lembark Workhorse Computing lemb...@wrkhors.com +1 888 359 3508
Re: Does Postgres 14 have a query cache?
Thanks all for the replies. Just wanted to confirm. On Sat, Feb 18, 2023 at 10:45 AM Steven Lembark wrote: > On Sat, 18 Feb 2023 12:43:42 -0600 > Ron wrote: > > > > I think the answer is no but wanted to confirm here. this is what > > > my best friend told me. > > There are caches for prepared statements, table rows, indexes. > > What about the caches are you interested in? > > > -- > Steven Lembark > Workhorse Computing > lemb...@wrkhors.com > +1 888 359 3508 > > >
Re: Automatic aggressive vacuum on almost frozen table takes too long
Adrian, thanks for pointing out the fix. We are just about to update to 11.18 next month. Mikhael On Thu, 16 Feb 2023 at 23:44, Adrian Klaver wrote: > On 2/15/23 22:57, Mikhail Balayan wrote: > > Hello, > > > > I have a big table in the actively working system, in which nothing is > > written for a long time, and nothing is read from it. Table size is 15GB > > (data only), indexes 150GB. > > Since the table does not change, after a while it crosses the > > autovacuum_freeze_max_age and an aggressive vacuum is triggered. And it > > would be OK, but vacuuming of the table takes a long time, despite the > > fact that exactly the same scan was made a few days before and almost > > all pages are marked as frozen, which is confirmed by data from the log: > > automatic aggressive vacuum of table > > "appdbname.appschemaname.applications": index scans: 1 > > pages: 0 removed, 2013128 remain, 0 skipped due to pins, > > 2008230 skipped frozen > > tuples: 2120 removed, 32616340 remain, 0 are dead but not > > yet removable, oldest xmin: 4111875427 > > buffer usage: 2005318781 hits, 19536511 misses, 23903 > dirtied > > avg read rate: 4.621 MB/s, avg write rate: 0.006 MB/s > > system usage: CPU: user: 26398.27 s, system: 335.27 s, > > elapsed: 33029.00 s > > > > That is, if I understand it correctly, it says that there were (and > > actually are) 2013128 pages of which 2008230 were skipped, which leaves > > 4898 blocks to be scanned. I.e. it seems that the allocated 1GB > > (autovacuum_work_mem) should be enough to handle that amount of blocks > > and to avoid multiple scans of the indexes. > > But, based on buffer usage, one can see that a huge amount of data is > > read, greatly exceeding not only the number of remaining unfrozen > > blocks, but also the size of the table and indexes taken together: 2 > > billion blocks, more than 15TB. > > > > Is this a bug in Postgresql or am I interpreting the log data wrong? > > Not sure if this applies but from: > > https://www.postgresql.org/docs/11/release-11-18.html > > Release 11.18 > > Avoid long-term memory leakage in the autovacuum launcher process (Reid > Thompson) > > The lack of field reports suggests that this problem is only latent in > pre-v15 branches; but it's not very clear why, so back-patch the fix > anyway. > > > > > > Just in case, I'm using Postgresql version: 11.11. > > Besides the above you are missing 8 releases of other fixes. > > > autovacuum_vacuum_cost_delay: 2ms > > autovacuum_vacuum_cost_limit: 8000 > > > > Thank you. > > > > BR, > > Mikhael > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Automatic aggressive vacuum on almost frozen table takes too long
> > >> Even still, > >> the information about buffers that you've shown does indeed appear to > >> be total nonsense (while everything else we can see looks plausible). Actually not only buffers. The elapsed time also looks strange. And this is the first reason why I paid attention to this situation. 33029 seconds, which is 9+ hours to scan 4898 blocks and related indexes. And by this metric, it seems that the amount of buffers processed is reliable (which does not negate the presence of the bug). Otherwise I can not explain such a long vacuuming of the table. >> Do you have any non-btree indexes on the table? Can you show us the > details of the > >> table, including all of its indexes? In other words, can you show "\d > applications" output from psql? Only btree indexes. Please find the full table schema below: appdbname=> \d applications Table "appschemaname.applications" Column | Type | Collation | Nullable | Default ++---+--+-- id | character varying(36) | | not null | created_at | bigint | | not null | updated_at | bigint | | not null | deleted_at | bigint | | | tenant_id | character varying(36) | | | context_tenant_id | character varying(36) | | | policy_id | character varying(36) | | | policy_type| character varying(128) | | | policy_name| text | | | root_policy_id | character varying(36) | | | context_id | character varying(36) | | | context_type | character varying(128) | | | agent_id | character varying(36) | | | multiple_applications_conflict | text | | | validation_issues | text | | | deployment_state | character varying(128) | | | deployment_errors | text | | | enabled| boolean| | | has_conflict_alert | boolean| | | running_state | character varying(128) | | | running_activities | text | | | running_progress | character varying(128) | | | running_blockers | text | | | last_success_time | bigint | | | last_run_time | bigint | | | last_activity | character varying(128) | | | next_run_time | bigint | | | next_activity | character varying(128) | | | most_severe_status_event | text | | | status | character varying(128) | | | origin_context | text | | not null | ''::text euc| character varying(36) | | | event_flag | integer| | | 1 Indexes: "applications2_pkey" PRIMARY KEY, btree (id) "chk_invariant_1_apps2" UNIQUE, btree (policy_type, context_id) WHERE multiple_applications_conflict <> ''::text AND enabled = true AND root_policy_id::text <> ''::text AND deleted_at IS NULL "uq_policy_id_context2" UNIQUE, btree (policy_id, context_id) WHERE deleted_at IS NULL "idx_applications2_agent_id" btree (agent_id) "idx_applications2_context_id" btree (context_id) "idx_applications2_context_tenant_id" btree (context_tenant_id) "idx_applications2_context_type" btree (context_type) "idx_applications2_deleted_at" btree (deleted_at) "idx_applications2_enabled" btree (enabled) "idx_applications2_euc_index" btree (euc) "idx_applications2_policy_id" btree (policy_id) "idx_applications2_policy_type" btree (policy_type) "idx_applications2_root_policy_id" btree (root_policy_id) WHERE deleted_at IS NULL "idx_applications2_status" btree (status) "idx_applications2_status_by_policy_fields_context_tenant_i" btree (context_tenant_id, root_policy_id, policy_type, context_type, deleted_at) WHERE deleted_at IS NULL AND root_policy_id::text = ''::text "idx_applications2_status_by_policy_fields_tenant_id" btree (tenant_id, root_policy_id, policy_type, context_type, deleted_
Re: Does Postgres 14 have a query cache?
> On Sat, Feb 18, 2023 at 7:47 AM Siddharth Jain wrote: > >> I think the answer is no but wanted to confirm here. this is what my best >> friend told me. >> >> [image: image.png] >> > > I find the last paragraph suspect. The rest is basically correct. Yeah. Pgpool-II has query cache but PgBouncer does not. Best reagards, -- Tatsuo Ishii SRA OSS LLC English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp
Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
I’ve searched in vain for an account of how "autocommit" mode actually works. (I tried the built-in search feature within the PG docs. And I tried Google.) It seems clear enough that turning "autocommit" mode "on" or "off" is done by using a client-env-specific command like "\set" is psql, or "SET" in ECPG (Embedded SQL in C) or "self.session.set_session(autocommit=...)" in Python. And that the mode is a property of the current session. But it's not clear who actually implements the opening "start transaction" and the closing "commit" around every submitted SQL statement when autocommit is "on". Is this done in client-side code (maybe implying three round trips per intended SQL statement)? Or is it done server-side?
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
Hi, On Sat, Feb 18, 2023 at 03:49:26PM -0800, Bryn Llewellyn wrote: > > But it's not clear who actually implements the opening "start transaction" > and the closing "commit" around every submitted SQL statement when autocommit > is "on". > > Is this done in client-side code (maybe implying three round trips per > intended SQL statement)? Or is it done server-side? It's always done on the client side, postgres itself doesn't know about this feature.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> On Feb 18, 2023, at 15:49, Bryn Llewellyn wrote: > > Or is it done server-side? It's done server-side. Note that what really happens is that, when a statement begins execution and there is no open transaction, a snapshot is taken and then released when the statement finishes (just as happens in READ COMMITTED mode). No piece of code literally injects a BEGIN and a COMMIT statement to make it happen.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> On Feb 18, 2023, at 15:49, Bryn Llewellyn wrote: > > I’ve searched in vain for an account of how "autocommit" mode actually works. I realize now I may have misinterpreted your question... apologies if so! If you mean the BEGIN and COMMIT statement that some client libraries insert into the stream when autocommit is off, that's the client, not PostgreSQL. PostgreSQL has no idea that mode even exists: it either sees statements without transactions, which run in their own transaction, or BEGIN / COMMIT statements. Because client stacks have traditionally loved to provide their own transaction semantics, they might inject BEGIN and COMMIT statements, but that's not something PostgreSQL sees. (And I have never liked the term "autocommit mode"; it really doesn't reveal much about what is going on.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
On Sat, Feb 18, 2023 at 4:49 PM Bryn Llewellyn wrote: > > And that the mode is a property of the current session. > To rephrase the other responses, the client-defined setting has no inherent relationship to the concept of a PostgreSQL session. How the client uses that setting is internal to the client and whatever abstraction(s) it provides the programmer. So far as the server is concerned it is always auto (implicit) begin, and also auto (implicit) commit - absent receiving a BEGIN SQL Command in which case it disables implicit commit and (more or less) waits for a COMMIT or ROLLBACK before ending the transaction that it implicitly started. David J.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
2023年2月19日(日) 9:51 Christophe Pettus : > > > > > On Feb 18, 2023, at 15:49, Bryn Llewellyn wrote: > > > > I’ve searched in vain for an account of how "autocommit" mode actually > > works. > > I realize now I may have misinterpreted your question... apologies if so! If > you mean the BEGIN and COMMIT statement that some client libraries insert > into the stream when autocommit is off, that's the client, not PostgreSQL. > > PostgreSQL has no idea that mode even exists: it either sees statements > without transactions, which run in their own transaction, or BEGIN / COMMIT > statements. Because client stacks have traditionally loved to provide their > own transaction semantics, they might inject BEGIN and COMMIT statements, but > that's not something PostgreSQL sees. Historical trivia: PostgreSQL had a (backend) "autocommit" GUC in 7.3 only, which remained as a dummy GUC until 9.5 (see: https://pgpedia.info/a/autocommit.html ). Regards Ian Barwick
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> On Feb 18, 2023, at 18:52, Ian Lawrence Barwick wrote: > > Historical trivia: PostgreSQL had a (backend) "autocommit" GUC in 7.3 > only, which remained as > a dummy GUC until 9.5 (see: https://pgpedia.info/a/autocommit.html ). Well, that was a pretty whacky idea. :-)
