Re: Postgresql -- initial impressions and comments

  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