Re: Modifying pg_shadow?

Jason Hihn <jhihn(at)paytimepayroll(dot)com> writes:
>> 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.

There's a bootstrapping problem involved; how are you going to process
the tables that tell you what tables contain? I don't see any
reasonable way that the core catalogs (pg_class, pg_attribute, probably
pg_type and pg_proc) could be handled without hard-coding knowledge of
their contents. Non-core catalogs could perhaps be handled using
different methods, but it's easier to use the same coding style
throughout the backend.

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

For "database" you most likely want to think "schema", instead. Then
you just grant or revoke access as needed. You might want to assign
users to groups rather than having to manage access rights individually.

regards, tom lane