Re: [HACKERS] Possible bug in update?
On Wed, Aug 27, 2003 at 09:17:28PM +0200, Markus Kr?utner wrote: At the end of several bulk inserts the following statements get executed: begin update pom_c2996785 set p3=3268837857008 where p3=34804837857008 update pom_c2996795 set p3=3268837857008 where p3=34804837857008 update pom_c2996804 set p3=3268837857008 where p3=34804837857008 commit not overly clear from the debug statements, but are all the above lines also ended with a semi-colon? -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
On Thu, Aug 28, 2003 at 03:56:18PM -0400, Tom Lane wrote: Sean Chittenden [EMAIL PROTECTED] writes: Hrm, well, given things just went to beta2, I'm going to bump postgresql-devel to beta2 and include this patch for now, however, I'm going to explicitly requests that people who have problems or successes with beta2 on FreeBSD ask me before possible reporting problems with a platform specific alteration. You do realize that you'll be forcing initdbs on people if you blithely add and remove such a patch? I think we forced an initdb for beta2 for other reasons, but I'm hoping to avoid any more in the 7.4 branch. if it does in fact give performance for FreeBSD (and possibly others), at least make it a build time option. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] hacker help: PHP-4.2.3 patch to allow restriction of database access
On Sat, Sep 28, 2002 at 01:08:36PM +0200, Peter Eisentraut wrote: Jim Mercer writes: ideally, i'd like to have users-per-database, as opposed to the global model we have now. That's in the works. Some form of this will be in 7.3. cool! if we are willing to modify libpq to support a white-list, then what you are suggesting is quite possible. How would you store such a list and prevent users from simply unsetting it? the list is something determined by the client, effectively, in this scenario. basically, i'm just looking at a libpq function that will take a white-list, and only allow connections through PQconnect() based on that list. the reasoning for this is that postmaster has no ability to differentiate between incoming sessions, and as such, storing the list in the server makes no sense, the server won't know how to apply the list. in the scenario i'm working with, apache/php/libpq are safe from change by the users. apache has the ability to pass values through php to libpq which the user cannot change. so apache would tell libpq what tables _this_ instance of apache/php/libpq can access. simply using environment variables is not good enough, as the user can change their values in their php scripts. i suspect the php-dev people are unhappy with my patch because it is including logic (ie. parsing the white-list) which they don't think php should be responsible for. From my reading of the discussion, I think they have not understood that the PostgreSQL server has no way to distinguish different virtual host identities. I think your feature is quite reasonable, if you list users instead of databases. well, for my purposes, it is _databases_ i'm more concerned about. each virtual host should be restricted to specific databases. this way each user is entitled to mess up their own world, but not mess with other people's. as it currently stands, virtual hosts can trample all over other databases, and with the nature of a single uid for all apache/php/libpq proceses, they are generally doing it with the same pgsql user. vigilience over the user-level permissions is not something i trust the users to do. 8^( -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] hacker help: PHP-4.2.3 patch to allow restriction of database access
On Sat, Sep 28, 2002 at 03:57:27PM +0200, Michael Paesold wrote: Jim Mercer [EMAIL PROTECTED] wrote: as it currently stands, virtual hosts can trample all over other databases, and with the nature of a single uid for all apache/php/libpq proceses, they are generally doing it with the same pgsql user. I haven't followed the whole thread, so perhaps I missed something. But why not just use password authentication to the database with a different user for each database? Ok, one has to store the plain-text passwords in the php files. You have to protect your users from reading each others files anyway; this can be done. that can be done, but plain-text passwords are not good. also, it doesn't stop users from cruising other databases for unprotected data. my patch will control that, at least in the context of apach/php/libpq. At least you can set up different users per database, so that it doesn't matter if the proposed restriction setting is by database or by user. most of the databases have one user, that of the httpd process. from what i've seen, this is fairly standard with virtual hosting. until we have per-database users, generally what you end up doing is creating a per-database user/password table, and then write applications that control things based on that table, as opposed to the system table. this means that all of the tables in a database need to be read/write by one central user. i've always found this hokey, but necessary. the up-coming per-table userlist will help this alot. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] hacker help: PHP-4.2.3 patch to allow restriction of database access
the following was sent to the php developer's list, and they came back with: Isn't it generally better (where better means more secure, efficient, and easily maintained) to handle database access control using PostgreSQL's native access mappings? I would think so, and IMHO, that's where pgsql access control belongs, with pgsql. as best i can understand, there is no way to get apach/php/pgsql configured (using PostgreSQL's native access mappings) that would disallow php code in one virtual host from connecting to any database on the system. i understand that on a user level, we can control which tables they have access to (disregarding that almost all access will be by the web user). can anyone make some valid arguments for/against the patch in php? or any suggestions on how to do it in pgsql? - Forwarded message from Jim Mercer [EMAIL PROTECTED] - Date: Thu, 26 Sep 2002 14:54:45 -0400 From: Jim Mercer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: PHP-4.2.3 patch to allow restriction of database access the patch is at: ftp://ftp.reptiles.org/pub/php/php-pgsql.patch this patch adds the config variable pgsql.allowed_dblist by default it has no value, meaning all databases are accessible it can contain a colon delimited list of databases that are accessible. if the database accessed is not in the list, and the list is not null, then an error is returned as if the database did not exist this patch is relative to php-4.2.3 this function would be very useful to apache/virtual hosting. i have tested with the following in my apache httpd.conf: Directory /home/www/htdocs/jim php_admin_value pgsql.allowed_dblistjim:billing /Directory although it can be accomplished by other means, setting the variable to a value of : effectively locks the code out of pgsql. also, a special tag of -all- will allow access to all databases. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] - End forwarded message - -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] hacker help: PHP-4.2.3 patch to allow restriction of database access
On Fri, Sep 27, 2002 at 11:15:35AM +1000, Gavin Sherry wrote: On Thu, 26 Sep 2002, Jim Mercer wrote: I would think so, and IMHO, that's where pgsql access control belongs, with pgsql. I totally disagree. It is a language level restriction, not a database level one, so why back it into Postgres? Just parse 'conninfo' when it is pg_(p)connect() and check it against the configuration setting. which is effectively what my code does, except i was lazy, and i let the connection proceed, then check if PQdb() is in the auth list, and fail if it isn't. (i figured that way if there was any silliness in the conninfo string, PQconnect would figure it out). The patch seems fine. I am unsure as to how useful it is. system(/usr/local/pgsql/bin/psql -U jim -c \select 'i got in';\ template1); that wouldn't work so well in safe_mode. which is the area i'm playing with. maybe not _totally_ secure, but much moreso than nothing. and retricting virtual hosts to their own data sets relieves me of worry about GRANT all ON blah TO public;. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] hacker help: PHP-4.2.3 patch to allow restriction of database access
On Fri, Sep 27, 2002 at 12:06:43PM +1000, Gavin Sherry wrote: On Thu, 26 Sep 2002, Jim Mercer wrote: maybe not _totally_ secure, but much moreso than nothing. I was basically just suggesting that its effect needs to be documented. This needs to be used in conjunction with other forms of security documentation? what's that? 8^) -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] hacker help: PHP-4.2.3 patch to allow restriction of database access
On Thu, Sep 26, 2002 at 11:42:44PM -0400, Tom Lane wrote:
Jim Mercer [EMAIL PROTECTED] writes:
as best i can understand, there is no way to get apach/php/pgsql configured
(using PostgreSQL's native access mappings) that would disallow php code
in one virtual host from connecting to any database on the system.
Betraying my ignorance of PHP here: what does a server supporting
multiple virtual hosts look like from the database's end? Can we
tell the difference at all between connections initiated on behalf
of one virtual host from those initiated on behalf of another?
normally (in my experience) php is linked into apache, and pgsql is linked into
php.
apache runs as the same user (unless you use suexec and a cgi version of php).
pgsql's knowledge of the php process is only what is passed on by the user.
since there is no IP addr specific to the process, we can't easily use
host-based authentication.
for domain sockets, pgsql only gets the UID of the httpd process.
since all of the virtual hosts are run by the same uid, there is no way
to differentiate between the virtual hosts.
one could attempt to use a specific username and hardcoded password, but
that leaves the password in plain text in the php code.
and that does not stop someone from writing code to browse the available
databases for tables set with GRANT ALL ON blah to PUBLIC;.
my patch is an attempt to put an immutable list of databases in the apache
config (safe from modification by normal users). and to have PQconnect()
check against that list before allowing access. the list would be specific
to a virtual host (and/or the directort hierarchy of the pages).
it is possible to pass such a list to pgsql through environment variables,
but those can be overridden by users.
the php-dev people are giving me a hard time saying that this level of
security should be managed internally by pgsql.
i'm trying to explain to them that it isn't, and that my patch allows this
security to happen.
if libpq had an additional facility where PQconnect checked against a list
passed to it in some fashion, then we could probably just pass that through
in the php modules, and they'd probably be more content with that as it is
just part of the pgsql API.
i'm thinking something like a wrapper function like:
PGconn *PQconnect_restricted(char *conninfo, char *restricted_list)
{
// break out conninfo
...
if (restricted_list != NULL) {
// check to see if dbName is in the list
if (not in list) {
fail as if dbName did not exist
}
}
return(PQconnect(conninfo);
}
(i'm sure someone more familiar with the code could come up with a more
refined way of doing this)
this patch adds the config variable pgsql.allowed_dblist
by default it has no value, meaning all databases are accessible
it can contain a colon delimited list of databases that are accessible.
Seems like this hard-wires a rather narrow view of what sorts of
protection restrictions you need. Might I suggest instead that
an appropriate config variable would be a list of Postgres user ids
that the virtual host is allowed to connect as? Then the database's
usual protection mechanisms could be used to allow/disallow connection
to particular databases, if that's what you want. But this does more:
it lets different virtual hosts connect to the same database as
different users, and then access within that DB can be controlled using
the regular Postgres access-control mechanisms.
ideally, i'd like to have users-per-database, as opposed to the global
model we have now. i'm tired of maintaining seperate pgsql userlists and
application userlists. probably a pipe dream, but, well, there you are. 8^)
if we are willing to modify libpq to support a white-list, then what you
are suggesting is quite possible.
to satisfy the php-dev people, we just need to extend the API to require/allow
such a white-list to be processed.
passing the white-list from httpd.conf - php - libpq is an easy enough
tweak.
i suspect the php-dev people are unhappy with my patch because it is including
logic (ie. parsing the white-list) which they don't think php should be
responsible for.
personally, i think such an attitude is too rigid, but i'm also thinking a
white-list mechanism would be useful in other contexts as well.
--
[ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ]
[ I want to live forever, or die trying.]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] PostgreSQL mission statement?
On Thu, May 02, 2002 at 08:15:15AM -0400, mlw wrote: Jean-Michel POURE wrote: Le Jeudi 2 Mai 2002 01:59, David Terrell a écrit : Provide a really good database and have fun doing it PostgreSQL Community is commited to providing Humanity with the best multi-purpose, reliable, open-source and free database system. The PostgreSQL community is committed to creating and maintaining the best, most reliable, open-source multi-purpose standards based database, and with it, promote free and open source software world wide. Who's that? Anyone disagree? why does it have to be THE BEST ? that is insulting to the other projects like MySQL which while competitors are also a valid and useful benchmark for features, performance and keeping the postgresql community on its collective toes. postgresql is not THE BEST in all applications, so calling it that is inviting derision and pointless arguments. i'd go with: The PostgreSQL community is committed to creating and maintaining a very reliable, open-source, multi-purpose, standards-based database, and encouraging participation in open-source usage and development world wide. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL mission statement?
On Thu, May 02, 2002 at 08:43:04AM -0400, mlw wrote: Jim Mercer wrote: why does it have to be THE BEST ? that is insulting to the other projects like MySQL which while competitors are also a valid and useful benchmark for features, performance and keeping the postgresql community on its collective toes. This is interesting, a mission statement isn't necessarily about what is but about what we want to do, what it is that we intend to do, i.e. our mission. It is vital that a mission statement contain the superlatives. Mediocrity has no place here. I don't know about you, but I want PostgreSQL to be the best, be THE most reliable. Omitting best or most from the statement means that we should all just give up now, because PostgreSQL is pretty damn good already. i think a mission statement full of boastfulness is just a sound bite, and will be dismissed as such. if you want the mission statement to have an impact, then it needs to be acceptable not only to those who fully embrace it, but also acceptable to those who will respect the project from a distance. otherwise its not a mission statement, its akin to a corporate cheer. ( i'm picturing Steve Balmer's superlative exhaltations to the converted http://www.ntk.net/ballmer/mirrors.html ) -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL mission statement?
On Thu, May 02, 2002 at 01:43:39PM +0100, Nigel J. Andrews wrote: I like: We'll store your data; if we think it'll be an interesting enough diversion for us. gets my vote. 8^) -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL mission statement?
[ please try not to take this too seriously ] On Thu, May 02, 2002 at 01:11:41PM -0300, Marc G. Fournier wrote: altho in most contexts, I would agree with Jim as to the use of 'The Best', for any mission statement to say anything other then that, IMHO, shows a lack of commitment ... I agree with mlw on this one, the mission statement is what we are *striving* for ... where we eventually want to get to ... if we aren't The Best, then there is someone better then us that we have to work that much harder to become better then ... that's right, we have to work harder to STOMP OUT ALL COMPETITION! in fact, we should stop giving free access to the source, as our competitors might use the code to make their product better than ours. if we aren't THE BEST, then all those stock options are worthless! we could increase our chances of being the best by infiltrating the CVS tree of MySQL and the other projects stealing our thunder and injecting bugs into their code. i mean, if we want to be THE BEST, why should we stop at mere rhetoric? to be THE BEST, you need to dominate, good quality design and code are not the complete recipe for being THE BEST. THE BEST implies that no-one else compares, and we can completely demoralize the competition, thereby eliminating the competition. in all seriousness, i think that this attitude of being THE BEST goes against the philosophy of Open Source. if your source is open and available for modification/improvement/localization, then there will always be a chance for someone to run with it and make improvements. what is THE BEST Unix-based system? Linux? Debian? RedHat? FreeBSD? NetBSD? OpenBSD? Solaris? Minix? Qnix? if any of them claimed to be THE BEST Unix, we'd all laugh. none of them are THE BEST, but all of them strive to be as good as they can make them, and all of them borrow from each other in order to make their version better in the eyes of their audience. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL mission statement?
On Thu, May 02, 2002 at 08:41:30PM -0400, mlw wrote: A mission statement is like a tie. straw vote! who on the list wears ties? -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL mission statement?
On Thu, May 02, 2002 at 09:14:03PM -0400, mlw wrote: Jim Mercer wrote: On Thu, May 02, 2002 at 08:41:30PM -0400, mlw wrote: A mission statement is like a tie. straw vote! who on the list wears ties? How many people who make IT decisions wear ties? too many. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PostgreSQL mission statement?
On Thu, May 02, 2002 at 09:45:45PM -0400, mlw wrote: Jim Mercer wrote: On Thu, May 02, 2002 at 09:14:03PM -0400, mlw wrote: Jim Mercer wrote: On Thu, May 02, 2002 at 08:41:30PM -0400, mlw wrote: A mission statement is like a tie. who on the list wears ties? How many people who make IT decisions wear ties? too many. I'm sorry I started this thread. don't be sorry. i'm not big on wearing the corporate suit, be it physically, or figuratively. that's my opinion, and i'm stating it. your opinion differs, and that's fine. i've had to do the corporate mission statement dance, as well as a bunch of other hokey crap that didn't matter squat to the bottom line due to the fact that the execs read some magazine article or attended some Tony Robbins -esque motivational session. when i hear mission statement and quality circle and internal customer, i cringe. if the corporate management doesn't want to buy into the Open Source concept, fuck 'em. i've had a number of installations where due to management panic to get something working, it was implemented using Open Source. Only to have a perfectly good system replaced with real software when management finds out 6 months later that it is using Open Source. i have had successes in getting Open Source into corporate environments, but only after battling mega-politics with CIO/CFO and MSCE IT managers who only want to see Microsoft or Sun Solaris solutions. we did a project using FreeBSD and Samba to replace a number of highly unstable NT file/print servers. recently, some consultants (friends of the managing partners) said that it was a bad idea to use Public Domain software that was full of bugs and highly insecure. when we pointed out that the servers hadn't rebooted in 160 days, and that they were protected by both RFC1918 addressing and a firewall, the consultants backed off a bit. then they returned spouting the same full of bugs and highly insecure crap. now management is going to have them re-implement the network using the latest NT stuff. this is a long winded way of saying that my feeling is the type of MBA CFO/CIO that is impressed by a mission statement, is probably not going to buy into technology that isn't listed on NASDAQ. so, what's the harm in having one? probably not much, but to me it smells of corporate bullshit. -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: Week number
On Wed, Mar 14, 2001 at 04:54:54PM +0100, Zeugswetter Andreas SB wrote:
Unix day-of-week starts on Sunday, not Monday, which is what
date_trunc('dow',...) returns. Presumably this is modeled on the
traditional notion (at least in the US; I suspect this is true in most
European countries at least) of Sunday being "the first day of week".
Germany and Austria have Monday as first day of week, I think most of
Europe also.
it is all relative.
most western calendars that i have seen show "Sun Mon Tue Wed Thu Fri Sat".
the concept of "first" day of week is a bit muddied.
many christian-influenced places would consider Sunday to be the "first"
day of the week, but monday being the "first" business day of the week.
i have seen calendars which use "Mon Tue Wed Thu Fri Sat Sun", and i have
worked with people where saturday was the first day of business. and also
places where sunday is the first day of business.
so, suffice to say, there is no "proper" first day of the week.
as such, the unix day of week pegs sunday as day 0, your code should just
use that index. since almost all cultures have now adapted to a 7 day week
and a 365 day year, there shouldn't bee too much confusion.
--
[ Jim Mercer [EMAIL PROTECTED] ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ aka[EMAIL PROTECTED] +1 416 410-5633 ]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] off-topic: (sorta) freebsd - oracle, lightweight
i need to query some oracle tables from a freebsd system. is there a lightweight method to do this, or do i have no choice but to put in the Oracle Linux stuff and use their API's? -- [ Jim Mercer [EMAIL PROTECTED] +1 416 410-5633 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
Re: [HACKERS] Installation layout is still hazardous for shared prefixes
On Wed, Sep 27, 2000 at 10:53:43AM -0400, Tom Lane wrote: I think it would be a real bad idea to put the postmaster and postgres executables right in /usr/local/bin. Perhaps it is time to think about a separate 'sbin' directory for programs that aren't supposed to be invoked by normal users. Those two, initdb, initlocation, and ipcclean could certainly go to sbin, also pg_id, maybe the create/drop scripts if you feel those are admin-only. Perhaps using a private sbin directory could eliminate the issue of needing to rename stuff. generally there is a /usr/local/sbin or /usr/local/libexec for things like postgres and postmaster. at least on freebsd. if we are gonna go this route, i would prefer that we not have any "special" directories for postgres, other than the data directory. a layout of: /usr/local/bin /usr/local/include/pgsql /usr/local/lib /usr/local/libexec /usr/local/pgsql/data or /usr/local/pgsql the last one could also be /home/pgsql or whatever. -- [ Jim Mercer [EMAIL PROTECTED] +1 416 410-5633 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
