Postgresql -- initial impressions and comments

j.random.programmer <javadesigner@yahoo.com> wrote:

Hi:

I am a long time MySQL/Innodb user. I just installed
postgres 7.3 on my linux box and thought I'd post
some comments:

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

Why run as root?
It's as simple to run as postgres user, and by doing it this way we
catch any security holes as an non root.
It's best to be consistant in my meaning, as it makes you NEVER install
an root hole.
It's also easier to limit resources on an userlevel (quotas, rlimits).

[snip]

3) The documentation needs to be radically improved.
The mysql docs are much more comprehensive.

[snip]
What topics are the documentation bad?
I find it very good and on-topic.
I usually find everything i need in the Reference Guide, if not it's in
the Users Guide.

4) The auto-increment ("serial") fields are very
badly documented.

http://www.se.postgresql.org/users-lounge/docs/7.3/postgres/datatype.htm
l#DATATYPE-SERIAL
Doesn't this tell you what you need to know?

Normally, I want to say something like:

INSERT into foo values (null, 'a', 'b',...)

where the first field is defined as serial. However
I can't send null to that field and expect it to be
auto incremented - I have to either list out my
fields in the statment (and omit the serial field):

INSERT into foo
(field_a, field_b,...) values ('a', 'b', ...)

[snip]

Well it's a bad style to use the first syntax.
It doesn't work if the column order changes (database dump/restore,
column drop/add).
It will also break you app if there's a new column added, it wouldn't if
you use the second syntax.

[snip]

6) Well, docs again: there are no examples or
techniques for importing/exporting comma or tab
delimited files from/to tables ? Is this possible but
I just didn't find it ?

Exporting:
try "man pg_dump".
Also read the documentation:
http://www.se.postgresql.org/users-lounge/docs/7.3/postgres/backup.html

Importing:
http://www.se.postgresql.org/users-lounge/docs/7.3/postgres/sql-copy.htm
l

Well, having played with postgres for the first time,
these were the initial (after 4-5 hours) points that
came up. But overall, it looks and feels like a solid
product. The hot dump is a nice feature - I can
dump the db without shutting it down - innodb in
contrast charges $$ for this feature.

Best regards,

javadesigner@yahoo.com

Nice that you like it ;)
It looks only that you now need to learn the documentation layout :)

Magnus

On Mon, 2 Dec 2002, j.random.programmer wrote:

Hi:

I am a long time MySQL/Innodb user. I just installed
postgres 7.3 on my linux box and thought I'd post
some comments:

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

No, it's not. It's not the user that isn't trusted, it's j.random.cracker
that isn't to be trusted. with the current setup, if j.random.cracker
finds an exploit in Postrgresql, they can trash your database, but they
can't root your box. If Postgresql were run as root, then
j.random.cracker could then proceed to destroy the whole machine
(including any protected backups, syslogs, etc...) thus covering his
tracks.

This feature (mysql mis-feature?) isn't likely to change any time soon.

2)
It's not clear what md5 password auth does.

(a) Is the password stored as md5 in the database
itself, or only md5's on the wire (from client to
server) and then thereafter stored as plain text in
the db itself ?

In the database, on the wire.

(b) If the client is responsible for the md5
encryption,
then does the JDBC driver do this for us automatically

Yes. You need a fairly late model jdbc driver.

(c) Is there a md5 function in the database ?

I'm not sure.

3) The documentation needs to be radically improved.
The mysql docs are much more comprehensive. Postgres
wins hands down in the database internals
documentation
(mysql doesn't have any) but loses in the userland
documentation.

Good news! You can submit changes and have them accpeted. Seriously,
it's much easier to get changes accepted into postgresql than mysql.

For the latest version of the docs, take a look at:

http://developer.postgresql.org/docs/postgres/index.html

They are much more up to date than the ones that came with 7.2.x

4) The auto-increment ("serial") fields are very
badly documented.

Normally, I want to say something like:

INSERT into foo values (null, 'a', 'b',...)

