Thread: Re: Can't import large objects in most recent cvs (2002

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent: Friday, May 31, 2002 3:24 PM
> To: Ron Snyder
> Cc: pgsql-hackers
> Subject: Re: [HACKERS] Can't import large objects in most 
> recent cvs (20020531 -- approx 1pm PDT) 
> 
> 
> Ron Snyder <snyder@roguewave.com> writes:
> > I attempt to restore from a 7.2.1 created dump into my newly created
> > 7.3devel database, I get this:
> 
> > pg_restore: [archiver (db)] could not create large object 
> cross-reference
> > table:
> 
> > I didn't find any mention of this on the hackers mail 
> archive, so I thought
> > I'd pass it on.
> 
> News to me; and I just tested that code a couple days ago 
> after hacking
> on it for schema support.  Would you look in the postmaster log to see
> exactly what error message the backend is issuing?  Might help to run
> pg_restore with "PGOPTIONS=--debug_print_query=1" so you can 
> verify the
> exact query that's failing, too.

From the client:
COPY "unique_names" WITH OIDS FROM stdin;
LOG:  query: select getdatabaseencoding()
pg_restore: LOG:  query: Create Temporary Table pg_dump_blob_xref(oldOid
pg_catalog.oid, newOid pg_catalog.oid);
pg_restore: [archiver (db)] could not create large object cross-reference
table:

From the server:
May 31 15:58:15 vault pgcvs[366]: [5-5] -- Name: unique_names Type: TABLE
DATA Schema: - Owner: qvowner
May 31 15:58:15 vault pgcvs[366]: [5-6] -- Data Pos: 30713831 (Length 1214)
May 31 15:58:15 vault pgcvs[366]: [5-7] --
May 31 15:58:15 vault pgcvs[366]: [5-8] COPY "unique_names" WITH OIDS FROM
stdin;
May 31 15:58:15 vault pgcvs[367]: [1] LOG:  connection received:
host=[local]
May 31 15:58:15 vault pgcvs[367]: [2] LOG:  connection authorized:
user=qvowner database=quickview
May 31 15:58:15 vault pgcvs[367]: [3] LOG:  query: select
getdatabaseencoding()
May 31 15:58:15 vault pgcvs[367]: [4] LOG:  query: Create Temporary Table
pg_dump_blob_xref(oldOid pg_catalog.oid, newOid pg_catalog.oid);

(and then a later run with a higher debug level)
May 31 16:11:50 vault pgcvs[2135]: [77] LOG:  connection received:
host=[local]
May 31 16:11:50 vault pgcvs[2135]: [78] LOG:  connection authorized:
user=qvowner database=quickview
May 31 16:11:50 vault pgcvs[2135]: [79] DEBUG:
/usr/local/pgsql-20020531/bin/postmaster child[2135]: starting with (
May 31 16:11:50 vault pgcvs[2135]: [80] DEBUG:  ^Ipostgres
May 31 16:11:50 vault pgcvs[2135]: [81] DEBUG:  ^I-v131072
May 31 16:11:50 vault pgcvs[2135]: [82] DEBUG:  ^I-p
May 31 16:11:50 vault pgcvs[2135]: [83] DEBUG:  ^Iquickview
May 31 16:11:50 vault pgcvs[2135]: [84] DEBUG:  )
May 31 16:11:50 vault pgcvs[2135]: [85] DEBUG:  InitPostgres
May 31 16:11:50 vault pgcvs[2135]: [86] DEBUG:  StartTransactionCommand
May 31 16:11:50 vault pgcvs[2135]: [87] LOG:  query: select
getdatabaseencoding()
May 31 16:11:50 vault pgcvs[2135]: [88] DEBUG:  ProcessQuery
May 31 16:11:50 vault pgcvs[2135]: [89] DEBUG:  CommitTransactionCommand
May 31 16:11:50 vault pgcvs[2135]: [90] DEBUG:  StartTransactionCommand
May 31 16:11:50 vault pgcvs[2135]: [91] LOG:  query: Create Temporary Table
pg_dump_blob_xref(oldOid pg_catalog.oid, newOid pg_catalog.oid);
May 31 16:11:50 vault pgcvs[2135]: [92] DEBUG:  ProcessUtility
May 31 16:11:50 vault pgcvs[2135]: [93] ERROR:  quickview: not authorized to
create temp tables

