Re: Modifying pg_shadow? - Mailing list pgsql-novice

Oliver Fromme <olli@lurza.secnetix.de> writes:
> There is none.  The system tables are always visible, no
> matter which DB you're connected to.  That's why they are
> system tables ...

> You're always connected to one database.  A GRANT command
> will affect only that database, nothing else.  Even when
> you issue GRANT on system tables (which are visible in
> every database), the change will only affect the database
> you're connected to.

To enlarge on that a little: for the most part, each database has its
own copy of the system catalogs (created when CREATE DATABASE clones
the contents of template1).  This is why when you create a table in
one database, it's visible in pg_class in that database but not in other
databases.  CREATE TABLE only affects the local copy of pg_class.

The exception to this is the "shared" system catalogs pg_database,
pg_shadow, pg_group.  There is only one cluster-wide copy of these
tables (and their indexes).  That's why you can find out what other
databases exist in the cluster, and why you can create users and
groups that are valid across the cluster and not just in one database.

If you try to do something like GRANT or REVOKE on a system catalog,
you are modifying the local copy of pg_class, and so the effects
are only visible in your current database.  This is true even if the
catalog in question is one of the shared catalogs --- the *contents*
of the shared catalogs are shared, but the metadata about them is
not.

The reason for "pg_dumpall -g" to exist is precisely that users and
groups are cluster-wide.  Everything else (including the pg_database
attributes of a particular database) is dumped by pg_dump acting on
individual databases.  But it would not be useful for each such pg_dump
run to dump CREATE USER/GROUP commands.  So pg_dumpall dumps those
separately, then invokes pg_dump successively on each database.

BTW: the separate-databases mechanism is invaluable for experimental or
development work --- no matter how badly you screw up the contents of
pg_class or pg_proc, you can only corrupt the database you are working
in, and the rest of the cluster can sail along just fine.  But for most
production scenarios, it's probably overkill; do you really need to copy
all the system catalogs for each user?  I'd recommend looking at using
multiple schemas within a single database, instead.  Schemas are much
lighter-weight than databases.  They also allow controlled sharing of
information, whereas in a multiple-databases installation there is no
convenient way to access data from different databases.

            regards, tom lane