Re: [HACKERS] Proposed TODO: CREATE .... WITH OWNER;

[Dennis Bjorklund]

On Sat, 23 Oct 2004, Tom Lane wrote:

> Josh Berkus <[EMAIL PROTECTED]> writes:
> > Dennis and I are hashing this out on IRC.   The second option would be to 
> > simply put SET SESSION AUTHORIZATION statements before each and every 
> > statement in the pg_dump.   This would make each statement "atomic" as far as
> > user ownership is concerned, with less changes than "WITH OWNER" would 
> > entail.  
> 
> Uh, isn't that how we did it before?  Why is that better?

I havn't looked at what pg_dump do in the code. Josh showed some code
generated by pg_dump that contains SET SESSSION ... and then some
statement and a RESET SESSION AUTHORIZATION. When I saw that I simply
asked; why do it issue the RESET at all? Wouldn't it be enough to just set
the user whenever needed? Especially since Josh said that pg_dump got the 
resets wrong.

In the extreme one could set the user before every statement but a better
way is that pg_dump keeps track of who is the current user and then just
issue a SET SESSION AUTH when needed. This sounds like what I though 
pg_dump were doing already, but probably wasn't since it got it wrong 
and Josh had a database where the owners after restore was messed up.

Another observation is that SET SESSION AUTHORIZATION postgres; and RESET
SESSION AUTHORIZATION; would be the same when postgres is the superuser.  
By not using the name of the superuser one get the benefit that one can
restore as another superuser (but see the part about acl's below).

Well, hopefully this is not a problem in 8.0 as you say.


When discussing this, _another issue_ came up that made me thinking. Let
me ask about that:

When you alter the owner of an table with ALTER TABLE ...  OWNER TO ...
then it looks like it just sets the owner but does not alter the acl
string at all (at least in 7.4 where I tested). So after one have altered
the owner it's possible that the new owner does not have any rights set
for the object. and (worse) that the old owner still have rights set.

It's also more complicated since in some cases the acl is set to NULL
which means that it has the default priviledges. And the default
privileges always include all privileges for the owner. So if the acl is
NULL then the old owner looses its privileges and the new gets them. Here
we have a different semantics based on an implementation detail that's not
very visible to the user.

-- 
/Dennis Björklund


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Time off

[Lamar Owen]

[late on a Saturday night, getting ready to go to bed, after putting four to 
bed: this topic is just too good to pass onapologies in advance...]

On Saturday 23 October 2004 17:16, Steve Crawford wrote:
> > Its also an unusual replication scheme in that, more often than
> > not, the slaves control the masters.

> As the slave of a replica with an 86 day 16 hour uptime I've also
> discovered that the new I/O functions take some adjustment as does
> working around the lack of sleep(3).

The 9 month bootstrap time does cause some interesting latency issues, not to 
mention the nondeterministic behavior and unpredictable endianess of the 
processors that can cause the controlling init to fallback to heuristic 
techniques of initing processes in parallel, out-of-order, speculative,  
deeply and randomly pipelined manners.  INTERCAL is easier to program than 
the machine code of these replicas.  Forget the trampolines of COME-FROM.  
You get the wonders of ME-TOO and HE-DID_IT.  Endless loops of 
DID-TO::DID_NOT require the deepest programming discipline, and sometimes a 
nonmaskable interrupt, to break.  But the WHY loop is the most difficult, 
since the degree of precision of the controlling conditional constantly and 
randomly changes.

But very few programming tasks are more rewarding than bringing this NDIA of 
the last order to code maturity, and even to version 2.0.   Process migration 
issues abound, but are necessary for proper process stability.  The 
controlling init process pair often has difficulty free'ing malloc'ed 
resources while migrating child processes.  Inevitable memory leaks occur, 
with free'ed resources never equalling malloc'ed ones.  But when the replica 
forks, and spawns its own child process, resource utilization goes up; but 
fortunately the VM code can easily swap back to the home of the originating 
processes.

Here with four, one big endian with an 10y26w5d5h41m uptime, one little endian 
with 9y27w6d21h37m, one little endian at 7y7w2d22h14m, and one little endian 
2y2w1d4h56m (due to kernel/init spawn events, uptime resolutions of less than 
a minute are difficult, if not impossible, to determine due to time dilation 
effects at kernel-initprocess handoff, where the spawning init loses 
timeslices during replica kernel respawn.).  Endian conflicts abound, but 
uptime-related conflicts abound more, with significant replica competition 
for init process timeslices; all such attempts typically require superuser 
intervention to re-nice. 

*ducking*and*grinning*
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[HACKERS] Dumb shlib build rules cause regression test failures on ia64

[Tom Lane]

I am seeing regression test failures on HPUX 11.23 (on ia64) using gcc
3.4.1.  The failures occur because refint.so fails to load:

/usr/lib/hpux32/dld.so: Unsatisfied code symbol '__divdi3' in load module 
'/house/tgl/pgsql/src/test/regress/../../../contrib/spi/refint.so'.
ERROR:  could not load library 
"/house/tgl/pgsql/src/test/regress/../../../contrib/spi/refint.so": Unresolved external

Several of the contrib regression tests fail similarly.  The problem is
that the .so files get linked without mentioning libgcc.a, and
apparently this platform won't resolve the references to link to the
same routines in the backend.

There is no problem with modules that are linked using Makefile.shlib,
because it knows to add the appropriate libgcc reference to the link.
But the "MODULES" branch in pgxs.mk is not as smart.  It's effectively
relying on the DLSUFFIX rule supplied by the platform-specific makefile.
Those rules have always been a few bricks shy of a load, IMHO.

The obvious solution to this is to use Makefile.shlib all the time,
but there's a problem: Makefile.shlib is only designed to build a single
shlib per build subdirectory, and contrib/spi wants to build several.
I don't see any easy way to rejigger Makefile.shlib to support multiple
shared libraries at once --- anyone see a way?

A klugy workaround is to build all the modules in contrib/spi into a
single shared library.  This is ugly but I can't level any worse charge
than "ugly" against it.

The other contrib modules build no more than one shared library apiece,
and could trivially be converted to the MODULE_big build path.  Or more
likely, redefine the MODULES case in pgxs.mk to support only one module
in a directory, and use Makefile.shlib all the time.

Comments?

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Proposed TODO: CREATE .... WITH OWNER;

[Josh Berkus]

Tom,

> No, you misunderstood me.  The bug of which you are complaining (namely,
> ALTER OWNER not fixing the ACL list) is gone in 8.0; therefore you are
> arguing from a faulty premise about whether this change is needed.

Aha, I misunderstood the terse phrasing ;-)