Digging a bit, I've discovered this:
1) usesysid 1 owns the database in the old server, but all the tables are
owned by 'qvowner' (and others).
2) qvowner does not have dba privs

My theory is that I'm getting this last message (not authorized to create
temp tables) because the permissions have been tightened down.

I believe that I can safely change the ownership of the database in the old
server to qvowner, right? And run the pg_dump and pg_restore again? Or
should pg_restore connect as the superuser and just change ownership
afterwards?

-ron


> (I've thought several times that we should clean up pg_dump and
> pg_restore so that they report the failed query and backend message in
> *all* cases; right now they're pretty haphazard about it.)
> 
>             regards, tom lane
> 

Ron Snyder <snyder@roguewave.com> writes:
> May 31 16:11:50 vault pgcvs[2135]: [91] LOG:  query: Create Temporary Table
> pg_dump_blob_xref(oldOid pg_catalog.oid, newOid pg_catalog.oid);
> May 31 16:11:50 vault pgcvs[2135]: [93] ERROR:  quickview: not authorized to
> create temp tables

> My theory is that I'm getting this last message (not authorized to create
> temp tables) because the permissions have been tightened down.

Yeah.  Right at the moment, new databases default to only-db-owner-has-
any-rights, which means that others cannot create schemas or temp tables
in that database (unless they're superusers).  I'm of the opinion that
this is a bad default, but was waiting to see if anyone complained
before starting a discussion about it.

Probably we should have temp table creation allowed to all by default.
I'm not convinced that that's a good idea for schema-creation privilege
though.  Related issues: what should initdb set as the permissions for
template1?  Would it make sense for newly created databases to copy
their permission settings from the template database?  (Probably not,
since the owner is likely to be different.)  What about copying those
per-database config settings Peter just invented?

Comments anyone?  
        regards, tom lane

Tom,

> Probably we should have temp table creation allowed to all by default.
> I'm not convinced that that's a good idea for schema-creation privilege
> though.  Related issues: what should initdb set as the permissions for
> template1?  Would it make sense for newly created databases to copy
> their permission settings from the template database?  (Probably not,
> since the owner is likely to be different.)  What about copying those
> per-database config settings Peter just invented?

Yes.  I think there should be a not optional INITDB switch:  either --secure
or --permissive.   People usually know at the time of installation whether
they're building a web server (secure) or a home workstation (permissive).

Depending on the setting, this should set either a grant all or revoke all for
non-db owners as default, including such things as temp table creation.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology     josh@agliodbs.com   and data management solutions     (415) 565-7293  for law firms, small
businesses     fax 621-2533   and non-profit organizations.     San Francisco 

Josh Berkus wrote:
> 
> Tom,
> 
> > Probably we should have temp table creation allowed to all by default.
> > I'm not convinced that that's a good idea for schema-creation privilege
> > though.  Related issues: what should initdb set as the permissions for
> > template1?  Would it make sense for newly created databases to copy
> > their permission settings from the template database?  (Probably not,
> > since the owner is likely to be different.)  What about copying those
> > per-database config settings Peter just invented?
> 
> Yes.  I think there should be a not optional INITDB switch:  either --secure 
> or --permissive.   People usually know at the time of installation whether 
> they're building a web server (secure) or a home workstation (permissive).  
> 
> Depending on the setting, this should set either a grant all or revoke all for 
> non-db owners as default, including such things as temp table creation.

I like this idea.  I think we should prompt for tcp socket permission
setting for only the owner (Peter E's idea that I think he wants for
7.3), default public schema permissions, temp shema permissions, stuff
like that. We can have initdb flags to prevent the prompting, but doing
this quering at initdb time seems like an ideal solution.  We have
needed such control for a while.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Josh Berkus writes:

> Yes.  I think there should be a not optional INITDB switch:  either --secure
> or --permissive.   People usually know at the time of installation whether
> they're building a web server (secure) or a home workstation (permissive).

If you're on a home workstation you make yourself a superuser and be done
with it.

Adding too many options to initdb is not a path I would prefer since
initdb happens mostly hidden from the user these days.

-- 
Peter Eisentraut   peter_e@gmx.net

Tom Lane writes:

> Probably we should have temp table creation allowed to all by default.

Agreed.

> I'm not convinced that that's a good idea for schema-creation privilege
> though.

Agreed. (not good)

> Related issues: what should initdb set as the permissions for template1?

Same as above.

> What about copying those per-database config settings Peter just
> invented?

You're not supposed to put those into template1 anyway.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut wrote:
> Josh Berkus writes:
> 
> > Yes.  I think there should be a not optional INITDB switch:  either --secure
> > or --permissive.   People usually know at the time of installation whether
> > they're building a web server (secure) or a home workstation (permissive).
> 
> If you're on a home workstation you make yourself a superuser and be done
> with it.
> 
> Adding too many options to initdb is not a path I would prefer since
> initdb happens mostly hidden from the user these days.

Well, we have the config files for most things.  I would just like to
have an easy way to configure things that aren't GUC parameters.  That's
where the initdb idea came from.  Other ideas?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Folks,

> Adding too many options to initdb is not a path I would prefer since
> initdb happens mostly hidden from the user these days.

What about adding a parameter to CREATE DATABASE, then?  Like CREATE DATABASE
db1 WITH (SECURE)?

--
-Josh Berkus

Have we addressed this?  I don't think so.

---------------------------------------------------------------------------

Tom Lane wrote:
> Ron Snyder <snyder@roguewave.com> writes:
> > May 31 16:11:50 vault pgcvs[2135]: [91] LOG:  query: Create Temporary Table
> > pg_dump_blob_xref(oldOid pg_catalog.oid, newOid pg_catalog.oid);
> > May 31 16:11:50 vault pgcvs[2135]: [93] ERROR:  quickview: not authorized to
> > create temp tables
> 
> > My theory is that I'm getting this last message (not authorized to create
> > temp tables) because the permissions have been tightened down.
> 
> Yeah.  Right at the moment, new databases default to only-db-owner-has-
> any-rights, which means that others cannot create schemas or temp tables
> in that database (unless they're superusers).  I'm of the opinion that
> this is a bad default, but was waiting to see if anyone complained
> before starting a discussion about it.
> 
> Probably we should have temp table creation allowed to all by default.
> I'm not convinced that that's a good idea for schema-creation privilege
> though.  Related issues: what should initdb set as the permissions for
> template1?  Would it make sense for newly created databases to copy
> their permission settings from the template database?  (Probably not,
> since the owner is likely to be different.)  What about copying those
> per-database config settings Peter just invented?
> 
> Comments anyone?  
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Have we addressed this?  I don't think so.

No, it's not done yet.  My inclination is

* Template1 has temp table creation and schema creation disabled
(disallowed to world) by default.

* CREATE DATABASE sets up new databases with temp table creation allowed
to world and schema creation allowed to DB owner only (regardless of
what the template database had).  The owner can adjust this default
afterwards if he doesn't like it.

It would be nice to lock down the public schema in template1 too, but I
see no good way to do that, because CREATE DATABASE can't readily fiddle
with protections *inside* the database --- the only games we can play
are with the protections stored in the pg_database row itself.  So
public's permissions are going to be inherited from the template
database, and that means template1's public has to be writable.

Objections anyone?
        regards, tom lane

Sorry, I am confused.  Why can we modify temp's permissions on CREATE
DATABASE but not public's permissions?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Have we addressed this?  I don't think so.
> 
> No, it's not done yet.  My inclination is
> 
> * Template1 has temp table creation and schema creation disabled
> (disallowed to world) by default.
> 
> * CREATE DATABASE sets up new databases with temp table creation allowed
> to world and schema creation allowed to DB owner only (regardless of
> what the template database had).  The owner can adjust this default
> afterwards if he doesn't like it.
> 
> It would be nice to lock down the public schema in template1 too, but I
> see no good way to do that, because CREATE DATABASE can't readily fiddle
> with protections *inside* the database --- the only games we can play
> are with the protections stored in the pg_database row itself.  So
> public's permissions are going to be inherited from the template
> database, and that means template1's public has to be writable.
> 
> Objections anyone?
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

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

Mostly because a user may explicitly create a database with wanted
permissions, only to have this 'special code' remove them.

I personally intend to immediately revoke permissions on public in
template1, to allow the database owner to grant them as needed.

On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote:
> 
> Sorry, I am confused.  Why can we modify temp's permissions on CREATE
> DATABASE but not public's permissions?
> 
> ---------------------------------------------------------------------------
> 
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Have we addressed this?  I don't think so.
> > 
> > No, it's not done yet.  My inclination is
> > 
> > * Template1 has temp table creation and schema creation disabled
> > (disallowed to world) by default.
> > 
> > * CREATE DATABASE sets up new databases with temp table creation allowed
> > to world and schema creation allowed to DB owner only (regardless of
> > what the template database had).  The owner can adjust this default
> > afterwards if he doesn't like it.
> > 
> > It would be nice to lock down the public schema in template1 too, but I
> > see no good way to do that, because CREATE DATABASE can't readily fiddle
> > with protections *inside* the database --- the only games we can play
> > are with the protections stored in the pg_database row itself.  So
> > public's permissions are going to be inherited from the template
> > database, and that means template1's public has to be writable.
> > 
> > Objections anyone?
> > 
> >             regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

Oh, so we don't modify public writeability of template1 because the
admin may want to disable write in template1 so all future databases
will have it disabled.  I see.

So template1 is writable (yuck) only so databases created from template1
are writeable to world by default.  Is that accurate?

---------------------------------------------------------------------------

Rod Taylor wrote:
> Mostly because a user may explicitly create a database with wanted
> permissions, only to have this 'special code' remove them.
> 
> I personally intend to immediately revoke permissions on public in
> template1, to allow the database owner to grant them as needed.
> 
> On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote:
> > 
> > Sorry, I am confused.  Why can we modify temp's permissions on CREATE
> > DATABASE but not public's permissions?
> > 
> > ---------------------------------------------------------------------------
> > 
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > Have we addressed this?  I don't think so.
> > > 
> > > No, it's not done yet.  My inclination is
> > > 
> > > * Template1 has temp table creation and schema creation disabled
> > > (disallowed to world) by default.
> > > 
> > > * CREATE DATABASE sets up new databases with temp table creation allowed
> > > to world and schema creation allowed to DB owner only (regardless of
> > > what the template database had).  The owner can adjust this default
> > > afterwards if he doesn't like it.
> > > 
> > > It would be nice to lock down the public schema in template1 too, but I
> > > see no good way to do that, because CREATE DATABASE can't readily fiddle
> > > with protections *inside* the database --- the only games we can play
> > > are with the protections stored in the pg_database row itself.  So
> > > public's permissions are going to be inherited from the template
> > > database, and that means template1's public has to be writable.
> > > 
> > > Objections anyone?
> > > 
> > >             regards, tom lane
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > 
> > 
> > -- 
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 359-1001
> >   +  If your life is a hard drive,     |  13 Roberts Road
> >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
> > 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

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

On Mon, 2002-08-26 at 23:45, Bruce Momjian wrote:
> 
> Oh, so we don't modify public writeability of template1 because the
> admin may want to disable write in template1 so all future databases
> will have it disabled.  I see.
> 
> So template1 is writable (yuck) only so databases created from template1
> are writeable to world by default.  Is that accurate?

I believe thats the crux of the issue -- but those of us who don't want
newly created DBs to be world writable have no issues with that :)


