Re: grant connect to all databases

[Adrian Klaver]

On 10/5/24 15:04, Matt Zagrabelny wrote:


psql (15.8 (Debian 15.8-0+deb12u1))


Where did you install it from or where are you running it?


Installed from Debian repos via apt via puppet.

Still digging...


To me it looks like something is doing:

REVOKE CONNECT
ON DATABASE 
FROM PUBLIC;



-m


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

Re: grant connect to all databases

[Tom Lane]

Matt Zagrabelny  writes:
>  ...but I still cannot connect:

> $ psql -d test -U alice
> psql: error: connection to server on socket
> "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied for
> database "test"
> DETAIL:  User does not have CONNECT privilege.

This shouldn't be happening, since as mentioned upthread our default
for newly-created databases is that they have CONNECT granted to
PUBLIC.  It works fine for me, even for a user with no special
permissions:

postgres=# create user alice;
CREATE ROLE
postgres=# create database test;
CREATE DATABASE
postgres=# \c test alice
You are now connected to database "test" as user "alice".

If I explicitly revoke the privilege, I can duplicate your
results:

test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# revoke connect on database test from public;
REVOKE
postgres=# \c test alice
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  permission 
denied for database "test"
DETAIL:  User does not have CONNECT privilege.

I wonder if your puppet recipe is revoking that behind your
back, or if you are using some modified version of Postgres
with different ideas about default privileges.  Looking at
psql \l output for the test DB might be informative.

regards, tom lane

Re: grant connect to all databases

[Matt Zagrabelny]

On Sat, Oct 5, 2024 at 11:26 AM Adrian Klaver 
wrote:

> On 10/5/24 09:04, Matt Zagrabelny wrote:
> >
> >
> > On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver  > > wrote:
> >
> > On 10/5/24 07:13, Matt Zagrabelny wrote:
> >  > Hi David (and others),
> >  >
> >  > Thanks for the info about Public.
> >  >
> >  > I should expound on my original email.
> >  >
> >  > In our dev and test environments our admins (alice, bob, eve) are
> >  > superusers. In production environments we'd like the admins to be
> > read-only.
> >
> > What are the REVOKE and GRANT commands you use to achieve that?
> >
> >
> > GRANT alice TO pg_read_all_data;
>
> Does alice have existing GRANTs?
>

Nope. I create the role (via puppet) and then add the GRANT
pg_read_all_data TO (via puppet).


>
> I would try:
>
> GRANT pg_read_all_data TO alice;
>
> As example:
>
> psql -d test -U postgres
>
>   List of role grants
>   Role name  |  Member of   |   Options| Grantor
> +--+--+--
>   aklaver| app_admin| INHERIT, SET | postgres
>   aklaver| production   | INHERIT, SET | postgres
>   dd_admin   | dd_owner | ADMIN, SET   | postgres
>   dd_user| dd_admin | INHERIT, SET | postgres
>   pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
>   pg_monitor | pg_read_all_stats| INHERIT, SET | postgres
>   pg_monitor | pg_stat_scan_tables  | INHERIT, SET | postgres
>   postgres   | dd_owner | INHERIT, SET | postgres
>
>
> grant pg_read_all_data to adrian;
> GRANT ROLE
>
> test=# \drgS
>

What is \drgS? I don't believe I have that.


>   List of role grants
>   Role name  |  Member of   |   Options| Grantor
> +--+--+--
>   adrian | pg_read_all_data | INHERIT, SET | postgres
>   aklaver| app_admin| INHERIT, SET | postgres
>   aklaver| production   | INHERIT, SET | postgres
>   dd_admin   | dd_owner | ADMIN, SET   | postgres
>   dd_user| dd_admin | INHERIT, SET | postgres
>   pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
>   pg_monitor | pg_read_all_stats| INHERIT, SET | postgres
>   pg_monitor | pg_stat_scan_tables  | INHERIT, SET | postgres
>   postgres   | dd_owner | INHERIT, SET | postgres
>
>   \dt csv_test
>List of relations
>   Schema |   Name   | Type  |  Owner
> +--+---+--
>   public | csv_test | table | postgres
>
> test=# \q
>
> psql -d test -U adrian
>
> test=> select * from csv_test ;
>   id | val
> +--
>1 | test
>2 | dog
>3 | cat
>4 | test
>5 | fish
>
>
That looks good.

