Thread: 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
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.
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.
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
> 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
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
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"
On Tuesday 03 Dec 2002 11:23 pm, Joe Conway wrote: > 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; > > > > For example (not heavily tested!) [snipped plpgsql grantall function from Joe] There are two examples on how to do this in my Postgresql Notes at techdocs.postgresql.org (one shellscript, one plpgsql) and I think it's also a utility in Roberto's cookbook. Been there 12 months at least. -- Richard Huxton
> > 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.
What's the difference between the two? Autoincrement and serial?
Unless I missed something in the docs, it didn't appear that there
was any difference.
Chris
Chris Boget wrote:
>>>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.
>
>
> What's the difference between the two? Autoincrement and serial?
> Unless I missed something in the docs, it didn't appear that there
> was any difference.
Hi Chris,
Do you have the ability to view Flash presentations?
If so, then serial/sequences are pretty well illustrated here:
http://techdocs.postgresql.org/college/001_sequences/index.php
Hope that helps.
:-)
Regards and best wishes,
Justin Clift
> Chris
>
>
> ---------------------------(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
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
Thanks to everyone who responded to my initial post. Here are some followup comments: --------------------------------------- Original: 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. Other comments: Many folks gave examples on how to do this via shell or plpgsql script. Followup: My suggestion is: please add a link from the command reference documentation page for the GRANT command to the appropriate page on technotes.postgresql.com that talks about this issue. --------------------------------------- Original: INSERT into foo values (null, 'a', 'b',...) does not work if the first column is serial. (null cannot be inserted into serial type). Other comments: - Even though mysql allows it, 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 - NULL is not the same as DEFAULT. Treating it the same is not correct. Followup: Please mention the ability to use 'DEFAULT' in the documentation for the serial type. --------------------------------------- Original: My wanting to run postmaster as non-root Other comments: - there is no good reason for running as root, so it's ok to forbit it. - This feature (mysql mis-feature?) isn't likely to change any time soon. Followup: It's clear that everyone thinks this is a bad idea. I have to disgree here. I should be allowed to do what I want even if it's a security risk as long as that risk is known/explained. It's _not_ an issue if I bind the daemon to a ip/interface which is guaranteed private and non-routable (such as 192.168.x.x) and non-forwardable. Then there is no risk. But it's not really a significant issue either way - creating a new user/group is not a big deal. --------------------------------------- Original: About lack of documentatin for MD5 password encryption (which btw, is the default encryption for all passwords when 7.3 is compiled out of the box). Does the JDBC driver do this automatically ? Other comments: Yes. You need a fairly late model jdbc driver. Followup: I am not seeing this with latest compiled JDBC driver which I downloaded yesterday (for JDK 1.4 and postgres 7.3). There is also no mention of this in the JDBC driver's documentation. Essentially, I cannot connect using JDBC unless I change the connect mode to "trust". --------------------------------------- Original: The documentation needs to be radically improved. Other comments: - I usually find everything i need in the Reference Guide, if not it's in the Users Guide. - What needs to be changed from a "newbie" perspective ? Followup: I am not sure I can point to any one thing that needs to be changed. But somehow the userland (not internals) documentation "feels" incomplete, compared to the extensive mysql docs. Maybe the docs are a) too spread out b) don't have enough examples c) are not totally up-to-date --------------------------------------- Original: No docs for importing/exporting comma or tab delimited files from/to tables ? Other comments: 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.html Followup: I was wrong about this. As the followup above stated, the documentation does cover this. --------------------------------------- Overall: I must say that I found the postgres community very helful and responsive. In the end, that's - by far - the most important thing. If I was to undertake a production postgres project, I would feel comfortable for this very reason. I did also find some JDBC specific issues that I posted to the JDBC list. Meanwhile, I am going to keep playing with postgres and make sure that my programs work with both mysql and postgresql. Best regards, --j __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
j.random.programmer <javadesigner@yahoo.com> wrote: > Thanks to everyone who responded to my initial post. > Here are some followup comments: > [snip] > Overall: > > I must say that I found the postgres community very > helful and responsive. In the end, that's - by far - > the most important thing. If I was to undertake a > production postgres project, I would feel comfortable > for this very reason. > Well good luck in exploring postgresql! I come from a mysql background myself and i think the developer community around postgresql is very good and they (/me/we) almost always respond quickly to questions and fixes bugs in no-time. After awhile you'll start thinking the postgresql way on databases and then you'll be even happier in choosing the most advanced opensource database there is :) Magnus
Thanks for your summary and very constructive criticism. I've also found the postgresql community to be very helpful and supportive. (Thanks everyone!) I want to add something with regard to security and running as root. First, if postgresql runs as root and is bound to an internal interface, someone getting root access to the box through a postgresql exploit would have access to everything on that box, including other interfaces. Second, many security threats and attacks are launched from within companies, or from within corporate firewalls. You need to guard against these as well. This could happen either because an employee or contractor decides to work against the company for whatever reason, or because another box on the LAN is compromised from outside, and is then used to attack other servers within the LAN. You can never have too many layers of security, especially when you get an extra layer for the low low price of adding another user and group. :-) Wes Sheldahl "j.random.programmer" <javadesigner@yahoo.com>@postgresql.org on 12/04/2002 02:14:37 PM Sent by: pgsql-general-owner@postgresql.org To: pgsql-general@postgresql.org cc: Subject: Re: [GENERAL] Postgresql -- initial impressions and comments Thanks to everyone who responded to my initial post. Here are some followup comments: --------------------------------------- Original: 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. Other comments: Many folks gave examples on how to do this via shell or plpgsql script. Followup: My suggestion is: please add a link from the command reference documentation page for the GRANT command to the appropriate page on technotes.postgresql.com that talks about this issue. --------------------------------------- Original: INSERT into foo values (null, 'a', 'b',...) does not work if the first column is serial. (null cannot be inserted into serial type). Other comments: - Even though mysql allows it, 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 - NULL is not the same as DEFAULT. Treating it the same is not correct. Followup: Please mention the ability to use 'DEFAULT' in the documentation for the serial type. --------------------------------------- Original: My wanting to run postmaster as non-root Other comments: - there is no good reason for running as root, so it's ok to forbit it. - This feature (mysql mis-feature?) isn't likely to change any time soon. Followup: It's clear that everyone thinks this is a bad idea. I have to disgree here. I should be allowed to do what I want even if it's a security risk as long as that risk is known/explained. It's _not_ an issue if I bind the daemon to a ip/interface which is guaranteed private and non-routable (such as 192.168.x.x) and non-forwardable. Then there is no risk. But it's not really a significant issue either way - creating a new user/group is not a big deal. --------------------------------------- Original: About lack of documentatin for MD5 password encryption (which btw, is the default encryption for all passwords when 7.3 is compiled out of the box). Does the JDBC driver do this automatically ? Other comments: Yes. You need a fairly late model jdbc driver. Followup: I am not seeing this with latest compiled JDBC driver which I downloaded yesterday (for JDK 1.4 and postgres 7.3). There is also no mention of this in the JDBC driver's documentation. Essentially, I cannot connect using JDBC unless I change the connect mode to "trust". --------------------------------------- Original: The documentation needs to be radically improved. Other comments: - I usually find everything i need in the Reference Guide, if not it's in the Users Guide. - What needs to be changed from a "newbie" perspective ? Followup: I am not sure I can point to any one thing that needs to be changed. But somehow the userland (not internals) documentation "feels" incomplete, compared to the extensive mysql docs. Maybe the docs are a) too spread out b) don't have enough examples c) are not totally up-to-date --------------------------------------- Original: No docs for importing/exporting comma or tab delimited files from/to tables ? Other comments: 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.html Followup: I was wrong about this. As the followup above stated, the documentation does cover this. --------------------------------------- Overall: I must say that I found the postgres community very helful and responsive. In the end, that's - by far - the most important thing. If I was to undertake a production postgres project, I would feel comfortable for this very reason. I did also find some JDBC specific issues that I posted to the JDBC list. Meanwhile, I am going to keep playing with postgres and make sure that my programs work with both mysql and postgresql. Best regards, --j __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Wed, 4 Dec 2002 wsheldah@lexmark.com wrote: > > Thanks for your summary and very constructive criticism. I've also found > the postgresql community to be very helpful and supportive. (Thanks > everyone!) > > I want to add something with regard to security and running as root. > First, if postgresql runs as root and is bound to an internal interface, > someone getting root access to the box through a postgresql exploit would > have access to everything on that box, including other interfaces. Second, > many security threats and attacks are launched from within companies, or > from within corporate firewalls. You need to guard against these as well. > This could happen either because an employee or contractor decides to work > against the company for whatever reason, or because another box on the LAN > is compromised from outside, and is then used to attack other servers > within the LAN. You can never have too many layers of security, especially > when you get an extra layer for the low low price of adding another user > and group. :-) also, think of a mistake with the copy command: copy table to /etc/passwd; or copy table to /dev/hda; As postgres, no big deal. Access denied. As root, kaboom.
Hi, --On Mittwoch, 4. Dezember 2002 11:14 -0800 "j.random.programmer" <javadesigner@yahoo.com> wrote: > Thanks to everyone who responded to my initial post. > Here are some followup comments: > > --------------------------------------- > Original: > 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. Not out of the box. But if you do it wise, you would make groups and assign group permissions to the tables during creation. Then you only have to add or remove a user to or from one of the groups. Sure, you can do a mass-assign too. Get the list of tables and use it to assign the user too. > Other comments: > Many folks gave examples on how to do this > via shell or plpgsql script. > > Followup: > My suggestion is: please add a link from > the command reference documentation page for the GRANT > command to the appropriate page on > technotes.postgresql.com that talks about this issue. > > --------------------------------------- > Original: > INSERT into foo values (null, 'a', 'b',...) does not > work if the first column is serial. (null cannot be > inserted into serial type). > > Other comments: > - Even though mysql allows it, 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 > - NULL is not the same as DEFAULT. Treating it the > same is not correct. > > Followup: > Please mention the ability to use 'DEFAULT' in the > documentation for the serial type. Its better to explicitely specify the columns to insert into anyway. The lazyness of not doing so will pay out if you change the table columns for any reason. .... > Original: > The documentation needs to be radically improved. > > Other comments: > - I usually find everything i need in the Reference > Guide, if not it's in the Users Guide. > - What needs to be changed from a "newbie" perspective > ? > > Followup: > I am not sure I can point to any one thing that needs > to be changed. But somehow the userland (not > internals) documentation "feels" incomplete, compared > to the extensive mysql docs. Maybe the docs are a) too > spread out b) don't have enough examples c) are not > totally up-to-date > --------------------------------------- I was new to postgresql and even a little bit green on SQL by beginning of the year. I dont find the documentation such bad as you describe. Of yourse ymmv. Regards Tino Wildenhain
j.random.programmer wrote: > Followup: > It's clear that everyone thinks this is a > bad idea. I have to disgree here. then you need to complain to the apache software foundation as well. they don't let you run httpd as root. and most probably you need to complain about qmail, djbdns, pod2html, etc. > I should be allowed > to do what I want even if it's a security risk as long > as that risk is known/explained. well, you are allowed. just modify the source code. i bet it's not far than a single line or two. > It's _not_ an issue > if I bind the daemon to a ip/interface which is > guaranteed private and non-routable (such as > 192.168.x.x) and non-forwardable. Then there is no > risk. then clearly you do _not_ know the risks/issues then. you should thank the postgresql developers for not letting your daemon run as root. :-) -- dave
On Wed, Dec 04, 2002 at 11:14:37AM -0800, j.random.programmer wrote: > --------------------------------------- > Original: > My wanting to run postmaster as non-root > > Other comments: > - there is no good reason for running as root, so > it's ok to forbit it. > - This feature (mysql mis-feature?) isn't likely to > change any time soon. > > Followup: > It's clear that everyone thinks this is a > bad idea. I have to disgree here. I should be allowed > to do what I want even if it's a security risk as long > as that risk is known/explained. It's _not_ an issue > if I bind the daemon to a ip/interface which is > guaranteed private and non-routable (such as > 192.168.x.x) and non-forwardable. Then there is no > risk. But it's not really a significant issue either > way - creating a new user/group is not a big deal. I think you're looking at it the wrong way. As a postgresql developer, by preventing people running the daemon as root, they can never be blamed for accedently trashing someone's system, since they can't write to most of it. Any of the modules loaded by postgres don't need to audited as strongly. Bugs in the COPY command have no chance of destroying important logs. Since there is no good reason to run the server as root, it's better not to. The reduced risk of remote hacking really seems like a secondary benefit (to me anyway). -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.