Could create a template2 as the default 'copy from' template.  Make it
connectible strictly by superusers.  Template1 becomes a holding area
for those without a db to connect to and can be locked down.


Another is to enable users to connect to the server without requiring a
database.  This basically removes the secondary requirement of template1
to be the holding area for those otherwise without a home.


> ---------------------------------------------------------------------------
> 
> Rod Taylor wrote:
> > Mostly because a user may explicitly create a database with wanted
> > permissions, only to have this 'special code' remove them.
> > 
> > I personally intend to immediately revoke permissions on public in
> > template1, to allow the database owner to grant them as needed.
> > 
> > On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote:
> > > 
> > > Sorry, I am confused.  Why can we modify temp's permissions on CREATE
> > > DATABASE but not public's permissions?
> > > 
> > > ---------------------------------------------------------------------------
> > > 
> > > Tom Lane wrote:
> > > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > > Have we addressed this?  I don't think so.
> > > > 
> > > > No, it's not done yet.  My inclination is
> > > > 
> > > > * Template1 has temp table creation and schema creation disabled
> > > > (disallowed to world) by default.
> > > > 
> > > > * CREATE DATABASE sets up new databases with temp table creation allowed
> > > > to world and schema creation allowed to DB owner only (regardless of
> > > > what the template database had).  The owner can adjust this default
> > > > afterwards if he doesn't like it.
> > > > 
> > > > It would be nice to lock down the public schema in template1 too, but I
> > > > see no good way to do that, because CREATE DATABASE can't readily fiddle
> > > > with protections *inside* the database --- the only games we can play
> > > > are with the protections stored in the pg_database row itself.  So
> > > > public's permissions are going to be inherited from the template
> > > > database, and that means template1's public has to be writable.
> > > > 
> > > > Objections anyone?
> > > > 
> > > >             regards, tom lane
> > > > 
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 2: you can get off all lists at once with the unregister command
> > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > > 
> > > 
> > > -- 
> > >   Bruce Momjian                        |  http://candle.pha.pa.us
> > >   pgman@candle.pha.pa.us               |  (610) 359-1001
> > >   +  If your life is a hard drive,     |  13 Roberts Road
> > >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > > 
> > > http://archives.postgresql.org
> > > 
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> > 
> > http://www.postgresql.org/users-lounge/docs/faq.html
> > 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 

