Re: Having issue with SSL.

[Peter J. Holzer]

On 2023-05-24 21:02:04 +, Randy Needham wrote:
> The problem I am running into is my remote client can't connect via SSL to
> postgrsql.  I am able to from the server itself.  This is using pgAdmin 4 and
> making ssl mode as required.  Also ran psql.exe on the server to show that SSL
> was in fact working on the server. "SSL connection (protocol: TLSv1.3, cipher:
> TLS_AES_256_GCM_SHA384, bits: 256, compression: off)".  In the logs it is
> showing this when I try to connect via my remote client.
> 
> [2672] LOG:  could not accept SSL connection: An existing connection was
> forcibly closed by the remote host.
> 
> The error from pgAdmin 4 on the remote client is this.
> 
> connection failed: server closed the connection unexpectedly This probably
> means the server terminated abnormally before or while processing the 
> request. 
> SSL SYSCALL error: Connection reset by peer (0x2746/100054)

So both the server and the client claim that the connection was
terminated by the other side?

I suspect that the server and client cannot agree on a common cypher.
But if both are reasonably up to date that shouldn't happen (it can
happen if the SSL library on your server is much older than that on your
client or vice versa).

Can you use wireshark (or something similar) to record the session and
see where in the protocol they give up?

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: DB migration : Sybase to Postgres

[Marcos Pegoraro]

Em qui., 25 de mai. de 2023 às 08:30, Sengottaiyan T 
escreveu:

> Is there an option to set novalidate constraints in postgres? In my source
> Sybase DB, table structures are not defined properly (only primary keys
> exist and no foreign key) - I'm making necessary changes on target Postgres
> DB (created tables, identifying relationship between table columns with
> respective team). After creating proper structure on target, the next step
> is to load data - I'm sure there will be a lot of errors during initial
> data load (no parent record found). How to handle it?
>
>>
>> Other options:

create all foreign keys before importing your data and do ...

This way table triggers are disabled for all users;
ALTER TABLE T1 DISABLE TRIGGER ALL; ALTER TABLE T2 DISABLE TRIGGER ALL; ALTER
TABLE T3 DISABLE TRIGGER ALL;
--Import all your data
ALTER TABLE T1 ENABLE TRIGGER ALL; ALTER TABLE T2 ENABLE TRIGGER ALL; ALTER
TABLE T3 ENABLE TRIGGER ALL;

or

This way table triggers are disabled for this session only;
SET SESSION_REPLICATION_ROLE = REPLICA;
--Import all your data
SET SESSION_REPLICATION_ROLE = ORIGIN;

Obviously if your data doesn't have correct foreign keys matching to their
parent you'll never be able to do a dump/restore properly.

Marcos

Re: DB migration : Sybase to Postgres

[Peter J. Holzer]

On 2023-05-25 08:10:42 -0500, Ron wrote:
> (You can create the FKs ahead of time, but use the NOT VALID clause; then,
> after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.)

I don't think this will work:

| Normally, [ADD CONSTRAINT] will cause a scan of the table to verify
| that all existing rows in the table satisfy the new constraint. But if
| the NOT VALID option is used, this potentially-lengthy scan is
| skipped. The constraint will still be enforced against subsequent
| inserts or updates
(https://www.postgresql.org/docs/current/sql-altertable.html)

However, you can define a constraint as DEFERRABLE, and then defer
checking until commit:

hjp=> create table master(id int primary key, t text);
CREATE TABLE
hjp=> create table detail(id int primary key, master int references master 
DEFERRABLE INITIALLY IMMEDIATE, t text);
CREATE TABLE

hjp=> begin;
BEGIN
hjp=*> set constraints detail_master_fkey deferred;
SET CONSTRAINTS
hjp=*> insert into detail values(1, 1, '1/1');
INSERT 0 1
hjp=*> insert into detail values(2, 1, '1/2');
INSERT 0 1
hjp=*> insert into detail values(3, 2, '2/1');
INSERT 0 1
hjp=*> insert into detail values(4, 3, '3/1');
INSERT 0 1
hjp=*> insert into master values(1, '1');
INSERT 0 1
hjp=*> insert into master values(2, '2');
INSERT 0 1

-- We haven't inserted a master record with id 3 yet, so the commit will
-- fail:

hjp=*> commit;
ERROR:  insert or update on table "detail" violates foreign key constraint 
"detail_master_fkey"
DETAIL:  Key (master)=(3) is not present in table "master".

(You can also reenable the constraint explicitely before the end of a
transaction with SET CONSTRAINTS ... IMMEDIATE)

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: DB migration : Sybase to Postgres

[Ron]

On 5/26/23 08:38, Peter J. Holzer wrote:

On 2023-05-25 08:10:42 -0500, Ron wrote:

(You can create the FKs ahead of time, but use the NOT VALID clause; then,
after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.)

I don't think this will work:

| Normally, [ADD CONSTRAINT] will cause a scan of the table to verify
| that all existing rows in the table satisfy the new constraint. But if
| the NOT VALID option is used, this potentially-lengthy scan is
| skipped. The constraint will still be enforced against subsequent
| inserts or updates
(https://www.postgresql.org/docs/current/sql-altertable.html)


I was probably thinking of tables with existing data.

--
Born in Arizona, moved to Babylonia.

Re: PostgreSQL GSSAPI Windows AD

[Jean-Philippe Chenel]

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 
mailto:jp.che...@live.ca>> 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,

Suggestions on pg_statviz Extension

[Rajiv Harlalka]

Hi Everyone,
 I am working on a PostgreSQL extension pg_statviz
 [1] as a part of GSoC'23 with the
motivation of developing a tool that helps dba's monitor their running
database effortlessly. The extension would accumulate statistics about the
database over a regular period of time and after a long interval time
series analysis could be performed on the collected statistics.
The tool being minimal, would store just the right information that could
help in analyzing any performance bottlenecks. The project is currently at
alpha level and has a number of additional modules proposed by me such as:
- stats of growing database such as size, buffers hit/cache from
pg_stat_user_* views.
- stats on the locks imposed on tables from pg_locks view.
- Replication statistics wrt to current_lag and bytes transferred from
pg_stat_replication view.
- Functions stats such as running time from pg_stat_user_functions view.

Suggestions from the community regarding any module that they would like to
have in such a tool.
I Would also be happy to answer any questions regarding the project overall.

[1]: https://github.com/vyruss/pg_statviz

Thanking you,
Rajiv Harlalka,
rajivharlalka.tech
Indian Institute of Technology, Kharagpur

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

Query to find RDS endpoint

[Atul Kumar]

Hi,

Could someone help me in sharing a postgresql query to fetch the RDS
endpoint ?

Postgres version 14.6.

I tried to search but couldn't find it.



Regards.

explicit-locking.html "key values" reference

[jian he]

hi.
https://www.postgresql.org/docs/current/explicit-locking.html

FOR KEY SHARE
> Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT
> FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared
> lock blocks other transactions from performing DELETE or any UPDATE that
> changes the key values, but not other UPDATE, and neither does it prevent 
> SELECT
> FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.


A key-shared lock blocks other transactions from performing DELETE or any
> UPDATE that changes the key values


querya: select col1,col2,col3 from a for key share of a
Does "the key values" refer to these rows returned by the querya?
I still feel confused about "the key values".

Re: explicit-locking.html "key values" reference

[David G. Johnston]

On Fri, May 26, 2023 at 8:02 PM jian he  wrote:

>
> I still feel confused about "the key values".
>

The "key" here is the Foreign Key relationship.  In short, FOR NO KEY
UPDATE, promises that PK/FK values on the table will not be changed.  Only
non-FK/PK columns can be changed.  In neither case may the row be removed
either since that would necessitate changing the key to "non-existent".
However, the "UPDATE" part basically means "I am going to update some other
column".  On the KEY SHARE side you basically get "I only care that this
row/relationship continues to exist, you may change other attributes".

David J.