where the first field is defined as serial. However
I can't send null to that field and expect it to be
auto incremented - I have to either list out my
fields in the statment (and omit the serial field):

INSERT into foo
(field_a, field_b,...) values ('a', 'b', ...)

which is a drag or I have to use a funky nextval
command. Why can't postgres simply accept null for
the serial field and simply fill in the next value ?
This would make it easier to use (don't have to type
in the field list) and more consistent (with mysql and
perhaps other databases).

Just because that's how MySQL does it doesn't make it right (TM rights
reserved, etc...)

NULL is not the same as DEFAULT. Treating it the same is not correct.
Getting used to incorrect behaviour could result in unexpected results
when dealing with other databases. MySQL seems to train people to expect
what should not be.

Before 7.3, you HAD to do it the old fashioned way, i.e. either have a
list of fields and leave out the autoincrement field, or put your
autoincrement id at the end of your fields (that's how I always did it)

As of 7.3 you can now use the DEFAULT keyword. I.e. like so:

insert into table values (DEFAULT,1,2,'howdy')

will work. 7.3 was just released. It rocks. :-)

5) There is no way to grant permissions on all tables
within a database to some user. You have to grant
permissions on each table one-by-one. What I want
to do (and mysql allows this) is something like:

GRANT ALL on foodb.* to user_bar;

where 'foodb' is the name of a database. This sucks.

It certainly sounds useful. It's not on the todo list, and I don't think
it's scratched and itch for anyone I know of. Might be a nice place to
get your feet wet if you want to take part in hacking postgresql.

6) Well, docs again: there are no examples or
techniques for importing/exporting comma or tab
delimited files from/to tables ? Is this possible but
I just didn't find it ?

Tab delimited it easy, just type '\h copy' from the psql monitor to see
the online (i.e. very short) docs for this, or go here:

http://developer.postgresql.org/docs/postgres/populate.html
or
http://developer.postgresql.org/docs/postgres/sql-copy.html
To see the status of things to be done, i.e. csv copy, see:
http://developer.postgresql.org/todo.php

(i.e. CSV copy is on the list)

Well, having played with postgres for the first time,
these were the initial (after 4-5 hours) points that
came up. But overall, it looks and feels like a solid
product. The hot dump is a nice feature - I can
dump the db without shutting it down - innodb in
contrast charges $$ for this feature.

It'll feel more comfortable as time goes by. Funny thing is, after using
it for a while, when you go back to MySQL for some project, the sticking
points will be MUCH bigger. I.e. things like "well, if I functional
indexes, I could do this..."

Good luck on using postgresql, and definitely feel free to contribute to
the documentation, it could use several people with a "newbie" point of
view writing and re-writing parts of it.

Hi, thanks for your comments. Just a couple quick thoughts in response.

Regarding documentation, did you find http://techdocs.postgresql.org? It
has lots of fill-in-the-gaps documents contributed by various readers,
including several that cover different aspects of moving from mysql to
postgresql. There's also an FAQ early on that I think might help answer
your questions about md5. That may be a lot less of an issue in postgresql
7.3, if I understand the release notes correctly...

As far as doing things like MySQL does... Postgresql's design goal has
generally been to comply with SQL 92 and/or SQL 99 standards as much as
possible. MySQL does a lot of sloppy stuff (IMHO) that isn't standards
compliant in the least. Seems better to let ANSI set the standards. As for
inserting null values into primary key fields and expecting the database to
fill in the next 'autoincrement' value... that just seems very
non-intuitive to me, as well as non-portable. If I write a SQL statement
that says to insert a null value into a column, I want the database to try
and insert a null value into the column, not some autoincrement value or
other default. For any column that has a default specified (as postgresql's
serial columns do), it seems more reasonable for the database to use the
default when the field isn't specified, and to use 'null' if the insert
statement says null. Relying on the database's column order in the
application's SQL statements creates a lot of extra coupling between the
application and database structure. I once worked on an application that
was done that way, using MySQL, and it was a major pain paying attention to
column order when we wanted to make a small change to a table's schema.
Spelling out the field names would have saved some grief.

