Re: Modifying pg_shadow?

I wish to track some additional info about users. Can I just add columns to
the pg_shadow table? Can I add a system table while I am at it? (how?)

How do I back up the pg_ system tables?

Thanks

Jason Hihn
Paytime Payroll

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).

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

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

regards, tom lane

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

Jason Hihn wrote:
> Tom Lane wrote:
>
> > > 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.

I think "pg_dumpall -g" is what you want. That _is_ in the
online docs.

However, I agree that restoring a complete PG cluster from
scratch seems to be somewhat difficult. You still have to
do a lot of things manually in order to get everything right
without missing anything. At least that's my impression.

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

I think you might want to look at the relacl column of the
pg_class table.

However, in your case, it might be beneficial to store the
data about users in your own database, in a format which is
suitable for your use. You can then generate grant/revoke
commands from that if necessary. It would also be a lot
more portable than depending on the internal structure of
PG system tables.

Just my 0.02 Euro.

Regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"One of the main causes of the fall of the Roman Empire was that,
lacking zero, they had no way to indicate successful termination
of their C programs."
-- Robert Firth

> -----Original Message-----
> From: Oliver Fromme [mailto:olli(at)lurza(dot)secnetix(dot)de]
> Sent: Tuesday, September 09, 2003 9:37 AM
> To: Jason Hihn
> Cc: Tom Lane; pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Modifying pg_shadow?
>
>
>
> Jason Hihn wrote:
> > Tom Lane wrote:
> >
> > > > 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.
>
> I think "pg_dumpall -g" is what you want. That _is_ in the
> online docs.

> However, I agree that restoring a complete PG cluster from
> scratch seems to be somewhat difficult. You still have to
> do a lot of things manually in order to get everything right
> without missing anything. At least that's my impression.
>
"Chapter 9. Backup and Restore":
pg_dump dbname > outfile

What's the dbname for the system tables? The -g option of pg_dumpall only
does users and groups. No other tables. (Eek!)

>
> I think you might want to look at the relacl column of the
> pg_class table.

Ah, wonderful. This is what I was looking for. Though in the past I've used
databases where I wouldn't have to parse this text. It was quite easy and
fun to work with as tuple data. *wink*

Ok, so I have a question If I have 2 databases, a and b, and they both have
a table, c, how do I grant permissions only to table a.t and not both tables
in both databases at the same time? The intituve answer is not correct -
that 'ON a.t ...' does not work.

> However, in your case, it might be beneficial to store the
> data about users in your own database, in a format which is
> suitable for your use. You can then generate grant/revoke
> commands from that if necessary. It would also be a lot
> more portable than depending on the internal structure of
> PG system tables.

I really don't want to have to re-invent the wheel here.

Thank you for your help, I'm headed in the right direction now.

Jason Hihn wrote:
>
>
> > -----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.

We do use PostgreSQL to process itself. There is the bootstrap problem
of how to start things.

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

Just put the table in template1 and it will be added to every database
you create.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

Jason Hihn <jhihn(at)paytimepayroll(dot)com> writes:
> Ok, so I have a question If I have 2 databases, a and b, and they both have
> a table, c, how do I grant permissions only to table a.t and not both tables
> in both databases at the same time? The intituve answer is not correct -
> that 'ON a.t ...' does not work.

Perhaps you'd better show us exactly what you tried, because the above
doesn't have any connection to my view of reality. I don't see any way
that a single grant command could affect two databases.

regards, tom lane

Jason Hihn wrote:
> "Chapter 9. Backup and Restore":
> pg_dump dbname > outfile

That'll backup a complete database.

> What's the dbname for the system tables?

There is none. The system tables are always visible, no
matter which DB you're connected to. That's why they are
system tables ...

> The -g option of pg_dumpall only
> does users and groups. No other tables. (Eek!)

What other information do you need to be dumped? Users and
groups are the _only_ global (i.e. cluster-wide) objects,
as far as I have learned. Everything else is related to a
specific database, so it will be dumped along with that
database when you use pg_dump.

> Ah, wonderful. This is what I was looking for. Though in the past I've used
> databases where I wouldn't have to parse this text. It was quite easy and
> fun to work with as tuple data. *wink*

In my opinion it shouldn't be visible at all, because it
encourages all kinds of abuse ...