Here is the output of puppet's create role:

drop role alice;

The next puppet run and I get:

 'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN
NOCREATEROLE NOCREATEDB NOSUPERUSER  CONNECTION LIMIT -1'
GRANT pg_read_all_data TO alice;

test=# \du
 List of roles
  Role name   | Attributes
| Member of
--++
 alice|
   | {pg_read_all_data}
 postgres | Superuser, Create role, Create DB, Replication,
Bypass RLS | {}


 ...but I still cannot connect:

$ psql -d test -U alice
psql: error: connection to server on socket
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied for
database "test"
DETAIL:  User does not have CONNECT privilege.

Thanks for the help!

-m

Re: Repeatable Read Isolation Level "transaction start time"

[Tom Lane]

Greg Sabino Mullane  writes:
> All we can guarantee
> via pg_stat_activity is that if xact_start and query_start *are* identical,
> no snapshot has been granted yet,

Surely that's not true either.  xact_start = query_start implies that
the current statement is the first in its transaction (assuming
sufficiently fine-grained clock timestamps, something I'm not sure is
an entirely safe assumption).  But if that statement is not simply
a BEGIN, it's likely obtained its own transaction snapshot after a
few microseconds.

As long as "read the system clock" is a distinct operation from
"read a snapshot", there are going to be skew issues here.  We
could maybe eliminate that by reading the clock while holding the
lock that prevents commits while reading a snapshot, but I doubt
that anybody is going to accept that on performance grounds.
Adding a not-guaranteed-cheap syscall inside that extremely hot
code path seems unsatisfactory.

Also, we currently do guarantee that xact_start matches query_start
for the first statement of the transaction (the converse of what
I said above).  Removing that guarantee in order to add some other
one wouldn't necessarily please everybody.

regards, tom lane

Re: grant connect to all databases

[Adrian Klaver]

On 10/5/24 11:24, Matt Zagrabelny wrote:






Nope. I create the role (via puppet) and then add the GRANT 
pg_read_all_data TO (via puppet).





What is \drgS? I don't believe I have that.


That is available in Postgres 16+, you must running be in an instance of 
Postgres before that.



  'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN 
NOCREATEROLE NOCREATEDB NOSUPERUSER  CONNECTION LIMIT -1'

GRANT pg_read_all_data TO alice;




  ...but I still cannot connect:

$ psql -d test -U alice
psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied 
for database "test"

DETAIL:  User does not have CONNECT privilege.


Something is going on in the background.

What version of Postgres?

Where did you install it from or where are you running it?



Thanks for the help!

-m


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

grant connect to all databases

[Matt Zagrabelny]

Hello,

I'd like to have a read-only user for all databases.

I found the pg_read_all_data role predefined role, which I granted to my RO
user:

GRANT pg_read_all_data TO ro_user;

...but I cannot connect to my database(s).

I'd like to not have to iterate over all the databases and "GRANT
CONNECT...".

Is there a way to do this with just one GRANT or equivalent command?

Thanks for any help!

-m

Re: grant connect to all databases

[Matt Zagrabelny]

Hi David (and others),

Thanks for the info about Public.

I should expound on my original email.

In our dev and test environments our admins (alice, bob, eve) are
superusers. In production environments we'd like the admins to be read-only.

Is the Public role something I can leverage to achieve this desire?

Thanks for the help!

-m



On Sat, Oct 5, 2024 at 9:02 AM David G. Johnston 
wrote:

> On Saturday, October 5, 2024, Matt Zagrabelny  wrote:
>
>> Hello,
>>
>> I'd like to have a read-only user for all databases.
>>
>> I found the pg_read_all_data role predefined role, which I granted to my
>> RO user:
>>
>> GRANT pg_read_all_data TO ro_user;
>>
>> ...but I cannot connect to my database(s).
>>
>> I'd like to not have to iterate over all the databases and "GRANT
>> CONNECT...".
>>
>> Is there a way to do this with just one GRANT or equivalent command?
>>
>
>
> The pseudo-role Public exists for just this kind of thing.  In fact, in a
> default installation it already is given connect privileges on all
> databases created by the bootstrap superuser.
>
> David J.
>
>