It just bothers me that of all the databases that should be locked down,
it should be template1, and it isn't by default.

---------------------------------------------------------------------------

Rod Taylor wrote:
> On Mon, 2002-08-26 at 23:45, Bruce Momjian wrote:
> > 
> > Oh, so we don't modify public writeability of template1 because the
> > admin may want to disable write in template1 so all future databases
> > will have it disabled.  I see.
> > 
> > So template1 is writable (yuck) only so databases created from template1
> > are writeable to world by default.  Is that accurate?
> 
> I believe thats the crux of the issue -- but those of us who don't want
> newly created DBs to be world writable have no issues with that :)
> 
> 
> Could create a template2 as the default 'copy from' template.  Make it
> connectible strictly by superusers.  Template1 becomes a holding area
> for those without a db to connect to and can be locked down.
> 
> 
> Another is to enable users to connect to the server without requiring a
> database.  This basically removes the secondary requirement of template1
> to be the holding area for those otherwise without a home.
> 
> 
> > ---------------------------------------------------------------------------
> > 
> > Rod Taylor wrote:
> > > Mostly because a user may explicitly create a database with wanted
> > > permissions, only to have this 'special code' remove them.
> > > 
> > > I personally intend to immediately revoke permissions on public in
> > > template1, to allow the database owner to grant them as needed.
> > > 
> > > On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote:
> > > > 
> > > > Sorry, I am confused.  Why can we modify temp's permissions on CREATE
> > > > DATABASE but not public's permissions?
> > > > 
> > > > ---------------------------------------------------------------------------
> > > > 
> > > > Tom Lane wrote:
> > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > > > Have we addressed this?  I don't think so.
> > > > > 
> > > > > No, it's not done yet.  My inclination is
> > > > > 
> > > > > * Template1 has temp table creation and schema creation disabled
> > > > > (disallowed to world) by default.
> > > > > 
> > > > > * CREATE DATABASE sets up new databases with temp table creation allowed
> > > > > to world and schema creation allowed to DB owner only (regardless of
> > > > > what the template database had).  The owner can adjust this default
> > > > > afterwards if he doesn't like it.
> > > > > 
> > > > > It would be nice to lock down the public schema in template1 too, but I
> > > > > see no good way to do that, because CREATE DATABASE can't readily fiddle
> > > > > with protections *inside* the database --- the only games we can play
> > > > > are with the protections stored in the pg_database row itself.  So
> > > > > public's permissions are going to be inherited from the template
> > > > > database, and that means template1's public has to be writable.
> > > > > 
> > > > > Objections anyone?
> > > > > 
> > > > >             regards, tom lane
> > > > > 
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > > > 
> > > > 
> > > > -- 
> > > >   Bruce Momjian                        |  http://candle.pha.pa.us
> > > >   pgman@candle.pha.pa.us               |  (610) 359-1001
> > > >   +  If your life is a hard drive,     |  13 Roberts Road
> > > >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> > > > 
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 6: Have you searched our list archives?
> > > > 
> > > > http://archives.postgresql.org
> > > > 
> > > 
> > > 
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > > 
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> > > 
> > 
> > -- 
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 359-1001
> >   +  If your life is a hard drive,     |  13 Roberts Road
> >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> > 
> 
> 
> 

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> So template1 is writable (yuck) only so databases created from template1
> are writeable to world by default.  Is that accurate?

