Re: List user databases

2022年11月10日(木) 14:16 Adrian Klaver : > > On 11/9/22 20:57, Ian Lawrence Barwick wrote: > > 2022年11月10日(木) 13:41 Igor Korot : > >> > >> Hi, ALL, > >> According to > >> https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, > >> there are generally 3

Re: "set role" semantics

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Anyway, all this is moot (except in that thinking about it helps me to >> enrich my mental model) because the privilege notions here will never change. > > So, I want it but not really. I’d rather say “I’d very much prefer

Re: List user databases

Hi, On Wed, Nov 09, 2022 at 09:16:40PM -0800, Adrian Klaver wrote: > On 11/9/22 20:57, Ian Lawrence Barwick wrote: > > > > template0 and template1 are the mandatory system databases which > > cannot be dropped. > > Actually that is not strictly true: > >

Re: "set role" semantics

On 11/9/22 15:23, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: b...@yugabyte.com wrote: Connecting to database and the role that is in play inside a session are two different things. Making them the same would make things [security define vs "security invoker"] go sideways. I

Re: List user databases

On 11/9/22 20:57, Ian Lawrence Barwick wrote: 2022年11月10日(木) 13:41 Igor Korot : Hi, ALL, According to https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, there are generally 3 system DBs. However I'm confused with the word general. How many

Re: List user databases

2022年11月10日(木) 13:41 Igor Korot : > > Hi, ALL, > According to > https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, > there are generally 3 system DBs. > > However I'm confused with the word general. > How many system databases can be made on a PG

List user databases

Hi, ALL, According to https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, there are generally 3 system DBs. However I'm confused with the word general. How many system databases can be made on a PG server? Thank you.

Re: Q: pg_hba.conf separate database names file format

On Wed, Nov 09, 2022 at 04:02:43AM -0600, Ron wrote: > Are these "include" files supposed to solve the problem of having a *lot* of > databases (or users) that you want to allow access to? Yes, splitting the list of users and database eases the maintenance and readability of pg_hba.conf as each

Re: "set role" semantics

adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: > > Connecting to database and the role that is in play inside a session are two > different things. Making them the same would make things [security define vs > "security invoker"] go sideways. I said nothing to suggest that the

Re: copy file from a client app to remote postgres isntance

On 2022-11-09 12:57:23 -0600, Ron wrote: > On 11/9/22 10:17, Peter J. Holzer wrote: > > On 2022-11-07 14:40:40 -0600, Ron wrote: > > > On 11/7/22 10:57, Вадим Самохин wrote: > > > I have an application that must copy a local file in csv format to a > > > postgres table on a remote host.

Re: "set role" semantics

On 11/9/22 12:31, Bryn Llewellyn wrote: Thanks. If nobody thinks that ending up as I showed is possible brings any kind of risk, then I’m happy to accept that. More generally, I’m a huge fan of the principle of least privilege, and (as far as it concerns what I asked about in this thread),

Re: "set role" semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Is there anything that can be done to limit the scope of the ability to end >> up in a database like I'd thought would be possible? (A little test showed >> me that "set role" doesn't fire an event trigger.) >> >> I do

Re: "set role" semantics

On Tue, Nov 8, 2022 at 5:16 PM Bryn Llewellyn wrote: > > Is there anything that can be done to limit the scope of the ability to > end up in a database like I'd thought would be possible? (A little test > showed me that "set role" doesn't fire an event trigger.) > > I do see that, as far as I've

Re: "set role" semantics

On 11/9/22 10:55 AM, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: Revoking PUBLIC has been explained before to you (Bryn Llewellyn). A quick search: https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us

Re: "set role" semantics

On Wed, Nov 9, 2022 at 11:55 AM Bryn Llewellyn wrote: > > Here's an extract from the script that I copied in my first email: > > > > > > > *create database d1;revoke all on database d1 from public;create database > d2;revoke all on database d2 from public;* > > Didn't I do exactly what you both