Re: grant connect to all databases

[Adrian Klaver]

On 10/5/24 09:04, Matt Zagrabelny wrote:



On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver > wrote:


On 10/5/24 07:13, Matt Zagrabelny wrote:
 > Hi David (and others),
 >
 > Thanks for the info about Public.
 >
 > I should expound on my original email.
 >
 > In our dev and test environments our admins (alice, bob, eve) are
 > superusers. In production environments we'd like the admins to be
read-only.

What are the REVOKE and GRANT commands you use to achieve that?


GRANT alice TO pg_read_all_data;


Does alice have existing GRANTs?

I would try:

GRANT pg_read_all_data TO alice;

As example:

psql -d test -U postgres

 List of role grants
 Role name  |  Member of   |   Options| Grantor
+--+--+--
 aklaver| app_admin| INHERIT, SET | postgres
 aklaver| production   | INHERIT, SET | postgres
 dd_admin   | dd_owner | ADMIN, SET   | postgres
 dd_user| dd_admin | INHERIT, SET | postgres
 pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
 pg_monitor | pg_read_all_stats| INHERIT, SET | postgres
 pg_monitor | pg_stat_scan_tables  | INHERIT, SET | postgres
 postgres   | dd_owner | INHERIT, SET | postgres


grant pg_read_all_data to adrian;
GRANT ROLE

test=# \drgS
 List of role grants
 Role name  |  Member of   |   Options| Grantor
+--+--+--
 adrian | pg_read_all_data | INHERIT, SET | postgres
 aklaver| app_admin| INHERIT, SET | postgres
 aklaver| production   | INHERIT, SET | postgres
 dd_admin   | dd_owner | ADMIN, SET   | postgres
 dd_user| dd_admin | INHERIT, SET | postgres
 pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
 pg_monitor | pg_read_all_stats| INHERIT, SET | postgres
 pg_monitor | pg_stat_scan_tables  | INHERIT, SET | postgres
 postgres   | dd_owner | INHERIT, SET | postgres

 \dt csv_test
  List of relations
 Schema |   Name   | Type  |  Owner
+--+---+--
 public | csv_test | table | postgres

test=# \q

psql -d test -U adrian

test=> select * from csv_test ;
 id | val
+--
  1 | test
  2 | dog
  3 | cat
  4 | test
  5 | fish




...and then I could do something like this:
-- for $database in $databases;
GRANT CONNECT ON database $database TO alice;

...but I'd like to achieve it without the `for` loop.


 >
 > Is the Public role something I can leverage to achieve this desire?

You should read:

https://www.postgresql.org/docs/current/ddl-priv.html



Will do.




  From your original post:

"but I cannot connect to my database"

Was that due to a GRANT issue or a pg_hba.conf issue?


It was due to the missing GRANT CONNECT from above. pg_hba looks OK.

What was the actual complete error?


alice$ psql foo
psql: error: connection to server at "db.example.com 
" (fe80:100), port 5432 failed: FATAL: 
  permission denied for database "foo"
...after I GRANT CONNECT, I can connect. However, I don't want to have 
to iterate over all the databases to achieve the GRANT CONNECT.


I guess I was hoping that the pg_read_all_data would also allow 
connecting. Or if it didn't, there could/would be a 
pg_connect_all_databases role.


Cheers,

-m


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

Re: Repeatable Read Isolation Level "transaction start time"

[Adrian Klaver]

On 10/5/24 02:14, Peter J. Holzer wrote:

On 2024-09-25 18:09:44 -0400, Tom Lane wrote:

"Peter J. Holzer"  writes:




Admittedly, that would normally not be a very long interval if BEGIN
did both things ... but on a busy system you could lose the CPU for
awhile in between.


Assuming that the system does have a global clock of sufficiently
fine resolution which returns strictly monotonically increasing
timestamps[1], I think the following is true:

Every snapshot divides the set of transactions into two non-overlapping
subsets: Those which have committed early enough that their effects are
visible in the snapshot and those which haven't. Let's call the first set
the "earlier" transactions and the second the "later" transactions. Let's
call the current transaction c and any transaction in the earlier set e
(we ignore the later transactions for now).

Performing a commit and taking a snapshot take some time, but there
should be a time t_C(e) in each commit and t_S(c) in the snapshot, such
that t_C(e) < t_S(c) for each "earlier" transaction.


Assuming t_C is time of commit and t_S is time of snapshot, is the above 
not the crux of the matter? Namely when in the current transaction the 
snapshot is actually taken. That would determine what constitutes an 
earlier visible transaction relative to the current transaction. In 
other words I am not seeing how this changes anything?




Within each transaction each timestamp t which could be visible outside
of the transaction must have been obtained before the commit,
so t(e) < t_C(e) < t_S(c).

If we choose the transaction_timestamp to be >= t_S, then
transaction_timestamp(e) < t_C(e) < t_S(c) <= transaction_timestamp(c)
and therefore
transaction_timestamp(e) < transaction_timestamp(c)

Such a guarantee might be useful for some applications and it's not
(IMHO) an entirely unreasonable assumption, but it's not true for
PostgreSQL. So a programmer should be aware of that.

 hp



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

Re: Repeatable Read Isolation Level "transaction start time"

[Greg Sabino Mullane]

While working on a doc patch for this, I realized that the situation is
worse than I originally thought. This means that anyone relying on
pg_stat_activity.xact_start is not really seeing the time of the snapshot.
They are seeing the time that BEGIN was issued. Further, there is no way to
tell (AFAICT) when the snapshot was granted (i.e. when the transaction
actually started for purposes of MVCC comparisons). All we can guarantee
via pg_stat_activity is that if xact_start and query_start *are* identical,
no snapshot has been granted yet, and if they are not identical, then the
snapshot *might* have been granted, might not (depending on SHOW vs SELECT
for example). I suppose checking "query" could show that, but all you have
then is a general window saying that the snapshot was created sometime
after xact_start but no later than query_start (and could be a lot earlier
if this ain't query number one).

Maybe we doc patch pg_stat_activity too? Actually, let me just post my
quick work-in-progress patch here in the meantime for discussion.

Cheers,
Greg


0001-Clarify-READ-REPEATABLE-behavior-a-bit-more.patch
Description: Binary data

Re: Repeatable Read Isolation Level "transaction start time"

[Peter J. Holzer]

On 2024-10-05 09:59:00 -0700, Adrian Klaver wrote:
> On 10/5/24 02:14, Peter J. Holzer wrote:
> > On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> > > "Peter J. Holzer"  writes:
> > > Admittedly, that would normally not be a very long interval if BEGIN
> > > did both things ... but on a busy system you could lose the CPU for
> > > awhile in between.
> > 
> > Assuming that the system does have a global clock of sufficiently
> > fine resolution which returns strictly monotonically increasing
> > timestamps[1], I think the following is true:
> > 
> > Every snapshot divides the set of transactions into two non-overlapping
> > subsets: Those which have committed early enough that their effects are
> > visible in the snapshot and those which haven't. Let's call the first set
> > the "earlier" transactions and the second the "later" transactions. Let's
> > call the current transaction c and any transaction in the earlier set e
> > (we ignore the later transactions for now).
> > 
> > Performing a commit and taking a snapshot take some time, but there
> > should be a time t_C(e) in each commit and t_S(c) in the snapshot, such
> > that t_C(e) < t_S(c) for each "earlier" transaction.
> 
> Assuming t_C is time of commit and t_S is time of snapshot, is the
> above not the crux of the matter? Namely when in the current
> transaction the snapshot is actually taken. That would determine what
> constitutes an earlier visible transaction relative to the current
> transaction. In other words I am not seeing how this changes anything?

The important part is in the last paragraph:

> > If we choose the transaction_timestamp to be >= t_S, then
> > transaction_timestamp(e) < t_C(e) < t_S(c) <= transaction_timestamp(c)
> > and therefore
> > transaction_timestamp(e) < transaction_timestamp(c)

In PostgreSQL, transaction_timestamp is taken during BEGIN (as Greg
noted). If it was instead taken at the end of the snapshot, it would be
guaranteed to be later than any transaction_timestamp of an earlier
transaction.

Again, I'm not arguing for such a change, but I'm wondering if recording
transaction_timestamp just after the snapshot might be a safe change or
whether that might break some assumption that programmers can currently
make.

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: Repeatable Read Isolation Level "transaction start time"

[Tom Lane]

"Peter J. Holzer"  writes:
> Again, I'm not arguing for such a change, but I'm wondering if recording
> transaction_timestamp just after the snapshot might be a safe change or
> whether that might break some assumption that programmers can currently
> make.

As I mentioned upthread, we currently promise that xact_start matches
the query_start of the transaction's first statement.  (I'm not sure
how well that's documented, but the code goes out of its way to make
it so, so somebody thought it was important.)

