template1, can there be a template2/3/4?

Jim Mercer <jim@reptiles.org> writes:

would it be possible for me to create a template2 and have some arbitrary
database use it instead of template1?

In 7.1, you can tell CREATE DATABASE to clone any existing database,
not only template1. However, this doesn't seem to have much to do with
what you are really after.

this way "sample" could have its own set of users, permissions, etc, etc.

Users (also groups) are installation-wide, so there's no way to have
database-specific users. AFAICS, the only thing the alternate-template
facility is good for is to preinstall languages, tables, etc into some
databases and not others.

it would be a method for getting around the fact that a user in template1
has access to all of the other databases, modulo per-database permissions
via GRANT/REVOKE

Which database you are connected to has nothing whatever to do with
whether you can see/manipulate other databases. template1 is certainly
not special in that regard. The only reason createdb and friends
connect to template1 is that it's the only DB name they can be pretty
certain exists.

regards, tom lane

On Mon, Jun 04, 2001 at 12:13:23PM -0400, Tom Lane wrote:

it would be a method for getting around the fact that a user in template1
has access to all of the other databases, modulo per-database permissions
via GRANT/REVOKE

Which database you are connected to has nothing whatever to do with
whether you can see/manipulate other databases. template1 is certainly
not special in that regard. The only reason createdb and friends
connect to template1 is that it's the only DB name they can be pretty
certain exists.

ah, so users/groups are not stored in template1, but in some other series
of physical files.

hmmm. yep, that certainly does squash my idea.

i imagine it would be architectually difficult to have seperate user/group
tables per database.

--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Jim Mercer <jim@reptiles.org> writes:

ah, so users/groups are not stored in template1, but in some other series
of physical files.

pg_shadow/pg_group are installation-wide; they do not belong to any
individual database. This is done via special hacks (cf
IsSharedSystemRelation).

i imagine it would be architectually difficult to have seperate user/group
tables per database.

Codewise it would be trivial --- remove 'em from the list of shared
relations. From the point of view of backwards compatibility, however,
that's not likely to happen.

If you want to restrict users to connect only to their own database,
the 'sameuser' option in pg_hba.conf might be helpful.

regards, tom lane

On Mon, Jun 04, 2001 at 12:29:08PM -0400, Tom Lane wrote:

i imagine it would be architectually difficult to have seperate user/group
tables per database.

Codewise it would be trivial --- remove 'em from the list of shared
relations. From the point of view of backwards compatibility, however,
that's not likely to happen.

If you want to restrict users to connect only to their own database,
the 'sameuser' option in pg_hba.conf might be helpful.

my goal is a bit bigger than that.

at some point in the past, i posted a tweak that allowed one to have a
database authenticated similar to that of /bin/login.