In any case, I'm glad you're exploring postgresql. This doc points out a
few other gotchas you may run across:
http://techdocs.postgresql.org/techdocs/mysql2postgresql.php. Good luck!

Wes Sheldahl

"j.random.programmer" <javadesigner@yahoo.com>@postgresql.org on 12/02/2002
09:44:03 PM

Sent by: pgsql-general-owner@postgresql.org

To: pgsql-general@postgresql.org
cc:
Subject: [GENERAL] Postgresql -- initial impressions and comments

Hi:

I am a long time MySQL/Innodb user. I just installed
postgres 7.3 on my linux box and thought I'd post
some comments:

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

2)
It's not clear what md5 password auth does.

(a) Is the password stored as md5 in the database
itself, or only md5's on the wire (from client to
server) and then thereafter stored as plain text in
the db itself ?

(b) If the client is responsible for the md5
encryption,
then does the JDBC driver do this for us automatically
?

(c) Is there a md5 function in the database ?

The docs don't really answer these points at all -
especially (b) and (c).

3) The documentation needs to be radically improved.
The mysql docs are much more comprehensive. Postgres
wins hands down in the database internals
documentation
(mysql doesn't have any) but loses in the userland
documentation.

4) The auto-increment ("serial") fields are very
badly documented.

Normally, I want to say something like:

INSERT into foo values (null, 'a', 'b',...)

where the first field is defined as serial. However
I can't send null to that field and expect it to be
auto incremented - I have to either list out my
fields in the statment (and omit the serial field):

INSERT into foo
(field_a, field_b,...) values ('a', 'b', ...)

which is a drag or I have to use a funky nextval
command. Why can't postgres simply accept null for
the serial field and simply fill in the next value ?
This would make it easier to use (don't have to type
in the field list) and more consistent (with mysql and
perhaps other databases).

5) There is no way to grant permissions on all tables
within a database to some user. You have to grant
permissions on each table one-by-one. What I want
to do (and mysql allows this) is something like:

GRANT ALL on foodb.* to user_bar;

where 'foodb' is the name of a database. This sucks.

6) Well, docs again: there are no examples or
techniques for importing/exporting comma or tab
delimited files from/to tables ? Is this possible but
I just didn't find it ?

Well, having played with postgres for the first time,
these were the initial (after 4-5 hours) points that
came up. But overall, it looks and feels like a solid
product. The hot dump is a nice feature - I can
dump the db without shutting it down - innodb in
contrast charges $$ for this feature.

Best regards,

javadesigner@yahoo.com

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

you can use md5 encryption installing the pgcrypto CONTRIB
On Tue, 2002-12-03 at 18:30, scott.marlowe wrote:

On Mon, 2 Dec 2002, j.random.programmer wrote:

Hi:

I am a long time MySQL/Innodb user. I just installed
postgres 7.3 on my linux box and thought I'd post
some comments:

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

No, it's not. It's not the user that isn't trusted, it's j.random.cracker
that isn't to be trusted. with the current setup, if j.random.cracker
finds an exploit in Postrgresql, they can trash your database, but they
can't root your box. If Postgresql were run as root, then
j.random.cracker could then proceed to destroy the whole machine
(including any protected backups, syslogs, etc...) thus covering his
tracks.

This feature (mysql mis-feature?) isn't likely to change any time soon.

2)
It's not clear what md5 password auth does.

(a) Is the password stored as md5 in the database
itself, or only md5's on the wire (from client to
server) and then thereafter stored as plain text in
the db itself ?

In the database, on the wire.

(b) If the client is responsible for the md5
encryption,
then does the JDBC driver do this for us automatically

Yes. You need a fairly late model jdbc driver.

(c) Is there a md5 function in the database ?

I'm not sure.

3) The documentation needs to be radically improved.
The mysql docs are much more comprehensive. Postgres
wins hands down in the database internals
documentation
(mysql doesn't have any) but loses in the userland
documentation.

Good news! You can submit changes and have them accpeted. Seriously,
it's much easier to get changes accepted into postgresql than mysql.

For the latest version of the docs, take a look at:

http://developer.postgresql.org/docs/postgres/index.html

They are much more up to date than the ones that came with 7.2.x

4) The auto-increment ("serial") fields are very
badly documented.