Another issue with redefining things like that is that presumably,
for a session that has issued BEGIN but not yet taken a snapshot,
xact_start would have to read as NULL, misleadingly implying that
the session doesn't have an open transaction.

Lastly, wouldn't postponing the collection of the timestamp like
that break the very property you want to promise, by making other
transactions' timestamps nontrivially later than they are now?

I think if we wanted to do something here, it'd make more sense to
keep xact_start as it stands and introduce a new variable
snapshot_timestamp or something like that.  Then maybe we could have
some guarantees about what you get when comparing other sessions'
xact_start to your own snapshot_timestamp.  But I'm not convinced we
can really guarantee anything without reading the snapshot_timestamp
within the snapshot-collecting critical section, and I'm not for that.

regards, tom lane

Re: Repeatable Read Isolation Level "transaction start time"

[Peter J. Holzer]

On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> "Peter J. Holzer"  writes:
> > On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote:
> >> This might well be a failure of imagination on my part, but when would
> >> it pragmatically matter that the snapshot is taken at the first
> >> statement as opposed to at BEGIN?
> 
> > It may make a difference if you're comparing timestamps.
> 
> > For example, if you're using isolation level REPEATABLE READ and
> > (mistakenly) assume that the snapshot is taken at BEGIN, you would
> > expect any  transaction_timestamp() written by a different transaction
> > and readable by this transaction to be earlier than the
> > transaction_timestamp() of this transaction.
> 
> > But that's wrong because the other transaction could have happened
> > entirely in the time between your BEGIN and the statement which actually
> > triggers the snapshot.
> 
> I don't find that hugely compelling, because there's always going
> to be some skew between the time we read the clock for the timestamp
> and the time we obtain the snapshot.

I wasn't arguing for a change in PostgreSQL's behaviour, just pointing
out a scenario where that would make a user-observable difference.

> Admittedly, that would normally not be a very long interval if BEGIN
> did both things ... but on a busy system you could lose the CPU for
> awhile in between.

Assuming that the system does have a global clock of sufficiently
fine resolution which returns strictly monotonically increasing
timestamps[1], I think the following is true:

Every snapshot divides the set of transactions into two non-overlapping
subsets: Those which have committed early enough that their effects are
visible in the snapshot and those which haven't. Let's call the first set
the "earlier" transactions and the second the "later" transactions. Let's
call the current transaction c and any transaction in the earlier set e
(we ignore the later transactions for now).

Performing a commit and taking a snapshot take some time, but there
should be a time t_C(e) in each commit and t_S(c) in the snapshot, such
that t_C(e) < t_S(c) for each "earlier" transaction.

Within each transaction each timestamp t which could be visible outside
of the transaction must have been obtained before the commit,
so t(e) < t_C(e) < t_S(c).

If we choose the transaction_timestamp to be >= t_S, then
transaction_timestamp(e) < t_C(e) < t_S(c) <= transaction_timestamp(c)
and therefore 
transaction_timestamp(e) < transaction_timestamp(c)

