Re: Removing a key from jsonb is sloooow

[Tumasgiu Rossini]

Hi,

Your current query actually process your 10K rows,
it is a bit of an overkill if only a few row contains the key you want to
delete.
Depending on how big your json data is, this could be problematic.

Have you considered adding a where clause to your query ?

Also, maybe you could create an index on your jsonb column
to improve the identification of rows which
contains the key you want to delete ?



Le mer. 17 juil. 2019 à 16:31, Volkan Unsal  a
écrit :

> I'm trying to remove a key from a jsonb column in a table with 10K rows,
> and the performance is abysmal. When the key is missing, it takes 5
> minutes. When the key is present, it takes even longer.
>
> Test with non-existent key:
>
> >> update projects set misc = misc - 'foo';
> Time: 324711.960 ms (05:24.712)
>
> What can I do to improve this?
>

Re: PostgreSQL GSSAPI Windows AD

[Tumasgiu Rossini]

Have you tried your tickets on the client machine ? From my (little)
understanding, the postgresql server complain that the client initiated the
communication with an ticket signed with a different key (kvno 3 vs. 4).
Hope it help.

For information here the differences from your setup with mine (debian 10 /
AD 2012) :
1) postgresql server not joined in domain
2) keytab generated with
  ktpass -out postgres.keytab ^
-princ POSTGRES/debby@dom.local ^
-mapUser DOM\postgres ^
-rndpass ^
-mapOp set ^
-maxpass ^
-crypto AES256-SHA1 ^
-ptype KRB5_NT_PRINCIPAL


Le ven. 26 mai 2023 à 20:35, Jean-Philippe Chenel  a
écrit :

> Dear Tumasgiu Rossini,
>
> When I do the ktpass command on Windows AD, I can see that there is no
> other AD account mapped, otherwise it will raise an exception (Failed to
> set property 'servicePrincipalName').
>
> *Here is the klist command:*
> root@SFADAPGDDF02:/# klist -k /etc/postgresql/postgres.keytab
> KVNO Principal
> 
> --
>4 postgres/ubuntu.ad.corp@ad.corp.com
>
> *Windows AD command:*
> PS C:\Users\Administrateur> get-aduser pgsql_ubuntu -properties
> msDS-KeyVersionNumber
>
> DistinguishedName : CN=pgsql_ubuntu,CN=Managed Service
> Accounts,DC=ad,DC=corp,DC=com
> Enabled   : True
> GivenName : pgsql_ubuntu
> msDS-KeyVersionNumber : 4
> Name  : pgsql_ubuntu
> ObjectClass   : user
> ObjectGUID: dcaadc3c-2faf-44cf-a558-2a441cca690c
> SamAccountName: pgsql_ubuntu
> SID   : S-1-5-21-1388463811-2779960163-2428466526-1204
> Surname   :
> UserPrincipalName : postgres/ubuntu.ad.corp@ad.corp.com
>
> If I look at the postgresql.log, I saw another kvno number. This one is
> matching the user trying to connect.
>
> 2023-05-26 18:30:08.576 UTC [4033] jp.chenel@template1 LOG:  accepting
> GSS security context failed
> 2023-05-26 18:30:08.576 UTC [4033] jp.chenel@template1 DETAIL:
>  Unspecified GSS failure.  Minor code may provide more information: Request
> ticket server postgres/sfadapgddf02.ad.sygifcorp@ad.sygifcorp.com not
> found in keytab (ticket kvno 3)
>
> Like I said, if I make a new keytab, just changing "-pass postgres",
> connections will work again. How to change this password ! For security
> reason, I don't want to let this password.
>
> With best regards,
> --
> *De :* Tumasgiu Rossini 
> *Envoyé :* 26 mai 2023 12:09
> *À :* Jean-Philippe Chenel 
> *Objet :* Re: PostgreSQL GSSAPI Windows AD
>
> Hi,
>
> are you sure that there is no other ad account mapped to the postgres/
> ubuntu.ad.corp@ad.corp.com principal ?
>
> Also you should check that the kvnos of both your keytab and your ad
> account matches, with the following commands :
>
> in linux for the keytab
> klist  /path/to/the/keytab
>
> and in Windows for the account
>  get-aduser  -properties msDS-KeyVersionNumber
>
>
> Le jeu. 25 mai 2023 à 23:51, Jean-Philippe Chenel  a
> écrit :
>
> Hi,
>
> I've recently updated from PostgreSQL 9.6 to 14 and also ubuntu 16.04 to
> 22.04.
> I've made all the installation required for postgresql to connect in
> GSSAPI authentication to a Windows domain.
>
> Something is going wrong and I don't know why.
> When I change the mapped user password from "postgres" to anything else,
> the connection stop to work
>
> Log of postgres:
> Unspecified GSS failure.  Minor code may provide more information: Request
> ticket server postgres/ubuntu.ad.corp@ad.corp.com not found in keytab
> (ticket kvno 3)
>
> Here is the ktpass command (Windows AD):
>
> working:
> ktpass -out postgres.keytab -princ postgres/ubuntu.ad.corp@ad.corp.com
> -mapUser AD\pgsql_ubuntu -pass postgres -mapOp add -crypto AES256-SHA1
> -ptype KRB5_NT_PRINCIPAL
>
> not working:
> ktpass -out postgres.keytab -princ postgres/ubuntu.ad.corp@ad.corp.com
> -mapUser AD\pgsql_ubuntu -pass other_password -mapOp add -crypto
> AES256-SHA1 -ptype KRB5_NT_PRINCIPAL
>
> I put the keytab on the postgres server, the keytab file is referenced in
> the postgresql.conf file.
>
> Here is the full procedure:
>
>1. Create user in AD for postgresql mapping (pgsql_ubuntu), always
>valid, support AES256
>2. Create another user for connection testing
>3. run ktpass command
>4. put the keytab file on the pg server in /etc/postgresql, chown to
>postgres and chmod 600
>5. postgresql.conf krb_server_keyfile =
>'/etc/postgresql/postgres.keytab'
>6. pg_hba is configured to connect over gss
>7. ubuntu server (postgres) is added to domain with this command:
>sudo realm join server.ad.corp.com -U Administrateur
>
>
> I don't know why it works when the password is "postgres" and why I can't
> change it.
>
> With best regards,
>
>