> Ok, so I have a question If I have 2 databases, a and b, and they both have
> a table, c, how do I grant permissions only to table a.t and not both tables
> in both databases at the same time? The intituve answer is not correct -
> that 'ON a.t ...' does not work.

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. I learned that a few days ago, thanks
to Tom Lane. :-)

> > However, in your case, it might be beneficial to store the
> > data about users in your own database, in a format which is
> > suitable for your use. You can then generate grant/revoke
> > commands from that if necessary. It would also be a lot
> > more portable than depending on the internal structure of
> > PG system tables.
>
> I really don't want to have to re-invent the wheel here.

Well, if you prefer to use ready-made wheels which are
square instead of round ... ;-)

Regards
Oliver

PS: I'm still a novice, too, so if I talk nonsense, please
someone correct me. :-)

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"A language that doesn't have everything is actually easier
to program in than some that do."
-- Dennis M. Ritchie

> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Oliver Fromme
> Sent: Tuesday, September 09, 2003 11:55 AM
> To: Jason Hihn
> Cc: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Modifying pg_shadow?
>
>
>
> Jason Hihn wrote:
> > "Chapter 9. Backup and Restore":
> > pg_dump dbname > outfile
>
> That'll backup a complete database.
>
> > What's the dbname for the system tables?
>
> There is none. The system tables are always visible, no
> matter which DB you're connected to. That's why they are
> system tables ...
>
> > The -g option of pg_dumpall only
> > does users and groups. No other tables. (Eek!)
>
> What other information do you need to be dumped? Users and
> groups are the _only_ global (i.e. cluster-wide) objects,
> as far as I have learned. Everything else is related to a
> specific database, so it will be dumped along with that
> database when you use pg_dump.
>

OOOh.

> > Ah, wonderful. This is what I was looking for. Though in the
> past I've used
> > databases where I wouldn't have to parse this text. It was
> quite easy and
> > fun to work with as tuple data. *wink*
>
> In my opinion it shouldn't be visible at all, because it
> encourages all kinds of abuse ...

Abuse? I guess you could find out who as access to what and limit your
pasword guessing t a few accounts, but even then it's just a matter of time.

> > Ok, so I have a question If I have 2 databases, a and b, and
> they both have
> > a table, c, how do I grant permissions only to table a.t and
> not both tables
> > in both databases at the same time? The intituve answer is not
> correct -
> > that 'ON a.t ...' does not work.
>
> 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. I learned that a few days ago, thanks
> to Tom Lane. :-)

That would be a worth while addition to the docs - that it effects only the
currently connected database.

> > > However, in your case, it might be beneficial to store the
> > > data about users in your own database, in a format which is
> > > suitable for your use. You can then generate grant/revoke
> > > commands from that if necessary. It would also be a lot
> > > more portable than depending on the internal structure of
> > > PG system tables.
> >
> > I really don't want to have to re-invent the wheel here.
>
> Well, if you prefer to use ready-made wheels which are
> square instead of round ... ;-)

Well, I'd rather use your wheel and knock off a few corners...

Thanks to everyone - I think all my questions for now are solved!

Oliver Fromme <olli(at)lurza(dot)secnetix(dot)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

Wow. I learned a lot just now... You have me interested in these 'schemas' I
know they are new for 7.3, so where can I find more info on them? (I read
Section 2.8)

It now looks like I can't do what I intended and I'll have to create my own
master permission list table, in addition to posture's. But I'll ask it - is
there a way to get all pg_class info for all tables in all databases
(schemas)?

Reading the docs (2.8), this is what I gather:
--Setup:
1) create a database (D), fill with objects
2) create a schema (S)(inherits current database's objects)
--Apps:
1) Connect to database D
2) SET search_path TO S, public; (for not having to scope all sql to this
schema)
3) use the schema as if it were a separate database i.e. (select
S.tablename -> select tablename, because of my previous step)

Now, what are the implications for:
backups - If I do a pg_dump, it'll dump all schemas? Can I dump just one?
creating tables in the schema - they stay in that schema only?
modifying the database from which the schema was created - modifies all
schemas descended from that database?
create table with my step #2 above - does it go in the schema or the
database?

I think that does it for now...

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, September 09, 2003 12:24 PM
> To: Oliver Fromme
> Cc: Jason Hihn; pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Modifying pg_shadow?
>
>
> Oliver Fromme <olli(at)lurza(dot)secnetix(dot)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
>

Jason Hihn wrote:
> Wow. I learned a lot just now... You have me interested in these 'schemas' I
> know they are new for 7.3, so where can I find more info on them? (I read
> Section 2.8)