Such a guarantee might be useful for some applications and it's not
(IMHO) an entirely unreasonable assumption, but it's not true for
PostgreSQL. So a programmer should be aware of that.

hp


[1] Not sure of this is true for Linux. It's certainly possible to never
step the clock backwards (not even during leap seconds), and the
resolution should be fine enough that you won't get two equal
timestamps on the same core, but with multiple cores I'm not
sure that processes running on different cores are guaranteed to get
strictly monotonically increasing timestamps. But as they say, while
all models are wrong, some are useful, and I think this is close
enough to reality to be useful.

-- 
   _  | 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: Request for Insights on ID Column Migration Approach

[Peter J. Holzer]

On 2024-09-27 18:37:35 -0400, Tom Lane wrote:
> "Peter J. Holzer"  writes:
> > As you can see, adding the primary key takes just as much time as
> > creating the unique index. So it doesn't look like PostgreSQL is able to
> > take advantage of the existing index (which makes sense since it still
> > has to create a new index).
> 
> No, but you can attach an existing unique index as a primary key:
> 
> regression=# create table t1 (f1 int not null);
> CREATE TABLE
> regression=# create unique index t1_f1 on t1(f1);
> CREATE INDEX
> regression=# alter table t1 add primary key using index t1_f1;
> ALTER TABLE

Ah, yes. I thought that was possible but I checked the docs[1] and
didn't see it. I checked again before writing this mail and still didn't
see it. Only when I started copy-pasting relevant parts of th synopsis
into the mail did I notice "ADD table_constraint_using_index". I'll get
new glasses soon, I promise. ;-)

hp


[1] https://www.postgresql.org/docs/current/sql-altertable.html


-- 
   _  | 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: grant connect to all databases

[Adrian Klaver]

On 10/5/24 07:13, Matt Zagrabelny wrote:

Hi David (and others),

Thanks for the info about Public.

I should expound on my original email.

In our dev and test environments our admins (alice, bob, eve) are 
superusers. In production environments we'd like the admins to be read-only.


What are the REVOKE and GRANT commands you use to achieve that?



Is the Public role something I can leverage to achieve this desire?


You should read:

https://www.postgresql.org/docs/current/ddl-priv.html


From your original post:

"but I cannot connect to my database"

Was that due to a GRANT issue or a pg_hba.conf issue?

What was the actual complete error?



Thanks for the help!

-m



On Sat, Oct 5, 2024 at 9:02 AM David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


On Saturday, October 5, 2024, Matt Zagrabelny mailto:mzagr...@d.umn.edu>> wrote:

Hello,

I'd like to have a read-only user for all databases.

I found the pg_read_all_data role predefined role, which I
granted to my RO user:

GRANT pg_read_all_data TO ro_user;

...but I cannot connect to my database(s).

I'd like to not have to iterate over all the databases and
"GRANT CONNECT...".

Is there a way to do this with just one GRANT or equivalent command?



The pseudo-role Public exists for just this kind of thing.  In fact,
in a default installation it already is given connect privileges on
all databases created by the bootstrap superuser.

David J.



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

Re: grant connect to all databases

[David G. Johnston]

On Saturday, October 5, 2024, Matt Zagrabelny  wrote:

> Hello,
>
> I'd like to have a read-only user for all databases.
>
> I found the pg_read_all_data role predefined role, which I granted to my
> RO user:
>
> GRANT pg_read_all_data TO ro_user;
>
> ...but I cannot connect to my database(s).
>
> I'd like to not have to iterate over all the databases and "GRANT
> CONNECT...".
>
> Is there a way to do this with just one GRANT or equivalent command?
>


The pseudo-role Public exists for just this kind of thing.  In fact, in a
default installation it already is given connect privileges on all
databases created by the bootstrap superuser.

David J.

Re: grant connect to all databases

[Matt Zagrabelny]

On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver 
wrote:

> On 10/5/24 07:13, Matt Zagrabelny wrote:
> > Hi David (and others),
> >
> > Thanks for the info about Public.
> >
> > I should expound on my original email.
> >
> > In our dev and test environments our admins (alice, bob, eve) are
> > superusers. In production environments we'd like the admins to be
> read-only.
>
> What are the REVOKE and GRANT commands you use to achieve that?
>

