Re: Modifying pg_shadow?

> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: Monday, September 08, 2003 4:48 PM
> To: Jason Hihn
> Cc: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Modifying pg_shadow?
>
>
> Jason Hihn <jhihn(at)paytimepayroll(dot)com> writes:
> > I wish to track some additional info about users. Can I just
> add columns to
> > the pg_shadow table?
>
> Not without modifying the C code that manipulates pg_shadow (at the very
> least, some routines in src/backend/commands/user.c would have to
> change, and you'd need to update src/include/catalog/pg_shadow.h).

If I could write code to handle tables with extra columns, can't the
back-end do it too? It would be poetic to have the backend process itself
with itself. (It appears you hard code these tables?) Not to mention
extremely flexible.

> > Can I add a system table while I am at it? (how?)
>
> What's your idea of a "system table"? Mine is one that some C code in
> the backend knows about explicitly. Unless you've written some C code
> that accesses a table, it's not a system table. The infrastructure for
> doing this is at least a header in include/catalog/, usually more
> depending on whether you need things like cache support for the new
> table. You might care to look at all the code referencing one of the
> lesser-used catalogs, perhaps pg_language or pg_cast, to get a sense of
> what is involved.

A 'system table' to me is one provided by Postgres. It is the set of tables
that exist with no user databases and user tables.

> > How do I back up the pg_ system tables?
>
> They aren't backed up as such; all the useful content is included in the
> schema information output by pg_dump or pg_dumpall.

What database name should pg_dump be given? This is a horrid omission from
the online docs! Furthermore, there is also no system database listed in
pg_database.

What I am trying to do, is I need Postgres to handle a thousand users and
several hundred databases. I need somewhere to store what type the user is
(our employee or a client's employee), along with a permission list for that
user - what database(s) that person can access. I have not yet found
something like a pg_grant table to tell me that. There must be one. What is
it?