Yup.

I had a probably-harebrained idea about this: the writeability of public
is only a serious issue when it is the default creation-target schema.
It's likely that you'd say "create table foo" without reflecting about
the fact that you're connected to template1; much less likely that you'd
say "create table public.foo".  So, what if the default per-database GUC
settings for template1 include setting the search_path to empty?  That
would preclude accidental table creation in template1's public schema.
As long as CREATE DATABASE doesn't copy the per-database GUC settings of
the template database, copied databases wouldn't be similarly crippled.

Now I'm not entirely convinced that CREATE DATABASE shouldn't copy the
per-database GUC settings of the template.  But at the moment it
doesn't, and if we're willing to institutionalize that behavior then
it'd provide a way out.

Or is that too weird?
        regards, tom lane

I had a good chuckle with this.  It is the type of "shoot for the moon"
idea I would have.  Maybe I am rubbing off on you.  :-)

The only problem I see with this solution is it makes admins think their
template1 is safe, when it really isn't.  That seems more dangerous than
leaving it world-writable.  I don't think accidental writes into
template1 are common enough to add a possible admin confusion factor.

What we really need is some mode on template1 that says, "I am not
world-writable, but the admin hasn't made me world-non-writable, so I
will create new databases that are world-writable".  Does that make
sense?

I have an idea.  Could we have the template1 per-database GUC settings
control the writeability of databases created from template1, sort of a
'creation GUC setting', so we could run it on the new database once it
is created?  That way, we could make template1 public
non-world-writable, and put something in the template1 per-database GUC
setting to make databases created from template1 world-writable.  If
someone removes that GUC setting, the databases get created non-world
writable.