GRANT alice TO pg_read_all_data;

...and then I could do something like this:
-- for $database in $databases;
GRANT CONNECT ON database $database TO alice;

...but I'd like to achieve it without the `for` loop.


>
> >
> > Is the Public role something I can leverage to achieve this desire?
>
> You should read:
>
> https://www.postgresql.org/docs/current/ddl-priv.html


Will do.


>
>
>
>  From your original post:
>
> "but I cannot connect to my database"
>
> Was that due to a GRANT issue or a pg_hba.conf issue?
>

It was due to the missing GRANT CONNECT from above. pg_hba looks OK.


> What was the actual complete error?
>

alice$ psql foo
psql: error: connection to server at "db.example.com" (fe80:100), port 5432
failed: FATAL:  permission denied for database "foo"

...after I GRANT CONNECT, I can connect. However, I don't want to have to
iterate over all the databases to achieve the GRANT CONNECT.

I guess I was hoping that the pg_read_all_data would also allow connecting.
Or if it didn't, there could/would be a pg_connect_all_databases role.

Cheers,

-m

Re: Repeatable Read Isolation Level "transaction start time"

[Peter J. Holzer]

On 2024-10-05 17:03:08 -0400, Tom Lane wrote:
> "Peter J. Holzer"  writes:
> > Again, I'm not arguing for such a change, but I'm wondering if recording
> > transaction_timestamp just after the snapshot might be a safe change or
> > whether that might break some assumption that programmers can currently
> > make.
> 
> As I mentioned upthread, we currently promise that xact_start matches
> the query_start of the transaction's first statement.  (I'm not sure
> how well that's documented, but the code goes out of its way to make
> it so, so somebody thought it was important.)

It's mentioned in 
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

| statement_timestamp() and transaction_timestamp() return the same value 
during the first command of a transaction

But that seems to be wrong in practice. The first statement of a
transaction is BEGIN. So whatever command calls statement_timestamp() is
already the second command, so statement_timestamp() is later than
transaction_timestamp(). This is even true if the BEGIN and SELECT are
on the same line:

hjp=> begin; select transaction_timestamp(), statement_timestamp(), 
clock_timestamp(); rollback;
BEGIN
Time: 0.406 ms
╔═[ RECORD 1 ]══╤═══╗
║ transaction_timestamp │ 2024-10-05 23:55:47.356582+02 ║
║ statement_timestamp   │ 2024-10-05 23:55:47.357106+02 ║
║ clock_timestamp   │ 2024-10-05 23:55:47.357397+02 ║
╚═══╧═══╝

Time: 0.570 ms
ROLLBACK
Time: 0.285 ms

The difference are only 0.5 ms, but it's not zero.

I think the only case where transaction_timestamp() = statement_timestamp()
is outside of a transaction.


> Lastly, wouldn't postponing the collection of the timestamp like
> that break the very property you want to promise, by making other
> transactions' timestamps nontrivially later than they are now?

I don't think so. To restate the property in SQL terms:

Given one or more processes performing

begin;
...
insert into t(ts) values(transaction_timestamp();
...
commit;

and one which performs

begin;
set transaction isolation level repeatable read ;
...
select max(ts) < transaction_timestamp() from t;

that select statement returns always true.

If transaction_timestamp() returns a later timestamp, it will still be
true.


> I think if we wanted to do something here, it'd make more sense to
> keep xact_start as it stands and introduce a new variable
> snapshot_timestamp or something like that.

Actually I think an application can easily get a timestamp with that
property by calling
select clock_timestamp();
as the first (real) statement in the transaction. That will trigger the
snapshot and the timestamp will be made just after the snapshot (and
hence also after any commit seen by that snapshot).

(Using statement_timestamp() OTOH would not work because that timestamp
is from "the time of receipt of the latest command message from the
client", i.e. just before the snapshot, so there could still be commits
between that timestamp and the snapshot.)

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: Repeatable Read Isolation Level "transaction start time"

[Adrian Klaver]

On 10/5/24 15:25, Peter J. Holzer wrote:

On 2024-10-05 17:03:08 -0400, Tom Lane wrote:

"Peter J. Holzer"  writes:

Again, I'm not arguing for such a change, but I'm wondering if recording
transaction_timestamp just after the snapshot might be a safe change or
whether that might break some assumption that programmers can currently
make.


As I mentioned upthread, we currently promise that xact_start matches
the query_start of the transaction's first statement.  (I'm not sure
how well that's documented, but the code goes out of its way to make
it so, so somebody thought it was important.)


