Re: Modifying pg_shadow? - Mailing list pgsql-novice

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@lurza.secnetix.de]
> Sent: Tuesday, September 09, 2003 3:20 PM
> To: Jason Hihn
> Cc: Tom Lane; pgsql-novice@postgresql.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
>