Normally, I want to say something like:

INSERT into foo values (null, 'a', 'b',...)

where the first field is defined as serial. However
I can't send null to that field and expect it to be
auto incremented - I have to either list out my
fields in the statment (and omit the serial field):

INSERT into foo
(field_a, field_b,...) values ('a', 'b', ...)

which is a drag or I have to use a funky nextval
command. Why can't postgres simply accept null for
the serial field and simply fill in the next value ?
This would make it easier to use (don't have to type
in the field list) and more consistent (with mysql and
perhaps other databases).

Just because that's how MySQL does it doesn't make it right (TM rights
reserved, etc...)

NULL is not the same as DEFAULT. Treating it the same is not correct.
Getting used to incorrect behaviour could result in unexpected results
when dealing with other databases. MySQL seems to train people to expect
what should not be.

Before 7.3, you HAD to do it the old fashioned way, i.e. either have a
list of fields and leave out the autoincrement field, or put your
autoincrement id at the end of your fields (that's how I always did it)

As of 7.3 you can now use the DEFAULT keyword. I.e. like so:

insert into table values (DEFAULT,1,2,'howdy')

will work. 7.3 was just released. It rocks. :-)

5) There is no way to grant permissions on all tables
within a database to some user. You have to grant
permissions on each table one-by-one. What I want
to do (and mysql allows this) is something like:

GRANT ALL on foodb.* to user_bar;

where 'foodb' is the name of a database. This sucks.

It certainly sounds useful. It's not on the todo list, and I don't think
it's scratched and itch for anyone I know of. Might be a nice place to
get your feet wet if you want to take part in hacking postgresql.

6) Well, docs again: there are no examples or
techniques for importing/exporting comma or tab
delimited files from/to tables ? Is this possible but
I just didn't find it ?

Tab delimited it easy, just type '\h copy' from the psql monitor to see
the online (i.e. very short) docs for this, or go here:

http://developer.postgresql.org/docs/postgres/populate.html
or
http://developer.postgresql.org/docs/postgres/sql-copy.html
To see the status of things to be done, i.e. csv copy, see:
http://developer.postgresql.org/todo.php

(i.e. CSV copy is on the list)

Well, having played with postgres for the first time,
these were the initial (after 4-5 hours) points that
came up. But overall, it looks and feels like a solid
product. The hot dump is a nice feature - I can
dump the db without shutting it down - innodb in
contrast charges $$ for this feature.

It'll feel more comfortable as time goes by. Funny thing is, after using
it for a while, when you go back to MySQL for some project, the sticking
points will be MUCH bigger. I.e. things like "well, if I functional
indexes, I could do this..."

Good luck on using postgresql, and definitely feel free to contribute to
the documentation, it could use several people with a "newbie" point of
view writing and re-writing parts of it.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Faculdade Ritter dos Reis
www.ritterdosreis.br
felipes@ritterdosreis.br
Fone/Fax.: (51)32303328

On Mon, Dec 02, 2002 at 18:44:03 -0800,
"j.random.programmer" <javadesigner@yahoo.com> wrote:

4) The auto-increment ("serial") fields are very
badly documented.

Normally, I want to say something like:

INSERT into foo values (null, 'a', 'b',...)

where the first field is defined as serial. However
I can't send null to that field and expect it to be
auto incremented - I have to either list out my
fields in the statment (and omit the serial field):

Use the keyword DEFAULT instead of NULL.

5) There is no way to grant permissions on all tables
within a database to some user. You have to grant
permissions on each table one-by-one. What I want
to do (and mysql allows this) is something like:

GRANT ALL on foodb.* to user_bar;

You can get the list of tables from the system catalog and then issue
grant statements for them. You could write a function to do this,
do it in an application or write sql query output to a script which
you then execute.