Will have to try destruction test on 8.0.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Proposed TODO: CREATE .... WITH OWNER;

[Tom Lane]

Josh Berkus <[EMAIL PROTECTED]> writes:
> Dennis and I are hashing this out on IRC.   The second option would be to 
> simply put SET SESSION AUTHORIZATION statements before each and every 
> statement in the pg_dump.   This would make each statement "atomic" as far as
> user ownership is concerned, with less changes than "WITH OWNER" would 
> entail.  

Uh, isn't that how we did it before?  Why is that better?

>> Not in 8.0.

> Of course not.I'm talking for 8.1, or later.

No, you misunderstood me.  The bug of which you are complaining (namely,
ALTER OWNER not fixing the ACL list) is gone in 8.0; therefore you are
arguing from a faulty premise about whether this change is needed.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[HACKERS] unsubscribe-digest

[liyuexin]

unsubscribe-digest



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Proposed TODO: CREATE .... WITH OWNER;

[Josh Berkus]

Tom,

> The main objection to this is that it makes the dump completely
> unportable.

That's a powerful argument.   

Dennis and I are hashing this out on IRC.   The second option would be to 
simply put SET SESSION AUTHORIZATION statements before each and every 
statement in the pg_dump.   This would make each statement "atomic" as far as 
user ownership is concerned, with less changes than "WITH OWNER" would 
entail.  

I can't imagine that it would slow down restoring much, and could even be 
helped by making SET SESSION AUTHORIZATION realize it didn't have to do 
anything if that was already the current user (does it now?).

> Not in 8.0.

Of course not.I'm talking for 8.1, or later.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] Proposed TODO: CREATE .... WITH OWNER;

[Tom Lane]

Josh Berkus <[EMAIL PROTECTED]> writes:
> Instead, we should have a "CREATE  WITH OWNER username" extension to all 
> of our CREATE  statements.

The main objection to this is that it makes the dump completely
unportable.