I think the docs contain all necessary information. You
should also have a look at the description of CREATE SCHEMA
in the SQL Commands section of the Reference Manual.

> It now looks like I can't do what I intended and I'll have to create my own
> master permission list table, in addition to posture's. But I'll ask it - is
> there a way to get all pg_class info for all tables in all databases
> (schemas)?

Not easily. You'll have to connect to all databases in
turn. For example, a little shell script like this will
do it (caution, this is from the top of my head, not
tested):

DBSEL="SELECT datname FROM pg_database WHERE datname != 'template0'"
psql -q -t -A -d template1 -c "$DBSEL" \
| while read DATNAME; do
psql ... -d $DATNAME -c "select * from pg_class"
done

Alternatively, the shell script could build a script for
psql using the \c meta-command to change databases, so
psql doesn't have to be exec'ed a hundred times if you
have a hundred databases ...

> Reading the docs (2.8), this is what I gather:
> --Setup:
> 1) create a database (D), fill with objects
> 2) create a schema (S)(inherits current database's objects)

No. The objects already belong to the "public" schema.
When you create a new schema, it won't inherit them.
It will be empty.

You should create the schema first, then create the
objects inside that schema.

Remember that schemas are namespaces. Think of it like
directories in a filesystem, as an analogy. The database
would be the filesystem, the schemas are directories (only
one level of them, though, as in MS-DOS 1.0), and the
tables are files in that directory.

> --Apps:
> 1) Connect to database D
> 2) SET search_path TO S, public; (for not having to scope all sql to this
> schema)
> 3) use the schema as if it were a separate database i.e. (select
> S.tablename -> select tablename, because of my previous step)

Right. You have to be careful with permissions, though.
You can configure HBA access based on databases, but not
based on schemas. You need to use GRANT / REVOKE.

> Now, what are the implications for:
> backups - If I do a pg_dump, it'll dump all schemas?

Yes.

> Can I dump just one?

You can dump all tables that are contained in one schema.

> creating tables in the schema - they stay in that schema only?

Yes. Remember, a schema is just a namespace.

> modifying the database from which the schema was created - modifies all
> schemas descended from that database?

What exactly do you mean? What modification?

> create table with my step #2 above - does it go in the schema or the
> database?