Oh, there I go again, shooting at the moon.  ;-)

Another idea. Is there a GUC setting we could put in template1 that
would disable writing to public for world and _couldn't_ be revoked by
the user, except for super users?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > So template1 is writable (yuck) only so databases created from template1
> > are writeable to world by default.  Is that accurate?
> 
> Yup.
> 
> I had a probably-harebrained idea about this: the writeability of public
> is only a serious issue when it is the default creation-target schema.
> It's likely that you'd say "create table foo" without reflecting about
> the fact that you're connected to template1; much less likely that you'd
> say "create table public.foo".  So, what if the default per-database GUC
> settings for template1 include setting the search_path to empty?  That
> would preclude accidental table creation in template1's public schema.
> As long as CREATE DATABASE doesn't copy the per-database GUC settings of
> the template database, copied databases wouldn't be similarly crippled.
> 
> Now I'm not entirely convinced that CREATE DATABASE shouldn't copy the
> per-database GUC settings of the template.  But at the moment it
> doesn't, and if we're willing to institutionalize that behavior then
> it'd provide a way out.
> 
> Or is that too weird?
> 
>             regards, tom lane
> 

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

On Tue, 27 Aug 2002, Bruce Momjian wrote:

> 
> I had a good chuckle with this.  It is the type of "shoot for the moon"
> idea I would have.  Maybe I am rubbing off on you.  :-)
> 
> The only problem I see with this solution is it makes admins think their
> template1 is safe, when it really isn't.  That seems more dangerous than
> leaving it world-writable.  I don't think accidental writes into
> template1 are common enough to add a possible admin confusion factor.
> 
> What we really need is some mode on template1 that says, "I am not
> world-writable, but the admin hasn't made me world-non-writable, so I
> will create new databases that are world-writable".  Does that make
> sense?
> 
> I have an idea.  Could we have the template1 per-database GUC settings
> control the writeability of databases created from template1, sort of a
> 'creation GUC setting', so we could run it on the new database once it
> is created?  That way, we could make template1 public
> non-world-writable, and put something in the template1 per-database GUC
> setting to make databases created from template1 world-writable.  If
> someone removes that GUC setting, the databases get created non-world
> writable.
> 
> Oh, there I go again, shooting at the moon.  ;-)
> 
> Another idea. Is there a GUC setting we could put in template1 that
> would disable writing to public for world and _couldn't_ be revoked by
> the user, except for super users?