> CREATE followed by ALTER ... CHANGE OWNER would not be an adequate substitute.  
> The orginal owner of the object (in the case of a restore, the superuser) 
> retains all of their permissions on the object, which causes a lot of messy 
> GRANT statements.

Not in 8.0.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[HACKERS] Proposed TODO: CREATE .... WITH OWNER;

[Josh Berkus]

People:

Having today spent 3.5 hours correcting a pg_dump file with permissions 
problems, I've come to the inescapable realization that the "SESSION 
AUTHORIZATION" concept is WAY too fragile.

Instead, we should have a "CREATE  WITH OWNER username" extension to all 
of our CREATE  statements. Then any backup, or fragment of a 
backup, could be run by the superuser without fear that a bunch of objects 
could end up owned by a user with no permissions on them.   (And if you think 
such a fear does not exist, try using "CHANGE OWNER" on about 80 database 
objects, some of them with dependancies owned by other users, and then 
pg_dump and restore.  Fun, fun!).

CREATE followed by ALTER ... CHANGE OWNER would not be an adequate substitute.  
The orginal owner of the object (in the case of a restore, the superuser) 
retains all of their permissions on the object, which causes a lot of messy 
GRANT statements.

H ... this would also require a GRANT  AS USER name.  But those two 
changes should simplify dump and restore enormously.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[HACKERS] Question on the 8.0Beta Version

[Davide Negri]

Hello, 

i have installed postgres 8.0 beta3 as a program on
my pc on windows xp. I have read on the installation note file that postgres
server will not run with administrative permissions. I have read on your forum
that this happened because i have installed the beta version: is it true that
this happen only with the beta version? When will you plan to release the
production version? 

 

Thanks You

 

Negri Davide








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004
 

[HACKERS] Slony-I 1.0.4 Released

[Chris Browne]

The Slony-I team is proud to present the 1.0.4 release of the most
advanced replication solution for the most advanced Open Source
Database in the world.

The release tarball is available for download 
   http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.4.tar.gz

There are a limited number of "new features" this release largely in
terms of adding in the ability to move or drop tables and sequences
from replication:

 - SET DROP TABLE - drops a table from replication
 - SET DROP SEQUENCE - does the same for sequences
 - SET MOVE TABLE - moves a table from one replication set to another
 - SET MOVE SEQUENCE - moves a sequence from one replication set to another

Other changes involve smoothing out the 'sharp edges' found by early
adopters, notably including:

 - Frequently vacuuming pg_listener; growth of dead tuples could hurt
   performance
 
 - A cleanup process for pg_listener resolves cases where old slon
   processes may have terminated due to network problems, leaving
   backends around holding onto event notifications

 - Lowered lock level on sl_event, resolving issues where pg_dump
   would block Slony-I

 - Purges CONFIRM entries for nodes that don't exist anymore

 - Substantially increased documentation

 - More sophisticated administration scripts 

 - Now uses string comparison for user defined types that do not have
   a suitable comparison operation

 - Safer log purging

 - Various other bug fixes and "improved cleanliness."
 
 - As of 1.0.4, the slon replication engine refuses to work against
   any database that does not have the stored procedures for the same
   version loaded or where the shared object containing the C language
   support functions and the log trigger does not match the version
   number.  in a cluster must be upgraded at once.

See the HISTORY-1.0 file for a detailed list of changes.


-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] to_char/to_number loses sign

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> SELECT to_number('485-', '999S');
>  to_number
> ---
>485

> Is this a bug or intentional?

Tracing through this, it looks like the problem is that NUM_processor()
has no switch case for NUM_S (nor does the default case raise an error,
which seems a risky practice to me).

Karel, can you verify this and submit a fix?

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Time off

[Steve Crawford]

> Its also an unusual replication scheme in that, more often than
> not, the slaves control the masters.

As the slave of a replica with an 86 day 16 hour uptime I've also 
discovered that the new I/O functions take some adjustment as does 
working around the lack of sleep(3).

Cheers,
Steve

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[HACKERS] to_char/to_number loses sign

[Peter Eisentraut]

This is from one of the examples in the documentation:

SELECT to_char(-485, '999S');
 to_char
-
 485-

The reverse doesn't work as well:

SEKLECT to_number('485-', '999S');
 to_number
---
   485

Is this a bug or intentional?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html