Re: "set role" semantics

On 11/9/22 10:55 AM, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: Revoking PUBLIC has been explained before to you (Bryn Llewellyn). A quick search: https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us

Re: "set role" semantics

Hi, Le mer. 9 nov. 2022, 19:55, Bryn Llewellyn a écrit : > adrian.kla...@aklaver.com wrote: > > david.g.johns...@gmail.com wrote: > > b...@yugabyte.com wrote: > > Notice that I didn't grant "connect" on either of the databases, "d1" or > "d2", to any of the roles, "clstr$mgr, "d1$mgr", or

Re: copy file from a client app to remote postgres isntance

On 11/9/22 10:17, Peter J. Holzer wrote: On 2022-11-07 14:40:40 -0600, Ron wrote: On 11/7/22 10:57, Вадим Самохин wrote: I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://

Re: "set role" semantics

> adrian.kla...@aklaver.com wrote: > >> david.g.johns...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> Notice that I didn't grant "connect" on either of the databases, "d1" or >>> "d2", to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mgr". >> >> You didn't have to since PUBLIC

Re: Unnecessary locks for partitioned tables

On Thu, 10 Nov 2022 at 04:11, wrote: > If someone would create delayed locking for generic plans, after parameters > are known and partition pruning occurs, I believe generic plan will be on > pars with custom. > So, I`m sticking with plan cache parameter for feature development, that was >

Re: copy file from a client app to remote postgres isntance

On 2022-11-07 14:40:40 -0600, Ron wrote: > On 11/7/22 10:57, Вадим Самохин wrote: > I have an application that must copy a local file in csv format to a > postgres table on a remote host. The closest solution is this one > (https:// > stackoverflow.com/a/9327519/618020). It boils down

Re: copy file from a client app to remote postgres isntance

On 2022-11-07 19:57:04 +0300, Вадим Самохин wrote: > I have an application that must copy a local file in csv format to a postgres > table on a remote host. The closest solution is this one (https:// > stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy > meta-command in a

RE: Unnecessary locks for partitioned tables

> writes: > > Oh, I did not explicitly write that, in case of custom plan (first attempts or with force_custom_plan) database > > holds only a couple of locks! > > Why in this > case it is sufficient to lock only one partition and parent table ? > Because partition routing is done at

Re: Unnecessary locks for partitioned tables

writes: > Oh, I did not explicitly write that, in case of custom plan (first attempts > or with force_custom_plan) database holds only a couple of locks! Why in this > case it is sufficient to lock only one partition and parent table ? Because partition routing is done at planning time in that

RE: Unnecessary locks for partitioned tables

> > On Wed, 2022-11-09 at 14:11 +0300, n.kobza...@aeronavigator.ru wrote: > > Recently I`ve been pushing into life a new project and immediately > > experienced an Out of shared memory error while querying partitioned tables. > > > > ERROR: out of shared memory > > Hint: You might need to

Re: Unnecessary locks for partitioned tables

On Wed, 2022-11-09 at 14:11 +0300, n.kobza...@aeronavigator.ru wrote: > Recently I`ve been pushing into life a new project and immediately > experienced an > Out of shared memory error while querying partitioned tables. >   > ERROR: out of shared memory >   Hint: You might need to increase

Q: documentation improvement re collation version mismatch

Dear all, regarding changed collation versions this https://www.postgresql.org/docs/devel/sql-altercollation.html says: The following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on

Unnecessary locks for partitioned tables

Hello! Recently I`ve been pushing into life a new project and immediately experienced an Out of shared memory error while querying partitioned tables. Imagine a huge busy table that you want to split into hundreds of partitions by list. Partition key is a kind of surrogate key that can be

write an analyze_function for own type

I now have some custom data like: [0x1 0x22 0x365] It has a level attribute, that is, the level of 0x1 is 1, and the level of 0x22 is 2. How should I count the minimum level in a table? I want to use this statistic in GIN index.