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

Argh. I just realized that I gave this the wrong subject-- it should've been
"Can't pg_restore large objects"

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.

This test case works just fine with 7.2.1, but fails with my 'checked out
today' code.
Here is my shell script test case:
# this script assumes that the current user can connect without
# being prompted for a password

createuser -A -D lotest1
createuser -A -D lotest2

createdb lotest1

TESTF=/tmp/pgtest$$
cat >> $TESTF <<EOF
This is just a simple little file
EOF

#I don't think that this table is absolutely necessary for this test
psql lotest1 lotest1 -c "create table a (bah int);"
#now create the troublemaker table
psql lotest1 lotest2 -c "create table z (bah int);"
psql lotest1 lotest1 -c "\lo_import $TESTF";
pg_dump --blobs --format=c --quotes --oids --compress=5 lotest1 >
/tmp/lotest1.dump
psql template1 -c "drop database lotest1;"
createdb lotest1
pg_restore -d lotest1 < /tmp/lotest1.dump

## cleanup

rm $TESTF
rm /tmp/lotest1.dump

dropdb lotest1

dropuser lotest1
dropuser lotest2
########## End of test case

If that is according to design, then migration could be very painful going
to 7.3 because some databases could have tables owned by several different
users.

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 information technology 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, Pennsylvania 19026

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

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

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, Pennsylvania 19026

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, Pennsylvania 19073

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, Pennsylvania 19073

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.

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, Pennsylvania 19073

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.

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, Pennsylvania 19073

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, Pennsylvania 19073

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 think template1 public should be non-world-writeable by default, and pass that
to newly created databases. What exactly was it, why we don't want that ?
Backwards compatibility ? Imho doing one (additional) grant after creating a db
cannot be such a problem, no ?

Andreas

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

... What exactly was it, why we don't want that ?
Backwards compatibility ? Imho doing one (additional) grant after creating a db
cannot be such a problem, no ?

Breaking every existing pg_dumpall script doesn't strike you as a
problem?

regards, tom lane

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, Pennsylvania 19073

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, Pennsylvania 19073

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

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, Pennsylvania 19073

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