I think your idea is good.  Is there a chance we can have a set of very 
gross permissions based on the user and the database they are connected 
to and lives on top of the other security?  I.e.  UserA can READ from 
databaseB, and READ/WRITE from/to databaseA

Then, the regular permissions live under that?  Maybe we could have a 
some system that ANDed or ORed the perms easily so it wasn't slow or 
required a lot of extra programming, and if we really wanted it to not get 
in the way, only have it apply to the template databases?

Well, if there's any good ideas in there, let me know.  :-)  

OK, we are rolling out schemas in 7.3.  We better figure out if we have
the best solution for this.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Have we addressed this?  I don't think so.
> 
> No, it's not done yet.  My inclination is
> 
> * Template1 has temp table creation and schema creation disabled
> (disallowed to world) by default.
> 
> * CREATE DATABASE sets up new databases with temp table creation allowed
> to world and schema creation allowed to DB owner only (regardless of
> what the template database had).  The owner can adjust this default
> afterwards if he doesn't like it.
> 
> It would be nice to lock down the public schema in template1 too, but I
> see no good way to do that, because CREATE DATABASE can't readily fiddle
> with protections *inside* the database --- the only games we can play
> are with the protections stored in the pg_database row itself.  So
> public's permissions are going to be inherited from the template
> database, and that means template1's public has to be writable.
> 
> Objections anyone?
> 
>             regards, tom lane
> 

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

Can someone tell me where we are on this;  exactly what writability do
we have in 7.3?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Have we addressed this?  I don't think so.
> 
> No, it's not done yet.  My inclination is
> 
> * Template1 has temp table creation and schema creation disabled
> (disallowed to world) by default.
> 
> * CREATE DATABASE sets up new databases with temp table creation allowed
> to world and schema creation allowed to DB owner only (regardless of
> what the template database had).  The owner can adjust this default
> afterwards if he doesn't like it.
> 
> It would be nice to lock down the public schema in template1 too, but I
> see no good way to do that, because CREATE DATABASE can't readily fiddle
> with protections *inside* the database --- the only games we can play
> are with the protections stored in the pg_database row itself.  So
> public's permissions are going to be inherited from the template
> database, and that means template1's public has to be writable.
> 
> Objections anyone?
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone tell me where we are on this;  exactly what writability do
> we have in 7.3?

The current code implements what I suggested in that note, viz:
default permissions for new databases areowner = all rights (ie, create schema and create temp)public = create temp
rightonly
 
but template1 and template0 are set toowner (postgres user) = all rightspublic = no rights
by initdb.

Also, the "public" schema within template1 is empty but writable by
public.  This is annoying, but at least it's easy to fix if you
mess up --- you can DROP SCHEMA public CASCADE and then recreate
the schema.  (Or not, if you don't want to.)
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can someone tell me where we are on this;  exactly what writability do
> > we have in 7.3?
> 
> The current code implements what I suggested in that note, viz:
> default permissions for new databases are
>     owner = all rights (ie, create schema and create temp)
>     public = create temp right only
> but template1 and template0 are set to
>     owner (postgres user) = all rights
>     public = no rights
> by initdb.
> 
> Also, the "public" schema within template1 is empty but writable by
> public.  This is annoying, but at least it's easy to fix if you
> mess up --- you can DROP SCHEMA public CASCADE and then recreate
> the schema.  (Or not, if you don't want to.)

OK, yes, this is what I thought, that public in all databases is
world-writable, but you can control that by dropping and recreating the
public schema, or altering the schema, right?

How did you get temp schemas non-world writable in template1 but not in
the databases, or am I confused?

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> How did you get temp schemas non-world writable in template1 but not in
> the databases, or am I confused?

That right is associated with the database, so we just have to control
what CREATE DATABASE puts in the new pg_database row.
        regards, tom lane