In any case, I'm glad you're exploring postgresql. This doc points out a
few other gotchas you may run across:
http://techdocs.postgresql.org/techdocs/mysql2postgresql.php. Good luck!

This is a great article. I found one problem, though, and while I poked around
a bit to find this, I couldn't. So could someone point me to where this:

http://www.ca.postgresql.org/devel-corner/docs/postgres/index.html

really is?

thnx,
Chris

j.random.programmer wrote:

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

Ugh. No, no, no! It's a really bad idea to run things as root that don't
need to be, even if everyone who uses the system (and for whom it's even
physically possible to connect to the system) is totally trusted, which
almost never happens anyway. Programs running as root can do much more
damage if they go wrong.

2)
It's not clear what md5 password auth does.

(a) Is the password stored as md5 in the database
itself, or only md5's on the wire (from client to
server) and then thereafter stored as plain text in
the db itself ?

Ahh, good question. One of the other responses says it means both.

That's disconcerting to me because I think it defeats the point of
sending MD5 signatures on the wire - avoiding replay attacks. If it's
stored in MD5 format on the server, it can't request it with a different
salt every time (how would it compare them?), so you can just replay the
MD5 transmission.

The other way, though, a compromise of the database would mean a
compromise of all the passwords.

So it definitely would be helpful to have an answer to your question in
with the description of the authentication types, so you could choose
intelligently based on what you consider to be more likely risks.

(b) If the client is responsible for the md5
encryption,
then does the JDBC driver do this for us automatically
?

(c) Is there a md5 function in the database ?

Not by default. But look at contrib/crypto in the source distribution.
You'll have to manually compile it/insert it into the database. Once you
do that, it should do what you want. Works well for me, anyway.

Where would you have expected to find this information in the
documentation? I'm sure it could be added if you could find a good place
for it...

Scott

Scott Lamb wrote:

2)
It's not clear what md5 password auth does.

(a) Is the password stored as md5 in the database
itself, or only md5's on the wire (from client to
server) and then thereafter stored as plain text in
the db itself ?

Ahh, good question. One of the other responses says it means both.

That's disconcerting to me because I think it defeats the point of
sending MD5 signatures on the wire - avoiding replay attacks. If it's
stored in MD5 format on the server, it can't request it with a different
salt every time (how would it compare them?), so you can just replay the
MD5 transmission.

The other way, though, a compromise of the database would mean a
compromise of all the passwords.

So it definitely would be helpful to have an answer to your question in
with the description of the authentication types, so you could choose
intelligently based on what you consider to be more likely risks.

7.3 stores encrypted MD5 passowords in database (7.2 it is optional).
We send random salt to client and client double-MD5 encrypts, so
playback will not work --- best of both worlds.

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

Bruno Wolff III wrote:

On Mon, Dec 02, 2002 at 18:44:03 -0800,
"j.random.programmer" <javadesigner@yahoo.com> wrote:

5) There is no way to grant permissions on all tables
within a database to some user. You have to grant
permissions on each table one-by-one. What I want
to do (and mysql allows this) is something like:

GRANT ALL on foodb.* to user_bar;

You can get the list of tables from the system catalog and then issue
grant statements for them. You could write a function to do this,
do it in an application or write sql query output to a script which
you then execute.

For example (not heavily tested!)

CREATE OR REPLACE FUNCTION grant_all(text) RETURNS TEXT AS '
DECLARE
rel record;
sql text;
BEGIN
FOR rel IN SELECT pg_catalog.quote_ident(c.relname) AS relname FROM
pg_catalog.pg_class c WHERE c.relkind = ''r'' AND c.relnamespace NOT IN
(select oid from pg_catalog.pg_namespace where nspname like ''pg\_%'') AND
pg_catalog.pg_table_is_visible(c.oid) LOOP
sql := ''grant all on '' || rel.relname || '' to '' || $1;
RAISE NOTICE ''%'', sql;
EXECUTE sql;
END LOOP;
RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

create user foo;
select grant_all('foo');

HTH,

Joe

