Re: grant connect to all databases

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

Re: grant connect to all databases

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 PU

Re: Repeatable Read Isolation Level "transaction start time"

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 assump

Re: Repeatable Read Isolation Level "transaction start time"

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 curre

Re: grant connect to all databases

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

Re: Repeatable Read Isolation Level "transaction start time"

"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 pr

Re: Repeatable Read Isolation Level "transaction start time"

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

Re: grant connect to all databases

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.  'CRE

Re: Repeatable Read Isolation Level "transaction start time"

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 (assumin

Re: grant connect to all databases

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 happe

Re: grant connect to all databases

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

Re: Repeatable Read Isolation Level "transaction start time"

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 (AFA

Re: Repeatable Read Isolation Level "transaction start time"

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 sy

Re: grant connect to all databases

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 ori

Re: grant connect to all databases

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

Re: grant connect to all databases

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 th

Re: grant connect to all databases

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 t

Re: grant connect to all databases

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). > >

grant connect to all databases

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.

Re: Request for Insights on ID Column Migration Approach

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 >

Re: Repeatable Read Isolation Level "transaction start time"

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 op