ie. the client passes username/plain-text password, and that is authenticated
against crypto-gunge in the pg_shadow table.
(currently the authentication schemes seem to insist on storing plain-text
passwords in pg_shadow, which i just can't stomach).
(that tweak BTW was done in a completely reverse-compatability way)

with this tweak in place, i can then do PHP scripts which allow the webserver
to store the username/plain-textpass in session variables, and use them with
each call to the pgsql API.

this allows me to use the system catalogs for allowing/denying access to the
tables, without having to fake up some table with SELECT priv for user
"nobody" (or whatever the webserver is running as).

further to this, if i can have seperate pg_user/pg_shadow per database, then
i can have wholly seperate userbases for each database, rather than trying
to manage all my users in a single table.

--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

On Mon, Jun 04, 2001 at 12:29:08PM -0400, Tom Lane wrote:

Jim Mercer <jim@reptiles.org> writes:

i imagine it would be architectually difficult to have seperate user/group
tables per database.

Codewise it would be trivial --- remove 'em from the list of shared
relations. From the point of view of backwards compatibility, however,
that's not likely to happen.

where are these relations? is this a compile-time thing, or can it be done
on-the-fly?

--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Jim Mercer wrote:

as i understand the usage of template1, it holds the system catalogs,
users, etc, etc.

it is accessed as a quasi-shadow database supporting the actual production
data databases.

would it be possible for me to create a template2 and have some arbitrary
database use it instead of template1?

the reason i ask this is that it would be useful to have something to the
effect of:

database access startup for "sample" database.

if exists sample_cat database, use it instead of template1
otherwise use template1

this way "sample" could have its own set of users, permissions, etc, etc.

it would be a method for getting around the fact that a user in template1
has access to all of the other databases, modulo per-database permissions
via GRANT/REVOKE

does this make any sense?

There is no such concept like a shadow database. I think
you've misunderstood something.

Except for a few shared relations, namely pg_database,
pg_shadow, pg_group and pg_log, every database has it's own
copy of the system catalog. The entire content (catalog and
so far created objects) is *copied* at createdb time from
whatever you specify as the template database. If you modify
the template database after, these changes don't make it into
the databases derived from it.

The shared catalogs are shared in the entire instance. So if
you want different sets of users, you need to run separate
postmasters for the different sets of databases.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Jim Mercer <jim@reptiles.org> writes:

where are these relations? is this a compile-time thing, or can it be done
on-the-fly?

Compile-time; see SharedSystemRelationNames in
backend/utils/init/globals.c. You'd have to do an initdb after changing
it, anyway.

regards, tom lane

I wrote:

Jim Mercer <jim@reptiles.org> writes:

where are these relations? is this a compile-time thing, or can it be done
on-the-fly?

Compile-time; see SharedSystemRelationNames in
backend/utils/init/globals.c. You'd have to do an initdb after changing
it, anyway.

BTW, the reason that pg_shadow is installation-wide is that it's
not real clear what the 'ownership' column in pg_database means
if users are not installation-wide. Before cutting and hacking,
you'd need to think carefully about just what semantics you are
really after.

regards, tom lane

On Mon, Jun 04, 2001 at 02:16:35PM -0400, Tom Lane wrote:

I wrote:

Jim Mercer <jim@reptiles.org> writes:

where are these relations? is this a compile-time thing, or can it be done
on-the-fly?

Compile-time; see SharedSystemRelationNames in
backend/utils/init/globals.c. You'd have to do an initdb after changing
it, anyway.

BTW, the reason that pg_shadow is installation-wide is that it's
not real clear what the 'ownership' column in pg_database means
if users are not installation-wide. Before cutting and hacking,
you'd need to think carefully about just what semantics you are
really after.

theoretically, if i nuked everything from SharedSystemRelationNames, then
each database would be wholly stand-alone, and the remaining code should just
work?

--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Jim Mercer <jim@reptiles.org> writes:

theoretically, if i nuked everything from SharedSystemRelationNames, then
each database would be wholly stand-alone, and the remaining code should just
work?

Hmm. I do not know what would happen if pg_database were made
database-local, but I doubt it would be anything good ...

regards, tom lane

On Mon, Jun 04, 2001 at 11:16:40AM -0400, Jim Mercer wrote:

would it be possible for me to create a template2 and have some arbitrary
database use it instead of template1?

the reason i ask this is that it would be useful to have something to the
effect of:

database access startup for "sample" database.

if exists sample_cat database, use it instead of template1
otherwise use template1

this way "sample" could have its own set of users, permissions, etc, etc.

it would be a method for getting around the fact that a user in template1
has access to all of the other databases, modulo per-database permissions
via GRANT/REVOKE

does this make any sense?

as has already been discussed, template1 is basically the
default template to copy when creating a new database.

as for which relations are system-wide (versus
database-specific) check for files in
/var/lib/postgres/data/pg_*:

pg_control
pg_database
pg_geqo
pg_group
pg_group_name_index
pg_group_sysid_index
pg_hba.conf
pg_ident.conf
pg_log
pg_pwd
pg_pwd.reload
pg_shadow
pg_variable

(note that some of those are conf files, not db relations...)

all the other 'system' tables ARE database-specific
and thus reside in their respective subdirectories at
/var/lib/postgres/data/base/<databasename>/pg_*

--
#95: We are waking up and linking to each other. We are watching. But
we are not waiting. -- www.cluetrain.com

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!