On Tue, 2002-12-03 at 23:50, Bruce Momjian wrote:

Scott Lamb wrote:

That's disconcerting to me because I think it defeats the point of
sending MD5 signatures on the wire - avoiding replay attacks. If it's
stored in MD5 format on the server, it can't request it with a different
salt every time (how would it compare them?), so you can just replay the
MD5 transmission.

The other way, though, a compromise of the database would mean a
compromise of all the passwords.

So it definitely would be helpful to have an answer to your question in
with the description of the authentication types, so you could choose
intelligently based on what you consider to be more likely risks.

7.3 stores encrypted MD5 passowords in database (7.2 it is optional).
We send random salt to client and client double-MD5 encrypts, so
playback will not work --- best of both worlds.

So, if I understand it correctly :

- on the wire : no cleartext passwords, only doubly hashed + salted
passwords -> no replay possible (watch out for session hijacking though)
nor password sniffing

- in the database : no cleartext passwords are stored, but access to the
md5 hashed passwords is sufficient to get access to the database -
without really knowing the user's password - by using a modified client.

Is this correct ?

cheers
Tycho

Tycho Fruru wrote:

7.3 stores encrypted MD5 passowords in database (7.2 it is optional).
We send random salt to client and client double-MD5 encrypts, so
playback will not work --- best of both worlds.

So, if I understand it correctly :

- on the wire : no cleartext passwords, only doubly hashed + salted
passwords -> no replay possible (watch out for session hijacking though)
nor password sniffing

Right.

- in the database : no cleartext passwords are stored, but access to the
md5 hashed passwords is sufficient to get access to the database -
without really knowing the user's password - by using a modified client.

Right.

-- 
  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 Tue, 2002-12-03 at 21:39, Chris Boget wrote:

This is a great article. I found one problem, though, and while I poked around
a bit to find this, I couldn't. So could someone point me to where this:

http://www.ca.postgresql.org/devel-corner/docs/postgres/index.html

really is?

Go to www.postgresql.org; pick an HTTP mirror. Follow the links to the
developers' corner and then to the documentation.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Behold, happy is the man whom God correcteth.
Therefore despise thou not the chastening of the
Almighty." Job 5:17

I'm new to the community as well, a couple of responses to your points and
my impressions below.

----- Original Message -----
From: "j.random.programmer" <javadesigner@yahoo.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, December 02, 2002 9:44 PM
Subject: [GENERAL] Postgresql -- initial impressions and comments

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

Never, ever run anything under root if at all possible. Its just
baddddd..... Trust the user? Your joking right? :) I've been around too
many Lusers (as my friend would say) to trust any of em.

3) The documentation needs to be radically improved.
The mysql docs are much more comprehensive. Postgres
wins hands down in the database internals
documentation
(mysql doesn't have any) but loses in the userland
documentation.

Recently i've evaluated both mysql and postgresql for a project myself
and a few friends are working on. There are areas in both sets of
documentation that could use a little work but in general I find both
adequate. Actually the only thing that puts mysql's on the same level as
postgres's, is the user comments.

4) The auto-increment ("serial") fields are very
badly documented.

Normally, I want to say something like:

INSERT into foo values (null, 'a', 'b',...)

INSERT into foo
(field_a, field_b,...) values ('a', 'b', ...)

The first one is bad form.. If your schema ever changes your
application will break but i'm sure you've gotten many responses on this.

I setup 'serial' columns in both mysql and postgres with ease, didn't see
any issues with the docs for either.

With our project we decided to go with Postgres because it has an extensive
feature set.

L8r all.

Dave Best wrote:

Never, ever run anything under root if at all possible. Its just
baddddd..... Trust the user? Your joking right? :) I've been around too
many Lusers (as my friend would say) to trust any of em.

not to mention that we also need to distrust the postgresql developers
:-) should the postgresql code contained a nasty bug (say something that
could overwrite a random file on the filesystem), then running the
postgresql daemon as non-root user would limit the damage that can be done.

--
dave

j.random.programmer wrote:

I am a long time MySQL/Innodb user. I just installed
postgres 7.3 on my linux box and thought I'd post
some comments:

are you trolling? i suspect you are, but here's my comment anyway :-)

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

i could understand your wish to run the daemon as root (for whatever
twisted reason, only god knows), but i could not fathom your argument.
"it's an overkill"? wouldn't running *as root* be an overkill? do you
run mysql as root?

there is no good reason for running as root, so i think it's okay to
forbid it altogether. many programs forbid it too.

4) The auto-increment ("serial") fields are very
badly documented.

since you came from the mysql world, this is understandable. mysql
doesn't have a "serial". it only has "autoincrement field." a
convenience, at best. not very flexible.

Well, having played with postgres for the first time,
these were the initial (after 4-5 hours) points that
came up. But overall, it looks and feels like a solid
product. The hot dump is a nice feature - I can
dump the db without shutting it down - innodb in
contrast charges $$ for this feature.

well, i agree with you that postgresql is stable and solid. one of my
biggest gripes with postgresql is that it's not "lightweight" or
"simple" enough. some things are awkward/a chore to do in postgresql,
while simple in other dbms's. in mysql, i can shut down mysqld, copy the
database directory to another installation, and be done with it. ms
access and interbase/firebird (and sql server 2000 i think?) are even
simpler: just copy the database file. i can't do these things with
postgresql.

--
dave

On 3 Dec 2002 at 22:38, David Garamond wrote:

well, i agree with you that postgresql is stable and solid. one of my
biggest gripes with postgresql is that it's not "lightweight" or
"simple" enough. some things are awkward/a chore to do in postgresql,
while simple in other dbms's. in mysql, i can shut down mysqld, copy the
database directory to another installation, and be done with it. ms

And why it will not work with postgresql? Shutdown the database, backup the
data directory and restart with another option.

Just because you can backup database online does not mean you can not do a off-
line backup.

Correct me if I am wrong..

Bye
Shridhar

--
Campbell's Law: Nature abhors a vacuous experimenter.

Hi,

--On Mittwoch, 4. Dezember 2002 12:18 +0530 Shridhar Daithankar
<shridhar_daithankar@persistent.co.in> wrote:

On 3 Dec 2002 at 22:38, David Garamond wrote:

well, i agree with you that postgresql is stable and solid. one of my
biggest gripes with postgresql is that it's not "lightweight" or
"simple" enough. some things are awkward/a chore to do in postgresql,
while simple in other dbms's. in mysql, i can shut down mysqld, copy the
database directory to another installation, and be done with it. ms

And why it will not work with postgresql? Shutdown the database, backup
the data directory and restart with another option.

Just because you can backup database online does not mean you can not do
a off- line backup.

Correct me if I am wrong..

At least it worked for me :-)

Regards
Tino

On 4 Dec 2002 at 2:18, David Garamond wrote:

sorry, i was actually talking about moving one database from one
installation to another, not about offline backup. or can i do it with
postgresql too? afaik, each database has a unique id (unique to the
local installation/the 'template1' database). what if this id collides
with the target installation?

Why bother? Just dump the single database on installation A and import it on
installation B..

Easy no?

Bye
Shridhar

--
Hand, n.: A singular instrument worn at the end of a human arm and commonly
thrust into somebody's pocket. -- Ambrose Bierce, "The Devil's Dictionary"

Tino Wildenhain wrote:

well, i agree with you that postgresql is stable and solid. one of my
biggest gripes with postgresql is that it's not "lightweight" or
"simple" enough. some things are awkward/a chore to do in postgresql,
while simple in other dbms's. in mysql, i can shut down mysqld, copy the
database directory to another installation, and be done with it. ms

And why it will not work with postgresql? Shutdown the database, backup
the data directory and restart with another option.

Just because you can backup database online does not mean you can not do
a off- line backup.

sorry, i was actually talking about moving one database from one
installation to another, not about offline backup. or can i do it with
postgresql too? afaik, each database has a unique id (unique to the
local installation/the 'template1' database). what if this id collides
with the target installation?

--
dave