Re: Deferred constraint trigger semantics

[Bryn Llewellyn]

> laurenz.albe@cybertec.atwrote:
> 
>> b...@yugabyte.com wrote:
> 
>> 
>> …I tried this:
>> 
>> create table t(
>>   k serial primary key,
>>   v int not null,
>>   constraint t_v_unq unique(v) initially deferred);

Here's a better test:

-- BLUE session
start transaction isolation level read committed;
insert into t(v) values (1), (2);

-- RED session
start transaction isolation level read committed;
insert into t(v) values (1), (3);

-- BLUE session
set constraints all immediate;

-- RED session (hangs until BLUE commits).
-- Then, when it does, gets ERROR... "Key (v)=(1) already exists"
set constraints all immediate;

-- BLUE session
commit;

-- RED session
-- There are no changes to commit 'cos they were rolled back.
commit;

-- "select k, v from t order by k" (in each session) now shows that both 
sessions meet the constraint.

>> Where, in the PG doc, can I read the account of the proper mental model for 
>> the application programmer?
> 
> [See https://www.postgresql.org/docs/current/index-unique-checks.html.]

Thanks for referring me to the account "62.5. Index Uniqueness Checks". It's in 
the section "Part VII. Internals" (…contains assorted information that might be 
of use to PostgreSQL developers). I wouldn't expect to read this because I 
don't intend to write code that might become part of PG's implementation.

> I'd say that the proper mental model is that you don't need to care… The 
> exact sequence of what happens during COMMIT is interesting, but irrelevant 
> to the programmer. All that counts is "a deferred constraint is checked 
> between the time that COMMIT starts processing and the time that it returns".

Yes, I very much like this stance. It seems that, for built-in constraints 
(like "unique" or "foreign key") it's enough to understand that PG implements 
these at the "read committed" isolation level by using methods (that aren't 
exposed via SQL) to peep below the application programmer's MVCC view of the 
world to check the uncommitted state of other, concurrent, sessions.

This explains why, in the (new) test that I used above, the conflict is 
detected when the second session issues "set constraints all immediate" after 
the first already did this (i.e. long before COMMIT). In this case, the second 
session hangs until the first commits—at which point the second sees the 
uniqueness violation error.

In other words, the automagic implementation of the enforcement of built-in 
constraints allows the safe use of "set constraints all immediate" to provoke a 
possible early error that can, very usefully, be handled in PL/pgSQL code. This 
is the clue to understanding why the check of a built-in constraint, when it's 
performed as an implicit consequence of "commit", doesn't need to be within the 
small part of the operations that "commit" causes that are strictly serialized. 
(You've explained how this helps performance in multi-session scenarios.)

Critically, the special methods that implement the enforcement of built-in 
constraints aren't accessible in PL/pgSQL code and therefore not accessible in 
the "ordinary" implementation of trigger functions. This is the point that I 
failed to grasp. (Though I do see, now, that Laurenz's post says this clearly.)

I was able to demonstrate this by implementing a unique constraint with a 
deferred constraint trigger (and no use of "set constraints all immediate"). I 
simply introduced "pg_sleep(5)" between the trigger function's actual check and 
its final "return null". I copied the code below for completeness.

The "BLUE" session, because it reaches its serialized "commit" actions first, 
sees an outcome that meets the constraint. But the "RED" session has enough 
time to do its check before "BLUE" does its serialized "commit" actions. So its 
test passes too. This leaves the final database in conflict with the intended 
constraint.

I see now that the only robust use of an ordinarily (i.e. not using C) 
implemented constraint trigger (deferred or otherwise) is to enforce a single 
row-constraint. (There's a caveat that maybe, after careful analysis, you can 
work out a cunning lockings scheme to allow the safe implementation of an 
entity-level constraint without using C. But the "exactly one or two admins in 
a department" example shows that this isn't generally possible.) So it's 
reasonable that a constraint trigger must be AFTER EACH ROW. Further, it would  
make no sense to do SQL from its implementation function because the only 
values that you might defensibly use are available simply via "old" and "new".

So all that stuff I was concerned about where the deferred constraint fires 
many times when once is enough falls away because the larger endeavor makes no 
sense. (I suppose that it might matter if you implemented the trigger function 
robustly in C.)

It does strike me that the ordinary application programmer—who reads just the 
sections "CREATE TRIGGER", "Chapter 39. Triggers", "CREATE TABLE", and "SET 
CONSTRAINTS"—will

RE: Need to install Postgres Version 14 in Linux server with Client and migration steps.

[Rajamoorthy-CW, Thirumurugan 8361]

What is the helpdesk email address ? please let me know.






Regards
Thirumurugan Rajamoorthy – Biometrics Support

From: Sandeep Kumar Jakkaraju 
Sent: Monday, May 9, 2022 12:08 PM
To: Rajamoorthy-CW, Thirumurugan 8361 

Cc: pgsql-general@lists.postgresql.org
Subject: Re: Need to install Postgres Version 14 in Linux server with Client 
and migration steps.


WARNING: This message originated outside of Otsuka.
Do not click links or open attachments unless you recognize the sender and know 
the content is safe.
If you believe the contents of this email may be unsafe report it immediately 
by clicking the Phish Alert Button (PAB).
Please email it help desk

On Mon, 9 May 2022, 9:28 pm Rajamoorthy-CW, Thirumurugan 8361, 
mailto:thirumurugan.rajamoorthy...@otsuka-us.com>>
 wrote:
Hi Team,

I need to install Postgres Version 14 in Linux server with Client and migration 
steps. Can you please provide me the installation steps document ?



Regards
Thirumurugan Rajamoorthy – Biometrics Support

Re: Restricting user to see schema structure

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>>> neerajmr12...@gmail.com wrote:
>>> 
>>> ...
>> 
>> What exactly do you mean by "have created a new user and granted connection 
>> access to database"? As I understand it, there's no such thing. I mentioned 
>> a simple test in my earlier email that showed that any user (with no schema 
>> of its own and no granted privileges) can connect to any database—and see 
>> the full metadata account of all its content. I'm teaching myself to live 
>> with this.
> 
> Besides the REVOKE CONNECT, it is also possible to prevent connections to a 
> given database by a particular user by using settings in pg_hba.conf.

Adrian, I have the "pg_hba.conf" unmodified that came with the "brew" PG 
installation of PG Version 14.2 on my MacOS Big Sur laptop. It has just six 
non-comment lines, thus:

# TYPE  DATABASEUSERADDRESS METHOD
local   all all trust
hostall all 127.0.0.1/32trust
hostall all ::1/128 trust
local   replication all trust
hostreplication all 127.0.0.1/32trust
hostreplication all ::1/128 trust

This lines up with what "select * from pg_hba_file_rules" gets, thus:

 line_number | type  |   database| user_name |  address  | 
netmask | auth_method | options | error 
-+---+---+---+---+-+-+-+---
  89 | local | {all} | {all} |   |  
   | trust   | | 
  91 | host  | {all} | {all} | 127.0.0.1 | 255.255.255.255  
   | trust   | | 
  93 | host  | {all} | {all} | ::1   | 
::::::: | trust   | | 
  96 | local | {replication} | {all} |   |  
   | trust   | | 
  97 | host  | {replication} | {all} | 127.0.0.1 | 255.255.255.255  
   | trust   | | 
  98 | host  | {replication} | {all} | ::1   | 
::::::: | trust   | | 

I read the Current "21.1. The pg_hba.conf File" section and noted this tip:

«
To connect to a particular database, a user must not only pass the pg_hba.conf 
checks, but must have the CONNECT privilege for the database. If you wish to 
restrict which users can connect to which databases, it's usually easier to 
control this by granting/revoking CONNECT privilege than to put the rules in 
pg_hba.conf entries.
»

I'd like to do what this tip says. But the regime that I have allows any 
non-super user to connect to any database.

I just re-tested this with a brand-new user "joe"—and after doing "revoke 
connect on database postgres from joe".

I'm obviously missing critical "pg_hba.conf" line(s). But I can't see what to 
add from the section that I mentioned. There must be some keyword, like "none", 
meaning the opposite of "all" for users.

I tried this. (I don't have a database called "replication" so I removed those 
lines.)

local   postgrespostgrestrust
hostpostgrespostgres127.0.0.1/32trust
hostpostgrespostgres::1/128 trust

But that idea didn't work because, with my newly created user "joe", my "\c 
postgres joe" failed with a complaint that my "pg_hba.conf" had no entry for « 
user "joe", database "postgres" ».

I discovered (by "drop user") that « role name "none" is reserved ». So I added 
these lines:

local   postgresnonetrust
hostpostgresnone127.0.0.1/32trust
hostpostgresnone::1/128 trust

But even after "grant connect on database postgres to joe", my "\c postgres 
joe" still failed just as I described above. For sport, I tried this instead:

local   postgresjoe trust
hostpostgresjoe 127.0.0.1/32trust
hostpostgresjoe ::1/128 trust

But this goes against what the tip says. Anyway, after "revoke connect on 
database postgres from joe", my "\c postgres joe" succeeded.

I tried Googling. But all the hits that I found were about controlling which 
remote hosts can connect at all and what password security is to be used.

What must I do? And where is this described in the PG doc?

Re: Restricting user to see schema structure

[Adrian Klaver]

On 5/16/22 2:04 PM, Bryn Llewellyn wrote:

/adrian.kla...@aklaver.com  wrote:/


/b...@yugabyte.com  wrote:/


/neerajmr12...@gmail.com  wrote:/

...


What exactly do you mean by "have created a new user and granted 
connection access to database"? As I understand it, there's no such 
thing. I mentioned a simple test in my earlier email that showed that 
any user (with no schema of its own and no granted privileges) can 
connect to any database—and see the full metadata account of all its 
content. I'm teaching myself to live with this.


Besides the REVOKE CONNECT, it is also possible to prevent connections 
to a given database by a particular user by using settings in pg_hba.conf.


Adrian, I have the "pg_hba.conf" unmodified that came with the "brew" PG 
installation of PG Version 14.2 on my MacOS Big Sur laptop. It has just 
six non-comment lines, thus:




But that idea didn't work because, with my newly created user "joe", my 
"\c postgres joe" failed with a complaint that my "pg_hba.conf" had no 
entry for « user "joe", database "postgres" ».


So it worked you could not connect.



I discovered (by "drop user") that « role name "none" is reserved ». So 
I added these lines:


local   postgres        none                                    trust
host    postgres        none            127.0.0.1/32            trust
host    postgres        none            ::1/128                 trust


none is not going to work per:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

"user

Specifies which database user name(s) this record matches. The 
value all specifies that it matches all users. Otherwise, this is either 
the name of a specific database user, or a group name preceded by +. 
(Recall that there is no real distinction between users and groups in 
PostgreSQL; a + mark really means “match any of the roles that are 
directly or indirectly members of this role”, while a name without a + 
mark matches only that specific role.) For this purpose, a superuser is 
only considered to be a member of a role if they are explicitly a member 
of the role, directly or indirectly, and not just by virtue of being a 
superuser. Multiple user names can be supplied by separating them with 
commas. A separate file containing user names can be specified by 
preceding the file name with @.

"

none is not listed as a special name.

But this goes against what the tip says. Anyway, after "revoke connect 
on database postgres from joe", my "\c postgres joe" succeeded.


Because as mentioned previously you did not "revoke connect on database 
postgres from public".



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Restricting user to see schema structure

[David G. Johnston]

On Mon, May 16, 2022 at 2:04 PM Bryn Llewellyn  wrote:

> «
> To connect to a particular database, a user must not only pass
> the pg_hba.conf checks, but must have the CONNECT privilege for the
> database. If you wish to restrict which users can connect to which
> databases, it's usually easier to control this by
> granting/revoking CONNECT privilege than to put the rules
> in pg_hba.conf entries.
> »
>
> I'd like to do what this tip says. But the regime that I have allows any
> non-super user to connect to any database.
>
> I just re-tested this with a brand-new user "joe"—and after doing "revoke
> connect on database postgres from joe".
>
> I'm obviously missing critical "pg_hba.conf" line(s). But I can't see
> what to add from the section that I mentioned. There must be some keyword,
> like "none", meaning the opposite of "all" for users.
>

You are failing to grasp the concept of "additive permissions"
(ignoring auth-method reject for now).  The idea of a literal "none" makes
no sense - the absence of something is nothing, you do not say "none"
explicitly.


> But this goes against what the tip says. Anyway, after "revoke connect on
> database postgres from joe", my "\c postgres joe" succeeded.
>

See my answer, with link, from Friday.  Joe's ability to connect is
inherited through PUBLIC.  There is no privilege directly on Joe to revoke.

I don't quite know how to address your random experimentation with
pg_hba.conf.  None of the things you showed are surprising though - were
you expecting different?

David J.

[no subject]

[Rama Krishnan]

Hi All,

How to install pg_repack and use?


Thanks
RamaKrishnan

Re:

[David G. Johnston]

On Monday, May 16, 2022, Rama Krishnan  wrote:
>
>
> How to install pg_repack and use?
>

Two questions best answered by reading its documentation.

David J.

p.s. please remember to include a subject line in future emails.

Re:

[Amul Sul]

On Tue, May 17, 2022 at 9:12 AM Rama Krishnan  wrote:
>
> Hi All,
>
> How to install pg_repack and use?

Check this: https://reorg.github.io/pg_repack/

Regards,
Amul

Re: Restricting user to see schema structure

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>>> adrian.kla...@aklaver.com wrote:
>>> 
 b...@yugabyte.com  wrote:
 
> neerajmr12...@gmail.com  wrote:
> 
> ...
 
 What exactly do you mean by "have created a new user and granted 
 connection access to database"?
>>> 
>>> Besides the REVOKE CONNECT, it is also possible to prevent connections to a 
>>> given database by a particular user by using settings in pg_hba.conf.
>> 
>> 
>> Adrian, I have the "pg_hba.conf" unmodified that came with the "brew" PG 
>> installation of PG Version 14.2 on my MacOS Big Sur laptop. It has just six 
>> non-comment lines... [But thing don't work as I want.]
> 
> Because as mentioned previously you did not "revoke connect on database 
> postgres from public".

Right, I see the importance of this now. I now realize that when a database is 
created, CONNECT on it is automatically granted to PUBLIC. But there's no 
mention of this (or what to read to learn that this is the case) in the 
"pg_hba.conf" chapter. Nor does the section on the CREATE DATABASE statement 
mention this. How is the neophyte supposed to know about this behavior?

Another thing that confused me was the significance of the lines for the 
database "replication" in the "pg_hba.conf" that came with my installation. Add 
to this the mutually exclusive keywords "REPLICATION" and "NO REPLICATION" in 
the CREATE ROLE statement. ("These clauses determine whether a role is a 
replication role.") So this seems to be a distinct use of the word from how 
it's used in "pg_hba.conf" as the name of a database (that might well not 
exist). Strangely, the CREATE ROLE doc says that you don't need either of 
"REPLICATION" or "NO REPLICATION" but it doesn't say what the default is.

David Johnston wrote this in a separate thread:

> I don't quite know how to address your random experimentation with 
> pg_hba.conf.  None of the things you showed are surprising though - were you 
> expecting different?

My reports of my random experimentation were the email equivalent of the "think 
aloud" approach to usability testing. That paradigm has the creators of a 
system observe a new user trying to get things done (using any appropriate doc 
that's available). Sometimes, the user appears to be trying things randomly. 
Then the creators ask "why did you do that"—and they learn what faulty mental 
model the user has formed. And then they try to find out how the new user came 
to acquire that model. Often, the problem is that the doc (or the UI, when it's 
meant to me self-evident) suffers from what Steven Pinker calls the "curse of 
knowledge" in his book "The sense of style".

Anyway, with my experimentation and with the clues that you two (Adrian and 
David) have given me, I arrived that the following practice. It seems to give 
me what I want—i.e. a regime where ordinary new users that I create can operate 
without me needing to change the "pg_hba.conf" file and where they can connect 
to the one-and-only database that I intend and then perform exactly and only 
the tasks that I intend—in other words a regime that honors the principle of 
least privilege. (We've discussed the caveat that I can't prevent them from 
reading all of the metadata across all databases earlier.)

* I use this bare bones "pg_hba.conf" file.

 local   all all trust
 hostall all 127.0.0.1/32trust
 hostall all ::1/128 trust

* I say "\c postgres postgres" and use a script to strip the cluster done to 
its bare minimum—in my case: the users "Bllewell" and postgres; and the 
databases postgres, template0, and template1.

I say "revoke connect on database postgres from public" and "drop schema if 
exists public". (And I drop any other schemas that might have been created in 
the database postgres).

* When I create a database, I immediately drop its public schema and revoke 
connect on it from public.

* When I create a user, I say NOREPLICATION and grant it CONNECT on just the 
one database (it's always one) that I intend. However, when I create a 
superuser, I cannot prevent it from connecting to *any* database (present or 
future).