It will always go into a schema, either your self-defined
one, or the the "public" schema, depending on your search
path (if you don't specify the schema explicitely). The
function current_schema() will tell you in which one a
newly created table will go.

Regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"... there are two ways of constructing a software design: One way
is to make it so simple that there are _obviously_ no deficiencies and
the other way is to make it so complicated that there are no _obvious_
deficiencies." -- C.A.R. Hoare, ACM Turing Award Lecture, 1980

AAAh. I'll have to write a PostgreSQL Schema for Dummies page. ;-)

Correct me if I'm wrong:
I can create payroll.accountname.* (d.s.t), but the login security can only
auth to database level. Meaning my schema security must fall upon
grant/revoke.

I could then create payroll.tax (d.s), and set the search_path=account,tax
and share the same tax tables between all accounts.

Any database table (database.table) is in the public schema, with pg_*
ALWAYS avaible regardless of search_path. (so I could also do
payroll.taxtable{1,2,3} and still share but with search_path=account,public

How is the pg_dump done when only done for a schema?
"pg_dump d.s"?

Looks like I have to u/g from 7.2 to 7.3 sooner than I thought. Any word on
7.4? (Yeah, I know, "when its ready" but how ready does it look?)

Thanks a bunch, again.

> -----Original Message-----
> From: Oliver Fromme [mailto:olli(at)lurza(dot)secnetix(dot)de]
> Sent: Tuesday, September 09, 2003 3:20 PM
> To: Jason Hihn
> Cc: Tom Lane; pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Modifying pg_shadow?
>
>
>
> Jason Hihn wrote:
> > Wow. I learned a lot just now... You have me interested in
> these 'schemas' I
> > know they are new for 7.3, so where can I find more info on
> them? (I read
> > Section 2.8)
>
> I think the docs contain all necessary information. You
> should also have a look at the description of CREATE SCHEMA
> in the SQL Commands section of the Reference Manual.
>
> > It now looks like I can't do what I intended and I'll have to
> create my own
> > master permission list table, in addition to posture's. But
> I'll ask it - is
> > there a way to get all pg_class info for all tables in all databases
> > (schemas)?
>
> Not easily. You'll have to connect to all databases in
> turn. For example, a little shell script like this will
> do it (caution, this is from the top of my head, not
> tested):
>
> DBSEL="SELECT datname FROM pg_database WHERE datname != 'template0'"
> psql -q -t -A -d template1 -c "$DBSEL" \
> | while read DATNAME; do
> psql ... -d $DATNAME -c "select * from pg_class"
> done
>
> Alternatively, the shell script could build a script for
> psql using the \c meta-command to change databases, so
> psql doesn't have to be exec'ed a hundred times if you
> have a hundred databases ...
>
> > Reading the docs (2.8), this is what I gather:
> > --Setup:
> > 1) create a database (D), fill with objects
> > 2) create a schema (S)(inherits current database's objects)
>
> No. The objects already belong to the "public" schema.
> When you create a new schema, it won't inherit them.
> It will be empty.
>
> You should create the schema first, then create the
> objects inside that schema.
>
> Remember that schemas are namespaces. Think of it like
> directories in a filesystem, as an analogy. The database
> would be the filesystem, the schemas are directories (only
> one level of them, though, as in MS-DOS 1.0), and the
> tables are files in that directory.
>
> > --Apps:
> > 1) Connect to database D
> > 2) SET search_path TO S, public; (for not having to scope all
> sql to this
> > schema)
> > 3) use the schema as if it were a separate database i.e. (select
> > S.tablename -> select tablename, because of my previous step)
>
> Right. You have to be careful with permissions, though.
> You can configure HBA access based on databases, but not
> based on schemas. You need to use GRANT / REVOKE.
>
> > Now, what are the implications for:
> > backups - If I do a pg_dump, it'll dump all schemas?
>
> Yes.
>
> > Can I dump just one?
>
> You can dump all tables that are contained in one schema.
>
> > creating tables in the schema - they stay in that schema only?
>
> Yes. Remember, a schema is just a namespace.
>
> > modifying the database from which the schema was created - modifies all
> > schemas descended from that database?
>
> What exactly do you mean? What modification?
>
> > create table with my step #2 above - does it go in the schema or the
> > database?
>
> It will always go into a schema, either your self-defined
> one, or the the "public" schema, depending on your search
> path (if you don't specify the schema explicitely). The
> function current_schema() will tell you in which one a
> newly created table will go.
>
> Regards
> Oliver
>
> --
> Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
> Any opinions expressed in this message may be personal to the author
> and may not necessarily reflect the opinions of secnetix in any way.
>
> "... there are two ways of constructing a software design: One way
> is to make it so simple that there are _obviously_ no deficiencies and
> the other way is to make it so complicated that there are no _obvious_
> deficiencies." -- C.A.R. Hoare, ACM Turing Award Lecture, 1980
>

Jason Hihn wrote:
> Correct me if I'm wrong:
> I can create payroll.accountname.* (d.s.t), but the login security can only
> auth to database level. Meaning my schema security must fall upon
> grant/revoke.

Right. The default for a schema is that only the owner can
use it. That might be already what you want.

> I could then create payroll.tax (d.s), and set the search_path=account,tax
> and share the same tax tables between all accounts.

I'm not sure if I understand what you mean.

By default, only the owner of the tax schema will be able
to see the tables inside. To grant everyone access who can
connect to the payroll database, you would do something
like GRANT USAGE ON SCHEMA tax TO PUBLIC. See the docs
on the GRANT command for details.

> Any database table (database.table) is in the public schema,

Actually database.public.table. There's nothing magic about
the "public" schema. It's just the default. You can even
drop it and work entirely with your own schemas.

> with pg_*
> ALWAYS avaible regardless of search_path. (so I could also do
> payroll.taxtable{1,2,3} and still share but with search_path=account,public

The pg_* system tables are contained in a system schema
called pg_catalog. It's explained in the docs.

The docs really aren't that bad. You just have to read
them. Repeatedly, if necessary. ;-)

> How is the pg_dump done when only done for a schema?
> "pg_dump d.s"?

No, you have to name the tables of that schema, using the
-t option.

> Looks like I have to u/g from 7.2 to 7.3 sooner than I thought. Any word on
> 7.4? (Yeah, I know, "when its ready" but how ready does it look?)

I leave that answer to the experts. :-)

Regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"When your hammer is C++, everything begins to look like a thumb."
-- Steve Haflich, in comp.lang.c++