Re: GSSAPI authentication
This sounds like your PG service was unable to authenticate itself to AD. There's probably a trick to that somewhere - AD doesn't really want to be a Kerberos server, it just happens to use it 😉 On Mon, 6 June 2022, 10:05 pm Niels Jespersen, wrote: > Hello all > > > > We are running Postgres 14 on Ubuntu. Our Windows users connect > passwordless using GSSAPI. This works great. > > > > Now we want users on Linux client to also connect passwordless using > GSSAPI. Users on Linux log on using their Active Directory credentials, as > the Linux host (Ubuntu 22.04) is joined to the domain. Logon to Linux works > fine, access to Windows cifs shares works fine authentication with > Kerberos. > > > > But psql won't connect using GSSAPI. It does hit the right pg_hba.conf > line and the username is translated via pg_ident.conf, just fine. But psql > says > > > > psql: error: connection to server at "srvpostgres4.xxx.local" > (172.30.33.30), port 1609 failed: could not initiate GSSAPI security > context: Unspecified GSS failure. Minor code may provide more information: > Server not found in Kerberos database connection to server at > "srvpostgres4.xxx.local" (172.30.33.30), port 1609 failed: GSSAPI > continuation error: Unspecified GSS failure. Minor code may provide more > information: Server not found in Kerberos database > > > > Server log is like this > > > > 2022-06-06 08:14:01.176 > CEST,"yyy","db1",474094,"172.30.32.213:33556",627e83c9.73bee,2,"authentication",2022-06-06 > 08:14:01 CEST,2/14544,0,FATAL,28000,"GSSAPI authentication failed for user > ""yyy""","Connection matched pg_hba.conf line 15: ""hostall > all 172.0.0.0/8 gss map=xxxlocal include_realm=0 > krb_realm=""XXX.LOCAL""","client backend",,-3382135431624836920 > > > > We are a bit lost here. What are we missing? > > > > Regards Niels Jespersen > > > > > > > > > > > > > > > > > > >
Re: GSSAPI authentication
Looking closely at a configuration guide for MSSQL with Kerberos authentication, I see this part: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver16#Manual. It looks like it might be adapted to your question. --Michael On Mon, Jun 6, 2022 at 10:26 PM Michael van der Kolff < mvanderko...@gmail.com> wrote: > This sounds like your PG service was unable to authenticate itself to AD. > > There's probably a trick to that somewhere - AD doesn't really want to be > a Kerberos server, it just happens to use it 😉 > > On Mon, 6 June 2022, 10:05 pm Niels Jespersen, wrote: > >> Hello all >> >> >> >> We are running Postgres 14 on Ubuntu. Our Windows users connect >> passwordless using GSSAPI. This works great. >> >> >> >> Now we want users on Linux client to also connect passwordless using >> GSSAPI. Users on Linux log on using their Active Directory credentials, as >> the Linux host (Ubuntu 22.04) is joined to the domain. Logon to Linux works >> fine, access to Windows cifs shares works fine authentication with >> Kerberos. >> >> >> >> But psql won't connect using GSSAPI. It does hit the right pg_hba.conf >> line and the username is translated via pg_ident.conf, just fine. But psql >> says >> >> >> >> psql: error: connection to server at "srvpostgres4.xxx.local" >> (172.30.33.30), port 1609 failed: could not initiate GSSAPI security >> context: Unspecified GSS failure. Minor code may provide more information: >> Server not found in Kerberos database connection to server at >> "srvpostgres4.xxx.local" (172.30.33.30), port 1609 failed: GSSAPI >> continuation error: Unspecified GSS failure. Minor code may provide more >> information: Server not found in Kerberos database >> >> >> >> Server log is like this >> >> >> >> 2022-06-06 08:14:01.176 >> CEST,"yyy","db1",474094,"172.30.32.213:33556",627e83c9.73bee,2,"authentication",2022-06-06 >> 08:14:01 CEST,2/14544,0,FATAL,28000,"GSSAPI authentication failed for user >> ""yyy""","Connection matched pg_hba.conf line 15: ""hostall >> all 172.0.0.0/8 gss map=xxxlocal include_realm=0 >> krb_realm=""XXX.LOCAL""""""","client backend",,-3382135431624836920 >> >> >> >> We are a bit lost here. What are we missing? >> >> >> >> Regards Niels Jespersen >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >
Re: GSSAPI authentication
The part that you're missing, I think, is that Kerberized services require
a service account.
The SPN (service principal name) is the name that is used in Kerberos
contexts for that service account. PostgreSQL uses
postgres/${hostname}@${realm}
by default - see https://www.postgresql.org/docs/14/gssapi-auth.html.
The important part to note here is that $hostname must match what is
registered in the SPN for the user that you're using as the service account
in AD. It might (I don't know) have to match what AD believes about the
host from its PTR records for that domain as well.
--Michael
On Mon, Jun 6, 2022 at 11:33 PM Niels Jespersen wrote:
> *Fra:* Michael van der Kolff
> *Sendt:* 6. juni 2022 14:26
> *Til:* Niels Jespersen
> *Cc:* pgsql-general list
> *Emne:* Re: GSSAPI authentication
>
>
>
> >This sounds like your PG service was unable to authenticate itself to AD.
>
> >
>
> >There's probably a trick to that somewhere - AD doesn't really want to be
> a Kerberos server, it just happens to use it 😉
>
>
>
> But it works fine when the same AD-user connects from Windows to the same
> postgres (Linux) server. Auth fails when the user initiates login from a
> Linux box (that otherwise uses Kerberized ressources just fine).
>
>
>
> Niels
>
Re: GSSAPI authentication
Oh wait, I see.
On Mon, Jun 6, 2022 at 11:41 PM Michael van der Kolff <
mvanderko...@gmail.com> wrote:
> The part that you're missing, I think, is that Kerberized services require
> a service account.
>
> The SPN (service principal name) is the name that is used in Kerberos
> contexts for that service account. PostgreSQL uses
> postgres/${hostname}@${realm}
> by default - see https://www.postgresql.org/docs/14/gssapi-auth.html.
>
> The important part to note here is that $hostname must match what is
> registered in the SPN for the user that you're using as the service account
> in AD. It might (I don't know) have to match what AD believes about the
> host from its PTR records for that domain as well.
>
> --Michael
>
> On Mon, Jun 6, 2022 at 11:33 PM Niels Jespersen wrote:
>
>> *Fra:* Michael van der Kolff
>> *Sendt:* 6. juni 2022 14:26
>> *Til:* Niels Jespersen
>> *Cc:* pgsql-general list
>> *Emne:* Re: GSSAPI authentication
>>
>>
>>
>> >This sounds like your PG service was unable to authenticate itself to AD.
>>
>> >
>>
>> >There's probably a trick to that somewhere - AD doesn't really want to
>> be a Kerberos server, it just happens to use it 😉
>>
>>
>>
>> But it works fine when the same AD-user connects from Windows to the same
>> postgres (Linux) server. Auth fails when the user initiates login from a
>> Linux box (that otherwise uses Kerberized ressources just fine).
>>
>>
>>
>> Niels
>>
>
Re: GSSAPI authentication
>From the tiny bit I know about this, and a bit of googling, I arrived at
https://stackoverflow.com/questions/13850252/cannot-get-kerberos-service-ticket-krbexception-server-not-found-in-kerberos-d
.
It seems to suggest that either the KDC or your service account might have
bad PTR records, and you might want to capture DNS traffic on the two
hosts. Of course, I have no idea whether that is actually the issue.
I remember reading these docs ages ago - best of luck!
--Michael
On Mon, Jun 6, 2022 at 11:42 PM Michael van der Kolff <
mvanderko...@gmail.com> wrote:
> Oh wait, I see.
>
> On Mon, Jun 6, 2022 at 11:41 PM Michael van der Kolff <
> mvanderko...@gmail.com> wrote:
>
>> The part that you're missing, I think, is that Kerberized services
>> require a service account.
>>
>> The SPN (service principal name) is the name that is used in Kerberos
>> contexts for that service account. PostgreSQL uses
>> postgres/${hostname}@${realm}
>> by default - see https://www.postgresql.org/docs/14/gssapi-auth.html.
>>
>> The important part to note here is that $hostname must match what is
>> registered in the SPN for the user that you're using as the service account
>> in AD. It might (I don't know) have to match what AD believes about the
>> host from its PTR records for that domain as well.
>>
>> --Michael
>>
>> On Mon, Jun 6, 2022 at 11:33 PM Niels Jespersen wrote:
>>
>>> *Fra:* Michael van der Kolff
>>> *Sendt:* 6. juni 2022 14:26
>>> *Til:* Niels Jespersen
>>> *Cc:* pgsql-general list
>>> *Emne:* Re: GSSAPI authentication
>>>
>>>
>>>
>>> >This sounds like your PG service was unable to authenticate itself to
>>> AD.
>>>
>>> >
>>>
>>> >There's probably a trick to that somewhere - AD doesn't really want to
>>> be a Kerberos server, it just happens to use it 😉
>>>
>>>
>>>
>>> But it works fine when the same AD-user connects from Windows to the
>>> same postgres (Linux) server. Auth fails when the user initiates login from
>>> a Linux box (that otherwise uses Kerberized ressources just fine).
>>>
>>>
>>>
>>> Niels
>>>
>>
Re: Tuning a query with ORDER BY and LIMIT
What do you see when you remove the LIMIT clause? It may be possible to
rewrite this using ROW_NUMBER.
--Michael
On Thu, Jun 23, 2022 at 5:39 AM Dirschel, Steve <
steve.dirsc...@thomsonreuters.com> wrote:
> I am fairly new to tuning Postgres queries. I have a long background
> tuning Oracle queries.
>
>
>
> Posrgres version 10.11
>
>
>
> Here is the DDL for the index the query is using:
>
>
>
> create index workflow_execution_initial_ui_tabs
>
> on workflow_execution (workflow_id asc, status asc, result asc,
> completed_datetime desc);
>
>
>
>
>
> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
>
> select * from workflow_execution
>
> where workflow_id = 14560 and
>
> status = 'COMPLETED' and
>
> result in
> ('SUCCEEDED','REEXECUTED','ABORTED','DISCONTINUED','FAILED','PARTIAL_SUCCESS')
>
> order by completed_datetime desc limit 50;
>
>
>
> --
>
> Limit (cost=56394.91..56395.04 rows=50 width=1676) (actual
> time=3400.608..3400.622 rows=50 loops=1)
>
> " Output: execution_id, state_machine_id, workflow_id, started_datetime,
> completed_datetime, status, execution_context_s3_arn, ol_version,
> created_datetime, updated_datetime, deleted_millis, acquisition_channel_id,
> correlation_id, result, state_machine_execution_arn, created_by_id,
> updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
> Buffers: shared hit=142368
>
> -> Sort (cost=56394.91..56432.71 rows=15118 width=1676) (actual
> time=3400.607..3400.615 rows=50 loops=1)
>
> "Output: execution_id, state_machine_id, workflow_id,
> started_datetime, completed_datetime, status, execution_context_s3_arn,
> ol_version, created_datetime, updated_datetime, deleted_millis,
> acquisition_channel_id, correlation_id, result,
> state_machine_execution_arn, created_by_id, updated_by_id,
> acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
> Sort Key: workflow_execution.completed_datetime DESC
>
> Sort Method: top-N heapsort Memory: 125kB
>
> Buffers: shared hit=142368
>
> -> Index Scan using workflow_execution_initial_ui_tabs on
> workflow.workflow_execution (cost=0.69..55892.70 rows=15118 width=1676)
> (actual time=0.038..2258.579 rows=2634718 loops=1)
>
> " Output: execution_id, state_machine_id, workflow_id,
> started_datetime, completed_datetime, status, execution_context_s3_arn,
> ol_version, created_datetime, updated_datetime, deleted_millis,
> acquisition_channel_id, correlation_id, result,
> state_machine_execution_arn, created_by_id, updated_by_id,
> acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
> " Index Cond: ((workflow_execution.workflow_id = 14560) AND
> ((workflow_execution.status)::text = 'COMPLETED'::text) AND
> ((workflow_execution.result)::text = ANY
> ('{SUCCEEDED,REEXECUTED,ABORTED,DISCONTINUED,FAILED,PARTIAL_SUCCESS}'::text[])))"
>
> Buffers: shared hit=142368
>
> Planning time: 0.217 ms
>
> Execution time: 3400.656 ms
>
>
>
> With Oracle for a query like this since the index is on the 3 columns
> matching the WHERE clause and the ORDER BY clause is in the 4th position
> Oracle would be able to scan that index and as soon as it finds the first
> matching 50 rows. But as you can see above Postgres is finding 2,634,718
> matching rows for the WHERE clause , sorts them, and then returns the first
> 50 rows.
>
>
>
> I was questioning if the result IN clause was causing the issue so I ran
> the query with result = and see the same results:
>
>
>
> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
>
> select * from workflow_execution
>
> where workflow_id = 14560 and
>
> status = 'COMPLETED' and
>
> result = 'SUCCEEDED'
>
> order by completed_datetime desc limit 50;
>
>
>
> Limit (cost=54268.09..54268.22 rows=50 width=1676) (actual
> time=3372.453..3372.467 rows=50 loops=1)
>
> " Output: execution_id, state_machine_id, workflow_id, started_datetime,
> completed_datetime, status, execution_context_s3_arn, ol_version,
> created_datetime, updated_datetime, deleted_millis, acquisition_channel_id,
> correlation_id, result, state_machine_execution_arn, created_by_id,
> updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
> Buffers: shared hit=140313
>
> -> Sort (cost=54268.09..54304.46 rows=14547 width=1676) (actual
> time=3372.452..3372.460 rows=50 loops
Re: Modelling versioning in Postgres
One thing you could consider is a range type for your "versionTS" field instead of a single point in time. So that would be: CREATE TABLE objects ( objectID uuid, versionID uuid, validRange tsrange, objectData text, ); See https://www.postgresql.org/docs/12.5/rangetypes.html for more information. In particular, you can enforce the obvious business rule, that there is no objectID with overlapping validRanges (as long as you have the btree_gist extension): CREATE EXTENSION btree_gist; CREATE TABLE objects ( objectID uuid, versionID uuid, validRange tsrange, objectData text, EXCLUDE USING GIST(objectID WITH =, validRange WITH &&) ); On Fri, May 28, 2021 at 8:20 PM Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi > > I was wondering what the current thinking is on ways to model versioning > in Postgres. > > The overall premise is that the latest version is the current version > unless a rollback has occurred, in which case versions get tracked from the > rollback point (forking ?). > > My initial naïve starting point is something along the lines of : > > create table objects ( > objectID uuid, > versionID uuid, > versionTS timestamp > objectData text > ); > > This obviously creates a fool-proof answer to "latest version is the > current version" because its a simple case of an "where objectID=x order by > versionTS desc limit 1" query. However it clearly doesn't cover the > rollback to prior scenarios. > > I then though about adding a simple "versionActive boolean". > > But the problem with that is it needs hand-holding somewhere because there > can only be one active version and so it would introduce the need for a > "active switch" script somewhere that activated the desired version and > deactivated the others. It also perhaps is not the right way to deal with > tracking of changes post-rollback. > > How have others approached the problem ? > > N.B. If it makes any difference, I'm dealing with a 12.5 install here, but > this could easily be pushed up to 13 if there are benefits. > > Thanks for your time. > > Laura > > >
Re: Overriding natural order of query results for a subset
Have you considered use of the "nulls last" option in order by ( https://www.postgresql.org/docs/13/queries-order.html)? Alternatively, you could write your own type, with its own ordering primitive 😉 On Sun, 30 May 2021, 12:15 am Laura Smith, < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi > > I've got a bit of a puzzle that I'm not quite sure how to approach. > > Let's say I've got a table of bios, so : > > create table bios ( > first_name text not null, > last_name text not null, > person_title text, > person_short_bio text > ); > > Now, the "natural order" would be a standard "select * from bios order by > last_name". Basic stuff, no problem. > > The problem is that my use-case calls for a scenario where due to protocol > certain people may be designated as "VIP" and therefore need to appear at > the top. In addition, protocol may dictate that those "VIP" people > themselves may (sometimes but not always) need to be ordered in a specific > manner. > > Bear in mind that there may be a large enough number of people in this > table that the naïve approach of manually assigning everyone an order is > neither practical or desirable. Hence the need for an "override" which > would mean only a subset of people would need specific parameters. > > Any ideas ? > > Thanks ! > > Laura > > > > >