It's mentioned in
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

| statement_timestamp() and transaction_timestamp() return the same value 
during the first command of a transaction

But that seems to be wrong in practice. The first statement of a
transaction is BEGIN. So whatever command calls statement_timestamp() is
already the second command, so statement_timestamp() is later than
transaction_timestamp(). This is even true if the BEGIN and SELECT are
on the same line:

hjp=> begin; select transaction_timestamp(), statement_timestamp(), 
clock_timestamp(); rollback;
BEGIN
Time: 0.406 ms
╔═[ RECORD 1 ]══╤═══╗
║ transaction_timestamp │ 2024-10-05 23:55:47.356582+02 ║
║ statement_timestamp   │ 2024-10-05 23:55:47.357106+02 ║
║ clock_timestamp   │ 2024-10-05 23:55:47.357397+02 ║
╚═══╧═══╝

Time: 0.570 ms
ROLLBACK
Time: 0.285 ms

The difference are only 0.5 ms, but it's not zero.

I think the only case where transaction_timestamp() = statement_timestamp()
is outside of a transaction.



AFAIK that is still a transaction:

https://www.postgresql.org/docs/current/sql-begin.html

By default (without BEGIN), PostgreSQL executes transactions in 
“autocommit” mode, that is, each statement is executed in its own 
transaction and a commit is implicitly performed at the end of the 
statement (if execution was successful, otherwise a rollback is done).



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

Re: grant connect to all databases

[Matt Zagrabelny]

On Sat, Oct 5, 2024 at 3:12 PM Adrian Klaver 
wrote:

> On 10/5/24 11:24, Matt Zagrabelny wrote:
> >
>
> >
> > Nope. I create the role (via puppet) and then add the GRANT
> > pg_read_all_data TO (via puppet).
> >
>
> > What is \drgS? I don't believe I have that.
>
> That is available in Postgres 16+, you must running be in an instance of
> Postgres before that.
>

Ah. Yup!


>
>
> >   'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN
> > NOCREATEROLE NOCREATEDB NOSUPERUSER  CONNECTION LIMIT -1'
> > GRANT pg_read_all_data TO alice;
> >
>
> >   ...but I still cannot connect:
> >
> > $ psql -d test -U alice
> > psql: error: connection to server on socket
> > "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied
> > for database "test"
> > DETAIL:  User does not have CONNECT privilege.
>
> Something is going on in the background.
>

Agreed.


>
> What version of Postgres?
>


psql (15.8 (Debian 15.8-0+deb12u1))



> Where did you install it from or where are you running it?
>

Installed from Debian repos via apt via puppet.


Still digging...

-m

Re: grant connect to all databases

[Matt Zagrabelny]

On Sat, Oct 5, 2024 at 5:43 PM Adrian Klaver 
wrote:

> On 10/5/24 15:04, Matt Zagrabelny wrote:
>
> > psql (15.8 (Debian 15.8-0+deb12u1))
> >
> >
> > Where did you install it from or where are you running it?
> >
> >
> > Installed from Debian repos via apt via puppet.
> >
> > Still digging...
>
> To me it looks like something is doing:
>
> REVOKE CONNECT
>  ON DATABASE 
>  FROM PUBLIC;
>
>
I think we have a winner.

When puppet creates the database, it revokes connect:

https://github.com/puppetlabs/puppetlabs-postgresql/blob/main/manifests/server/database.pp#L55-L80

I'll have to ponder a solution. :(

Thanks for all the help (David, Adrian, and Tom)! I very much appreciate
you taking time to give me some guidance and pointers.

Cheers!

-m