public inbox for [email protected]help / color / mirror / Atom feed
Re: Fwd: Identify system databases 6+ messages / 4 participants [nested] [flat]
* Re: Fwd: Identify system databases @ 2025-04-16 12:30 Laurenz Albe <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Laurenz Albe @ 2025-04-16 12:30 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Adrian Klaver <[email protected]>; David G. Johnston <[email protected]>; Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote: > On Wed, Apr 16, 2025 at 9:32 AM Laurenz Albe <[email protected]> wrote: > > On Tue, 2025-04-15 at 17:24 -0700, Adrian Klaver wrote: > > But then you fortunately cannot drop all databases, because you cannot > > drop the database you are connected to. > > > > Still, a cluster that is missing "postgres" will give beginners trouble > > Right. Given shared "cluster-wide" objects like roles, databases, etc... > I'm always found it weird that one must know "a priori" the name of one > database to connect to, to do anything with PostgreSQL. It is documented, and it is the default database if you are connecting with the default database user "postgres", so it is not that bad. Also, tools like "createdb" connect to that database by default. > Not being able to drop the DB one's connected to is also a pita. > If one have the right to do it, then it should just be done, and the > connection closed. No, it is a life saver, at least in my opinion. > Authentication is cluster-wide, not DB specific, so I'd welcome a way to connect > to the cluster, not a specific DB, and introspect shared-objects, > including databases > I'm allowed to connect to, which could be an empty list. It is deep in the DNA of PostgreSQL that you always have to connect to a database, unless you establish a replication connection. I am surprised that you perceive that as a problem or limitation. > FWIW. And a little OT. And implicit cluster-level mini-DB given access > just to a subset of catalogs, or at least some limited queries like > my databases and my roles, nothing else, would be very welcome IMHO. --DD Well, that's the "postgres" database. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Identify system databases @ 2025-04-16 14:39 Tom Lane <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2025-04-16 14:39 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Adrian Klaver <[email protected]>; David G. Johnston <[email protected]>; Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Laurenz Albe <[email protected]> writes: > On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote: >> Authentication is cluster-wide, not DB specific, so I'd welcome a way to connect >> to the cluster, not a specific DB, and introspect shared-objects, >> including databases >> I'm allowed to connect to, which could be an empty list. > It is deep in the DNA of PostgreSQL that you always have to connect to > a database, unless you establish a replication connection. > I am surprised that you perceive that as a problem or limitation. That isn't going to change, and here's why not: a lot of the critical catalogs are per-database not shared. You aren't going to get anywhere "introspecting shared objects" when you don't have a copy of pg_class with which to find the shared catalogs, nor a copy of pg_proc with which to look up index access method support procedures, etc etc. You could imagine making up some mini-database that is somehow forbidden from gaining any user-defined objects and then using that, but I fail to see why that's a better idea than the approach we use now. Not being able to use any user-defined functions or views seems like a pretty huge handicap. And this hypothetical new mini-database *would* be a special snowflake in a way that none of the existing ones are, in that the system would have to prevent actions that are perfectly okay in any other one. I don't perceive that as a good thing. (You can, of course, speculate about some major rearchitecting of the system catalogs that would make this situation different. I doubt that's going to happen at this point, though. There's too much stuff that's dependent on how things are now.) regards, tom lane ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Identify system databases @ 2025-04-16 15:06 Dominique Devienne <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Dominique Devienne @ 2025-04-16 15:06 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Adrian Klaver <[email protected]>; David G. Johnston <[email protected]>; Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Wed, Apr 16, 2025 at 4:39 PM Tom Lane <[email protected]> wrote: > Laurenz Albe <[email protected]> writes: > > On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote: So in a way, you guys are saying one should never REVOKE CONNECT ON DATABASE FROM PUBLIC? All my DBs are not PUBLIC-accessible. And inside my DBs, I try to revoke everything from PUBLIC (USAGE ON TYPES, EXECUTE ON ROUTINES). Nor do I use the public schema. And I never use the "built-in" postgres database. Basically I want all GRANTs to be explicit. Given the above, I'd want to not provide access to the postgres DB too. Yet have a way to discover which DBs I can connect to, from the "cluster only". Naively. Sounds like you are saying use the "postgres" DB for that, and move on. --DD D:\>ppg -c acme -d postgres database_ --acls Connected OK (postgresql://ddevienne@acme/postgres); with SSL |----------|----------|-----------|-----------| | Grantor | Grantee | Privilege | Grantable | |----------|----------|-----------|-----------| | postgres | PUBLIC | TEMPORARY | NO | | postgres | PUBLIC | CONNECT | NO | | postgres | postgres | CREATE | NO | | postgres | postgres | TEMPORARY | NO | | postgres | postgres | CONNECT | NO | |----------|----------|-----------|-----------| 5 ACLs to 2 Grantees from 1 Grantor ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Identify system databases @ 2025-04-16 15:25 David G. Johnston <[email protected]> parent: Dominique Devienne <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: David G. Johnston @ 2025-04-16 15:25 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; Adrian Klaver <[email protected]>; Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Wed, Apr 16, 2025 at 8:07 AM Dominique Devienne <[email protected]> wrote: > On Wed, Apr 16, 2025 at 4:39 PM Tom Lane <[email protected]> wrote: > > Laurenz Albe <[email protected]> writes: > > > On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote: > > So in a way, you guys are saying one should never REVOKE CONNECT ON > DATABASE FROM PUBLIC? > > All my DBs are not PUBLIC-accessible. > And inside my DBs, I try to revoke everything from PUBLIC > (USAGE ON TYPES, EXECUTE ON ROUTINES). > Nor do I use the public schema. > And I never use the "built-in" postgres database. > Basically I want all GRANTs to be explicit. > > Given the above, I'd want to not provide access to the postgres DB too. > > Yet have a way to discover which DBs I can connect to, from the "cluster > only". > Kinda surprised you don't consider this a feature...give all of your databases UUID names and ensure that non-superusers must be told the databases they are allowed to connect to. But feel free to work out a design and add it to the ToDo list for the v4 protocol. The use case seems reasonable and doable (on the basis of the replication protocol works). https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes_.2F_v4_Protocol David J. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Identify system databases @ 2025-04-16 16:08 Tom Lane <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2025-04-16 16:08 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Laurenz Albe <[email protected]>; Adrian Klaver <[email protected]>; Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> "David G. Johnston" <[email protected]> writes: > But feel free to work out a design and add it to the ToDo list for the v4 > protocol. The use case seems reasonable and doable (on the basis of the > replication protocol works). No, the replication protocol isn't a precedent. Physical replication needn't connect to a particular database because it does no catalog accesses (and hence can't run SQL). All it's able to do is suck out the WAL stream. Logical replication can do SQL --- but it has to connect to a specific database. regards, tom lane ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Identify system databases @ 2025-04-16 20:04 Laurenz Albe <[email protected]> parent: Dominique Devienne <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Laurenz Albe @ 2025-04-16 20:04 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; David G. Johnston <[email protected]>; Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Wed, 2025-04-16 at 17:06 +0200, Dominique Devienne wrote: > So in a way, you guys are saying one should never REVOKE CONNECT ON > DATABASE FROM PUBLIC? No, not at all. > All my DBs are not PUBLIC-accessible. > And inside my DBs, I try to revoke everything from PUBLIC > (USAGE ON TYPES, EXECUTE ON ROUTINES). > Nor do I use the public schema. > And I never use the "built-in" postgres database. > Basically I want all GRANTs to be explicit. > > Given the above, I'd want to not provide access to the postgres DB too. > Yet have a way to discover which DBs I can connect to, from the "cluster only". > Naively. > > Sounds like you are saying use the "postgres" DB for that, and move on. It is just fine to restrict access to databases as much as you want. You just need access to a database if you want to run SQL statements, be that SELECT or CREATE DATABASE. It's as simple as that. Restricting access is not wrong per se, but if you randomly restrict access to everything, that's not so much a security measure as a way to make the database unusable. I see no value in restricting USAGE on the data type "text" or EXECUTE on the function "upper()". Modifying system objects is not recommended and can lead to trouble. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-04-16 20:04 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-16 12:30 Re: Fwd: Identify system databases Laurenz Albe <[email protected]> 2025-04-16 14:39 ` Tom Lane <[email protected]> 2025-04-16 15:06 ` Dominique Devienne <[email protected]> 2025-04-16 15:25 ` David G. Johnston <[email protected]> 2025-04-16 16:08 ` Tom Lane <[email protected]> 2025-04-16 20:04 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox