PostgreSQL 9.1 released

[Posted September 12, 2011 by corbet]

From:		Josh Berkus <josh-AT-postgresql.org>
To:		pgsql-announce-AT-postgresql.org
Subject:		[ANNOUNCE] PostgreSQL 9.1 Released
Date:		Mon, 12 Sep 2011 06:25:44 -0700
Message-ID:		<4E6E0858.5010406@postgresql.org>
Archive‑link:		Article

The PostgreSQL Global Development Group announces the release of
PostgreSQL 9.1.  This latest version of the leading open source database
offers innovative technology, unmatched extensibility, and new features
such as synchronous replication, K-Nearest Neighbor indexing, and
foreign data wrappers.

"PostgreSQL 9.1 provides some of the most advanced enterprise
capabilities of any open source database, and is backed by a vibrant and
innovative community with proven customer success.  PostgreSQL is well
positioned for building and running applications in the cloud," said
Charles Fan, Sr. VP R&D, VMware.

Responding to Users

Version 9.1 delivers several features which users have been requesting
for years, removing roadblocks to deploying new or ported applications
on PostgreSQL.  These include:

* Synchronous Replication: enable high-availability
  with consistency across multiple servers
* Per-Column Collations: support linguistically-correct
  sorting per database, table or column.
* Unlogged Tables: greatly improves performance for ephemeral data

"Heroku runs the largest PostgreSQL database-as-a-service in the world,"
said James Lindenbaum, Heroku co-founder. "The release of synchronous
data replication with 9.1 provides our customers with innovative new
ways of protecting mission-critical data, and validates PostgreSQL as
one of the fastest-moving datastores available."

Advancing the State of the Art

Our community of contributors innovates with cutting-edge features.
Version 9.1 includes several which are new to the database industry,
such as:

* K-Nearest-Neighbor Indexing: index on "distance" for
  faster location and text-search queries
* Serializable Snapshot Isolation: keeps concurrent transactions
  consistent without blocking, using "true serializability"
* Writeable Common Table Expressions: execute complex multi-stage
  data updates in a single query
* Security-Enhanced Postgres: deploy military-grade security
  and Mandatory Access Control

"OpenERP has always relied on the enterprise-class features of
PostgreSQL to provide a fast, reliable and scalable foundation for the
Business Applications supporting our customers' operations every day.
Data integrity in highly concurrent and transactional contexts is a
critical topic for us, and we're very enthusiastic about the new
Serializable Snapshot Isolation of PostgreSQL 9.1!"  said Olivier Dony,
OpenERP Community Manager.

Extending the Database Engine

PostgreSQL's extensibility enables users to add new functionality to a
running production database, and use them for tasks no other database
system can perform.  Version 9.1 adds new extensibility tools, including:

* Foreign Data Wrappers: attach and query other databases
  from PostgreSQL
* Extensions: easily create, load, and manage new database features

In PostgreSQL's 25th year of database development, our community
continues to advance database technology with every annual release.
Download version 9.1 now and experience the most advanced open source
database system in the world.

More information on PostgreSQL 9.1:
* Release notes
  http://www.postgresql.org/docs/9.1/static/release-9-1
* Presskit
  http://www.postgresql.org/about/press/presskit91
* Guide to 9.0:
  http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1

Download 9.1 now:
* Main download page:
  http://www.postgresql.org/download
* Source code:
  http://www.postgresql.org/ftp/source/v9.1.0
* One-click installer, including Windows installer:
  http://www.enterprisedb.com/products/pgdownload.do

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

               pgsql-announce-unsubscribe@postgresql.org

PostgreSQL 9.1 released

Posted Sep 12, 2011 16:36 UTC (Mon) by cmorgan (guest, #71980) [Link] (5 responses)

Went looking for benchmarks of the 'unlogged' tables vs. memcached or other in-memory databases but didn't see any. It would be interesting to see if Postgres has similar enough performance to allow admins to use for things like session information etc.

PostgreSQL 9.1 released

Posted Sep 13, 2011 1:24 UTC (Tue) by Cyberax (✭ supporter ✭, #52523) [Link] (4 responses)

Unlike memcached, unlogged tables are still persisted on disk. So they are not limited by RAM+swap.

PostgreSQL 9.1 released

Posted Sep 13, 2011 11:06 UTC (Tue) by Lennie (subscriber, #49641) [Link] (3 responses)

As I understand it, it is only persistent if the database server is shutdown normally.

In all other cases your data is lost (well, it will truncate the table on startup).

PostgreSQL 9.1 released

Posted Sep 13, 2011 17:20 UTC (Tue) by dlang (guest, #313) [Link] (2 responses)

the point he was making is that with a memory based table (including memcached) the entire table must be in ram at all times

with unlogged tables in postgres the table does not need to be held in memory at all times.

in both cases you do not have safety if the database crashes, but there are many cases where this is acceptable (a database of currently logged in sessions and associated data for example, if you crash people just login again and it's not a major problem)

It would be good to see benchmarks of this, I would expect that for the trivial queries that memcache can handle it will beat postgres, the question is by how much.

remember that unlogged tables still have all the power of postgres, they aren't just a key-value pair like memcached.

PostgreSQL 9.1 released

Posted Sep 13, 2011 19:22 UTC (Tue) by Lennie (subscriber, #49641) [Link] (1 responses)

I'm just a bit surprised by the design choice.

But maybe it is good to have more choice.

There is a lot of choice at the moment.

PostgreSQL 9.1 released

Posted Sep 13, 2011 22:07 UTC (Tue) by dlang (guest, #313) [Link]

I don't think that this is intended to be a direct competition to memcached and similar things.

instead this is a way to create temporary tables that you can re-generate easily and make them faster by doing away with some of the safety.

you still have all the power of SQL, including transaction related features. the only thing that you are giving up is safety in the face of a server crash. If you have a table that you can either reload from disk, or recreate from other data, getting a speed increase in using it can be worth the hassle of recreating it if the system crashes.

SEPostgres

Posted Sep 12, 2011 16:52 UTC (Mon) by dpquigl (guest, #52852) [Link] (37 responses)

I know someone was trying to make SEPostgres sound cool with the description "deploy military-grade security" but I really don't like it when people use those kinds of descriptions for any of the SE* products. Using terminology like this just furthers the opinions that people have that SELinux and other SE* products are only useful if you're a super secret government agency that has lots of sensitive data. In reality almost everyone can benefit from their use.

There are many really cool uses for SEPostgres that are not government related at all. I had met with Stephen Frost (Postgres Security Guy) and a bunch of the other Postgres developers in the last year to help make Kaigai's case for SEPostgres acceptance. In those meetings we came up with uses cases like PCI Compliance (credit card processing), and HIPA compliance as possible commercial applications of this technology. There are even use cases for this technology in web applications. Kaigai's work on SELinux+ for Apache allows him to control database access based on individual sites (Look up Kaigai's LAPP work). That usage is pretty cool because it means a vulnerability in one web application won't allow it to start reading tables of another web application or start creating tables and inserting data.

SEPostgres

Posted Sep 12, 2011 18:05 UTC (Mon) by Pc5Y9sbv (guest, #41328) [Link] (15 responses)

I understand the idea of trying to use SEPostgres for HIPAA or PCI privacy, but why would it be necessary for a multi-tenant web app? Why wouldn't you map each site to database roles having access to independent databases under the same postmaster?

SEPostgres

Posted Sep 12, 2011 19:31 UTC (Mon) by dpquigl (guest, #52852) [Link]

That is definitely a good way of isolating the applications from each other. The only issue there is that it binds access to a user identity by extension of assigning the role to an account. With SEPostgres you are binding the permissions to the application itself. This way if you manage to impersonate the other user within the context of the wrong application you don't get the privileges of that other user. You will get only the access defined for that particular web application. I'm not saying that you shouldn't use roles in addition to SEPostgres but roles alone don't bind the access to the application. There are other interesting usages but at the moment the version of SEPostgres merged into 9.1 is missing permission checking on DDL statements. You get permissions over DML statements (SELECT,INSERT,UPDATE,DELETE), and even execution of trusted procedures but for the moment we have to wait for permissions on DDL statements. The link below outlines more information on SEPostgres capabilities.

http://wiki.postgresql.org/wiki/SEPostgreSQL_Documentation

SEPostgres

Posted Sep 12, 2011 22:30 UTC (Mon) by SEJeff (guest, #51588) [Link] (13 responses)

Roles are Discretionary Access Control (DAC), SEpgsql is Mandatory Access Control (MAC). Google something like DAC vs MAC access control for a torrent of reasons why these are not the same.

Remember, security is like an onion. It works best in layers and tastes pretty good fried.

SEPostgres

Posted Sep 12, 2011 22:50 UTC (Mon) by nix (subscriber, #2304) [Link] (2 responses)

And if you eat too much of it, it gives you heart attacks.

The more I look at that analogy the better it gets.

SEPostgres

Posted Sep 12, 2011 23:09 UTC (Mon) by jamesmrh2 (guest, #31680) [Link] (1 responses)

Onions cause heart attacks?

I thought they were good for warding off evil.

SEPostgres

Posted Sep 12, 2011 23:15 UTC (Mon) by SEJeff (guest, #51588) [Link]

The vascular system doesn't like fried food's grease as much as your taste buds.

nix: Nice addition and so true!

SEPostgres

Posted Sep 12, 2011 23:27 UTC (Mon) by Pc5Y9sbv (guest, #41328) [Link] (9 responses)

In the given scenario of a multi-tenant web service, where the owner would be the system operator, isn't the distinction between MAC and DAC a bit elusive?

The "owner" of the database (or namespace), who would set RBAC policies, would be the sys op. The sys op would also be the one creating the SEPostgres policies. I think this is an important pragmatic fact.

SEPostgres

Posted Sep 13, 2011 12:49 UTC (Tue) by SEJeff (guest, #51588) [Link] (8 responses)

"""
In the given scenario of a multi-tenant web service, where the owner would be the system operator, isn't the distinction between MAC and DAC a bit elusive?
The "owner" of the database (or namespace), who would set RBAC policies, would be the sys op. The sys op would also be the one creating the SEPostgres policies. I think this is an important pragmatic fact.
"""

Hi, I think you're still completely missing the point of MAC. MAC does *not* replace DAC. MAC complements DAC. With SELinux in a full MLS strict enforcing mode, you can pretty much eliminate the concept of "root" or "sys op" entirely. What if someone in "rogue webapp A" manages to exploit a flaw and install a shell on your webserver and somehow gets root privileges with said shell? With SELinux that "root" shell could be as good as worthless and would still disallow access to data from "secure webapp B" in the proper enforcing mode. Please do take some time and try to understand the subtle differences between DAC and MAC. It takes awhile to wrap your head around.

You're thinking far to course where MAC lets you do things much much more fine-grained. As a SELinux fan, I also use Posix ACLs quite a bit for scary permissioning problems. Does that mean that ACLs should replace standard DAC? Nope. It should be used as a means to complement and further secure what already exists.

DAC vs. MAC in SEPostgres

Posted Sep 13, 2011 15:47 UTC (Tue) by Pc5Y9sbv (guest, #41328) [Link] (5 responses)

Hang on, I do appreciate the idea of security in depth, and how SE-Linux can complement other policy layers. I understand how SE-Linux contexts can be assigned to different clients to limit their ability to expand their privileges. However, my question was specifically about its application in SEPostgres to the problem of partitioning application data for different "sites" as described in the article...

I don't think "discretionary" vs. "mandatory" is a binary distinction when we're talking about fine-grained database resources not managed by the kernel. I still don't see how Postgres RBAC is any less mandatory than SEPostgres when it comes to isolating databases, schema, or tables between application tenants.

If I can subvert an application (database client process) I am not able to override the RBAC nor the SEPostgres policies being enforced on database resources. But, if I can subvert all per-site instances of the multi-tenant application, i.e. due to an application exploit rather than a site-specific configuration flaw, I can break down the per-site isolation to access whichever tenant's data I want, simply by controlling the right database client instance that already has privileges granted by RBAC and SEPostgres. Also, if I manage to subvert the database manager process, it seems I would be equally able to suppress the RBAC or the SEPostgres enforcement within that process to access database resources.

What am I missing here?

DAC vs. MAC in SEPostgres

Posted Sep 13, 2011 16:13 UTC (Tue) by dpquigl (guest, #52852) [Link] (1 responses)

In your example you're talking about an exploit in Postgres itself. If that is the case you are correct that can be used to bypass RBAS or SEPostgres. The database server is the object manager in the case of SEPostgres so exploiting the database server itself and bypassing SEPostgres is the equivalent of exploiting the kernel and bypassing SELinux. An exploit at the level that the mechanism is implemented can't be protected against at that level. The example I gave was a flaw in applications which use the database not the database itself.

DAC vs. MAC in SEPostgres

Posted Sep 13, 2011 16:46 UTC (Tue) by raven667 (subscriber, #5198) [Link]

That's actually an important point that should probably be explored more. SELinux implemented in the kernel can't protect against code that exploits the kernel. It is worth examining the assumption that a complex, fine-grained permissions system is worth the amount of effort required because any security exploit is going to have a rootkit running in the kernel that can completely bypass any permissions system. I thought redhat provided some data on how effective SELinux is in preventing exploit of the different CVEs they patch but I can't find it right now.

Just to be clear, for a sysadmin managing an SELinux system it isn't that hard to work with and so it very likely provides a useful protection that is worth the effort. For the sysadmin who just needs to tweak policies or write rules for home-grown software I did not find it nearly as difficult as one would be led to believe. I think more effort has been spent in complaining about it than in learning how to use it.

DAC vs. MAC in SEPostgres

Posted Sep 13, 2011 16:19 UTC (Tue) by dpquigl (guest, #52852) [Link] (2 responses)

I missed the first part of your last paragraph in my other response. I'm not quite understanding the scenario you're proposing. Could you provide a more concrete example?

DAC vs. MAC in SEPostgres

Posted Sep 13, 2011 17:24 UTC (Tue) by Pc5Y9sbv (guest, #41328) [Link] (1 responses)

The example was given of using SEPostgres to isolate the database state used by each "site" instance of a multi-tenant web application, which I claim is equivalent protection to using the normal Postgres RBAC model to protect different database instances created for each "site" instance. We're talking about roles or contexts assigned to the application for all its users, not fine-grained roles or contexts assigned to individual web users. The application is the enforcement point protecting data from unprivileged web users, not Postgres. Postgres is just protecting other applications' data from an application that breaks.

If I attack the application, I might subvert just one instance, for example by hijacking the web client that has site-level "super user" privileges to access data I shouldn't see or by using a code exploit that depends on invalid/unsafe data stored in that application instance. Neither RBAC nor SEPostgres helps here, because it still looks like the application code accessing its normal database content.

If I find an exploit in the web application code, not depending on the site-specific data content, I can repeat the attack on whichever site I want, and I can also access whatever data that Postgres would normally supply to that application instance. This is no different than if the "site" instances were each on physically separate servers with completely independent Postgres servers too. We see these sorts of exploits published all the time, due to the many layers of libraries and frameworks used for convenience on the web.

To do fine-grained restrictions, you would need to assign roles and/or context to each remote web user. This is a bootstrapping problem of getting from Internet-originated TCP stream containing an HTTP request to a restricted context suitable for evaluating the request. This means trusting Apache httpd and whatever web application components are involved in authentication and context establishment, much as we trust sshd today in establishing SE-Linux context for remote users.

To make SEPostgres "more mandatory", it would require pushing the access enforcement back into the kernel, e.g. by applying different SE-Linux contexts to different filesystem objects storing portions of the data, and having the query engine run in a more limited context for each client/query and making it tolerate refusals to access some elements of the data store. For example, each table is a separate file that could be protected to different levels, but you'd have to change the storage format to support column or row-level protections. You could then have the query engine act as if denied data does not exist. But this would probably require relaxing certain data integrity constraints, since they require a unified, global view of the existing data to validate them. But, you are still trusting parts of Postgres to act like sshd in establishing the right process context for clients.

DAC vs. MAC in SEPostgres

Posted Sep 13, 2011 19:45 UTC (Tue) by dpquigl (guest, #52852) [Link]

Ok I understand what you're saying now. Lets start at addressing paragraph 4 . I completely agree with you that you need something to be assigning sensible contexts to the request. mod_selinux (SELinux+) for apache does this. It has two ways of deciding this. One is that is uses authentication information to figure out the context similar to how sshd figures it out for a login and password. The second is based on the URL. Either way Apache assigns a context to the worker thread processing the request. Currently the second approach works on the site level so www.google.com or www.lwn.net and not specific pages. If it were extended to specific pages you could make it such that login processing is a trusted procedure in the PG database and only the page www.mysite.com/login has a context able to execute the trusted procedure.

Your 5th paragraph is an interesting idea but would require major rearchitecting of the database to do that. The idea at the base of it though is should the database server be in the TCB. If you can have it be in the TCB then having it act as the object manager is acceptable. I don't think however that we're trusting Postgres to establish the process context. In this case the context is still coming from the kernel. Postgres uses a function called getpeercon which has the kernel return the context of the user on the other end of the connecting socket. In the case of a CLI program its the program's context in the case of apache its the worker thread context. From there Postgres uses a call in libselinux that takes this context and the label on the database object in question and gives it to the kernel to make an access control decision. After that it is up to Postgres to enforce the decision.

Your design makes me think that you've worked on MLS databases before (I'm actually wondering who you are) since it would allow you to do separation of different sensitivity levels and compartments. There are people who currently try to do that with Postgres and what they have encountered is that once you start developing the lattice you get a ton of servers each with their own contexts. Instead by making Postgres be the object manager and assigning labels to the database objects themselves it allows more relevant decisions to be made. Like whether this Top Secret trusted procedure can be executed by a secret level process. Or another example that a secret process can run a trusted procedure that has access to TS data because the procedure is known to scrub it down to a S level. The idea is that by virtue of being a database Postgres has better knowledge about the objects in itself. It definitely is better suited to make decisions than the kernel is. So we make it what's called a userspace object manager. The kernel still gets to make the access control decision however the userspace object manager enforces it.

The reason the kernel can still make these decisions is because of the Flask architecture which separates the policy from the mechanism. Inside flask the policy exists as a set of masks which say if I'm given identifier a and it wants to act on an object of a certain type with identifier b here are the permissions it has (which is a bitmask). it knows nothing about what the object classes are for the user space components. It just knows that user space asked it for a decision based on 3 integers and a bitmask.

SEPostgres

Posted Sep 13, 2011 15:59 UTC (Tue) by foom (subscriber, #14868) [Link] (1 responses)

> What if someone in "rogue webapp A" manages to exploit a flaw and install a shell on your webserver and somehow gets root privileges with said shell? With SELinux that "root" shell could be as good as worthless and would still disallow access to data from "secure webapp B" in the proper enforcing mode.

No, if someone in "rogue webapp A" manages to exploit a flaw to get root, *that same flaw* can be used to become "super-root", bypassing all SELinux access control. SELinux *does not* protect you if the attacker has a kernel exploit.

SEPostgres

Posted Sep 13, 2011 16:03 UTC (Tue) by SEJeff (guest, #51588) [Link]

In a full MLS environment with the proper policy, you are incorrect. Note I was not mentioning a kernel flaw. There are plenty (the majority likely) of non-kernel exploits to become root.

http://www.coker.com.au/selinux/play.html for an example of why "root" is not so powerful in a full MLS SELinux environment.

SEPostgres

Posted Sep 13, 2011 1:29 UTC (Tue) by Cyberax (✭ supporter ✭, #52523) [Link] (19 responses)

Well, 'military grade' describes SELinux (and other SE* products) pretty well. I.e.:
1) It requires one to have a rank of at least colonel and staff of 80 people to make it work.
2) It costs 100 times more than 'civilian' models.
3) It's full of brain-dead solutions.
4) And in the end it doesn't work when it's needed most.

(PS: and yes, I have actual military experience)

SEPostgres

Posted Sep 13, 2011 4:57 UTC (Tue) by dpquigl (guest, #52852) [Link] (18 responses)

I'm trying to decide if you're attempting to be funny or if you're serious.

1) In the entire history of SELinux there probably haven't even been 80 people that have worked on it.
2) If you take the R&D dollars put into SELinux I guarantee companies such as Microsoft and Sun have spent far more on their security products.
3) I'd like a real example of this rather than baseless claims.
4) Again examples instead of baseless claims.

There will always be people who don't like SELinux for some reason or another. However considering SELinux stopped exploitation of a KVM vulnerability in May of this year I believe it actually is working when it is needed the most. The presentation at blackhat states you need a kernel level privesc to bypass SELinux in addition to the KVM vulnerability. Which definitely raises the bar (although not to an impossible level).

SEPostgres

Posted Sep 13, 2011 12:45 UTC (Tue) by SEJeff (guest, #51588) [Link] (2 responses)

Well SELinux is the culmination of 10 years of research by the NSA as the first (to my knowledge) real world example of the Flask operating system security architecture. Something tells me that isn't near as trivial as you so quickly write it off to be. I also love the bold "facts" you state without checking anything whatsoever.

Lets see how many people have worked on SELinux via our friend git log...

jeff@omniscience:~/git/linux-2.6/security$ (master) git log --pretty="%an" selinux/ | tr 'A-Z' 'a-z' | sort -u | wc -l
133

That shows 133 separate patch authors to code in the security/selinux/ subdirectory. That doesn't even begin to count the number of NSA analysts who worked on the design, a painstaking and > 1 person effort no doubt. If I manually remove a few duplicates (middle initials), that still leaves 128 separate patch authors.

I also think it is amusing (#2) that with all of the R&D MSFT has put into Windows, they have exactly 0 shipping MAC solutions. Linux has 4 integrated upstream I can think of without looking.
1.) SELinux
2.) AppArmour
3.) SMACK
4.) Tomoyo

Sun at least had the Trusted Solaris Extensions, which didn't catch on as much as the competing Linux-based solutions did. There is also TrustedBSD but still no MAC in windows.

Pot, meet kettle, you're both black. Please provide examples instead of baseless claims :)

SEPostgres

Posted Sep 13, 2011 14:33 UTC (Tue) by dpquigl (guest, #52852) [Link] (1 responses)

I understand the time and effort that went into SELinux and I can tell you that the core people who have done work on SELinux does not number anywhere close to either number. There are many commits in there that are people fixing usage of something that was changed outside of the security subsystem or fixing things that were changed in LSM. This is great though as it showcases exactly why having something in the kernel tree and the kernel development model is so awesome. For example when struct path was introduced we didn't have to fix the code in SELinux for it because the person who introduced it fixed it for us. I think a better metric might be to list people by code contribution instead.

Something that people don't realize is the actual core group of developers over the development of SELinux has never been that large. People seem to think there is a huge group of people at the agency working on it when in reality its just a hand full of people. The initial design for type enforcement was done by one person a long time ago and the design for Flask and SELinux was done by two people at the agency (My former bosses). The initial implementation for SELinux was done by one man(Steve Smalley).

It is true that windows does not have a MAC implementation but I didn't say they did. However with Vista they did introduce a mandatory integrity system and lucky for Microsoft they hired the creator of AppArmor onto their security team so maybe they will come up with one. You're right TSOL and Solaris TX would have been better comparisons. Its a shame that Sun took a step back with TX and moved to zone based labeling instead of doing fine-grained labeling again like in TSOL. I was told that they are fixing that in Solaris-TX however that last I heard that wasn't done yet.

SEPostgres

Posted Sep 13, 2011 14:41 UTC (Tue) by dpquigl (guest, #52852) [Link]

Try this command instead for looking at the authors.

git log --pretty="%an" selinux/ | sort -nr | uniq -c

SEPostgres

Posted Sep 13, 2011 15:21 UTC (Tue) by PaXTeam (guest, #24616) [Link] (1 responses)

> [...]considering SELinux stopped exploitation of a KVM vulnerability in May of this year[...]

no, it didn't, unless there's some magic capability that prevents use-after-free bugs from being exploited in which case i'm sure many other projects would like to hear about it and make use of it ;).

> [...]I believe it actually is working when it is needed the most.

this is just spreading a false sense of security. as a sidenote, MAC wasn't even designed for exploit prevention, it's no wonder it can't do much about exploits.

> The presentation at blackhat states you need a kernel level privesc to bypass SELinux [...]

that's a tautology, you obviously need a kernel bug to bypass a kernel feature. whether an attacker *wants* to go there is independent of his ability to exploit the kvm bug itself which already gives him plenty of privileges he didn't otherwise have, despite SELinux and whatnot.

SEPostgres

Posted Sep 13, 2011 15:40 UTC (Tue) by dpquigl (guest, #52852) [Link]

You're correct I misspoke. The sandboxing didn't prevent the exploit. It just limited the damage unless an additional privesc exploit was used.

SEPostgres

Posted Sep 14, 2011 1:32 UTC (Wed) by Cyberax (✭ supporter ✭, #52523) [Link] (12 responses)

I've personally given up on SELinux. It's a lot of pain for no gain. Policies are hellishly hard to write and maintain. They are even worse than autoconf-generated files.

And in the end, SELinux doesn't really matter that much. New local kernel exploits are discovered at least several times a years if not monthly. And SELinux can't do a thing against them (except make the life of admin harder during investigation and cleanup - been there, done that).

Just look at the recent hacks of kernel.org and linux.com - had they used SE* products? Nope, because they are way too complex. And probably would have been useless in the end.

Can we do better? Probably not. Linux fundamentally is not secure, and can't be made secure until it's rewritten in a safe language. That's not going to happen soon (if ever).

Ditto for PostgreSQL. Can SELinux be used to make it really secure? I doubt it - there's no way to really isolate security roles. However, ability to attach labels to database objects really rocks - I can use it in my application to do fast per-user views.

SEPostgres

Posted Sep 14, 2011 22:25 UTC (Wed) by cmccabe (guest, #60281) [Link] (11 responses)

> Linux fundamentally is not secure, and can't be made secure until
> it's rewritten in a safe language. That's not going to happen soon (if ever).

Higher-level languages are not immune to security problems. Just look at the huge number of Javascript cross-site scripting exploits, SQL injection attacks, and insecure shell scripts out there.

A microkernel architecture would help a little bit, but again, it's not a cure-all. For example, if you somehow compromise the filesystem component of a microkernel, you can probably simply add another root user to /etc/passwd and reboot. If you can modify the ACPI bytecode, you can get root that way. If you have access to the PCI bus, you can probably modify memory anywhere, which is again equivalent to root.

What it comes down to is that security is hard. Careful use of static analysis can probably blunt the sharpest edges of C, and various sandboxing technologies can help for higher level code, but there are no magic bullets.

SEPostgres

Posted Sep 14, 2011 23:54 UTC (Wed) by nix (subscriber, #2304) [Link] (1 responses)

Quite. People hold out formal proof as a solution here, but it isn't: all it does is push the problem up a level, to producing a bug-free formal prover and a bug-free spec and proof for your compiler and for the program you're going to compile with it, which seems a hell of a lot harder than merely coming up with a bug-free program in the first place (which is itself, of course, impossible).

Security: never gonna happen. So, in brief, we're all doomed.

SEPostgres

Posted Sep 16, 2011 9:53 UTC (Fri) by mpr22 (subscriber, #60784) [Link]

And bug-free silicon to run it on.

SEPostgres

Posted Sep 15, 2011 1:16 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link] (8 responses)

Insecure shell scripts and SQL injections are another results of insecure languages. Use LINQ in C# or QueryDSL in Java for data processing and all SQL injections are gone (well, if you REALLY want one - you can get it even with LINQ/QueryDSL, but you really have to try). Use PowerShell with typesafe shell language and most of shell vulnerabilities are gone as well.

I'm a security pessimist. Right now there's no way to secure Linux, in practice almost every vulnerability in almost any software can in principle be exploited to gain full root access.

But that shouldn't be so! We have safe languages which IMMEDIATELY limit the number of attacks. Even the worst SQL injection attack won't be able to give attacker root access and install a backdoor in your system, if database and kernel are written in safe a language.

Secure hardware is another issue, but we already have IOMMU which should help.

SEPostgres

Posted Sep 15, 2011 4:58 UTC (Thu) by dpquigl (guest, #52852) [Link] (4 responses)

Safe languages aren't a miracle cure. For example type JRE into the CVE database and you find a couple hundred exploits against just the JRE [1]. Then there is one of my favorites from last year [2].

[1]http://web.nvd.nist.gov/view/vuln/search-results?query=JR...
[2]http://blog.cr0.org/2010/04/javacalypse.html

SEPostgres

Posted Sep 15, 2011 12:52 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link] (3 responses)

Duh. VMs also should be written in a safe language.

Additionally, JVM's trust model and Code Access Security in CLR are braindead and should die.

SEPostgres

Posted Sep 15, 2011 18:58 UTC (Thu) by dlang (guest, #313) [Link] (2 responses)

if the VM is what's required to implement a safe language, you have a chicken and egg problem. somehow you need to implement a safe language in an unsafe language or you never get started, at that point everything is tainted and vulnerable.

SEPostgres

Posted Sep 15, 2011 20:30 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link] (1 responses)

Well, we already have Jikes RVM (Research Virtual Machine) - that's a JVM implemented in Java. It does have a problem of chicken-and-egg, but it's solved with a fairly small layer for direct RAM management and other utility functions.

There will be problems with insecurely JIT-ed machine code, but I believe they can also be solved.

SEPostgres

Posted Sep 22, 2011 4:53 UTC (Thu) by cmccabe (guest, #60281) [Link]

Periodically partisans of ${LANGUAGE} appear to tell the world that all existing software is doomed to be rewritten in ${LANGUAGE} within the next 5 years. Normally there are objections by reasonable people like "but I need to process text, and ${LANGUAGE} isn't very good at that" or "but I need to poke memory registers, and ${LANGUAGE} runs in a managed environment." These objections from lesser mortals are swept aside by the high priests of ${LANGUAGE} as the misguided rantings of the uninitiated.

But lets assume that "this time is different" and you really succeed in rewriting absolutely everything in ${LANGUAGE}. Well, once you have this perfect operating system (we'll assume it's bug-free, despite being written by humans), running on perfect hardware which somehow exists, you'll still get hacked.

Why? Because you'll give a login to someone who has a password sniffer installed on his computer. Or put his password on a post-it note near the monitor. Or who uses the same login for multiple accounts, one of which gets hacked. Or who uses a password that can be guessed. Or who you never should have trusted in the first place. Or any one of the million ways that your security can be breached that have nothing to do with what language your operating system is written in.

SEPostgres

Posted Sep 15, 2011 7:32 UTC (Thu) by dlang (guest, #313) [Link] (1 responses)

don't pick on linux, by your definition there is no OS that you can secure

SEPostgres

Posted Sep 15, 2011 12:53 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link]

Well, there ARE secure OSes out there. But they are in infancy right now.

And picking on Linux is fair, because it's what I'm using.

SEPostgres

Posted Sep 22, 2011 4:43 UTC (Thu) by cmccabe (guest, #60281) [Link]

I agree that in practice, Java is probably the most secure programming language we have. It doesn't have eval() [1], a perennial source of security problems, and it runs in a managed environment. Java is also statically typed, which tends to help static analysis tools understand it. (I do not know much about C#, but from what I understand, it's more or less the Microsoft version of Java with a few extra features thrown in.)

But "most secure we have" does not equal "magic bullet." If you've ever written some Java code that throws an exception when the user gives you bad data, and then not caught that exception-- congratulations, your server now has a denial-of-service vulnerability. If you've ever not checked the credentials of a user before serving them data-- congratulations, there's another security hole.

If you like Java, you should check out Google Go, which is also statically typed, but actually fixes a lot of the problems that Java has (in my opinion.)

[1] Well, actually you can load classes dynamically in Java. But from what I understand, this ability can be locked down when necessary.

SEPostgres

Posted Sep 13, 2011 4:56 UTC (Tue) by jberkus (guest, #55561) [Link]

Well, I would have been happy to have a phrase which was less cliche', actually. But I couldn't come up with anything and nobody suggested anything. For future notice, the initial drafts of the press releases are composed in public on the pgsql-advocacy mailing list.

PostgreSQL 9.1 released

Posted Sep 13, 2011 4:41 UTC (Tue) by butlerm (subscriber, #13312) [Link] (1 responses)

It is perhaps not as exciting, but what I would like to see is an equivalent for the following updateable view definition in Oracle:

CREATE OR REPLACE VIEW account_entry AS
SELECT * FROM z_account_entry a
WHERE a.owner = package.current_owner
WITH CHECK OPTION;

This allows a secure multi-tenant database (e.g. for software as a service applications) with an arbitrary number of record owners, each of whom cannot see or modify the rows that belong to any other owner, nor inadvertently give them away. No need for a separate database for each tenant. Just a simple implementation of row level security.

PostgreSQL 9.1 released

Posted Sep 13, 2011 4:55 UTC (Tue) by jberkus (guest, #55561) [Link]

You can do this in PostgreSQL now. The syntax for updatable views is a bit more verbose, but it works perfectly well.

See also the Veil project for a framework built around this concept:
http://veil.projects.postgresql.org/

Backslash escaped quotes

Posted Sep 13, 2011 19:08 UTC (Tue) by Richard_J_Neill (subscriber, #23093) [Link] (3 responses)

A potential gotcha [which is listed in the release notes] is that the postgresql setting "standard_conforming_strings" now defaults to "on" rather than "off" as it has historically been. As a result, backslashes are now interpreted literally, rather than as escapes.

Applications which use backslashes to escape quotes in strings will break.
For example, at the psql prompt, type:
SELECT 'I\'m singly quoted';

This used to work; now it will fail. I'm not certain whether or not this allows a different class of SQL injection attack.

Incidentally, Postgresql are, according to the SQL spec, correct: the following are the "right" way to do it:
SELECT 'I''m singly quoted';
SELECT E'I\'m singly quoted';

However, it seems a little risky to change such a well-established default.

Backslash escaped quotes

Posted Sep 13, 2011 19:16 UTC (Tue) by Richard_J_Neill (subscriber, #23093) [Link] (2 responses)

I suspect that "Little Bobby Tables" may make a comeback. If I'm not mistaken, a query constructed as:

$sql = "SELECT 'Some \'$quoted\' text' ...";

where $quoted comes from user-input, having being sanitised with addslashes()

is now vulnerable, if the user input is, for example:
";DROP TABLE students --"

This is built up as:

$sql = "SELECT 'Some \';DROP TABLE students --\' text' ...";

and if the backslashes aren't interpreted as escapes, we have all sorts of fun...

Backslash escaped quotes

Posted Sep 15, 2011 8:44 UTC (Thu) by rleigh (guest, #14622) [Link] (1 responses)

Isn't this only an issue for MS-SQL? As far as I know, it's the only database which can execute multiple SQL commands within a single statement, so PostgreSQL wouldn't actually execute the DROP TABLE in this context, being restricted to only running the initial SELECT. I'm not saying that there are no quoting issues, just that you would be restricted to modifying the SELECT statement.

Regards,
Roger

Backslash escaped quotes

Posted Sep 15, 2011 18:29 UTC (Thu) by dlang (guest, #313) [Link]

no, postgres can execute multiple ; separated commands as well (it depends on the interface that you use to send the command to postgres)

PostgreSQL 9.1 released

Posted Sep 14, 2011 6:51 UTC (Wed) by paxillus (guest, #79451) [Link] (3 responses)

"According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);

This is not currently implemented the source must be a list of independent expressions. "
http://www.postgresql.org/docs/current/static/sql-update....

This is what I would like to see soon ...

PostgreSQL 9.1 released

Posted Sep 15, 2011 10:42 UTC (Thu) by brunowolff (guest, #71160) [Link] (2 responses)

In Postgres scalar queries are limited to one column. But you can do what you want by using a separate scalar subquery for each of last_name and first_name. If the query is expensive (so that you don't want to do it more than once), then I think you can use WITH to get around that. But I haven't played with WITH very much and am not completely sure if it will work that way here.

PostgreSQL 9.1 released

Posted Sep 15, 2011 10:49 UTC (Thu) by andresfreund (subscriber, #69562) [Link] (1 responses)

Before doing independent subselects you just can bite the bullet and use a bit more verbosity:

UPDATE accounts SET contact_last_name = salesmen.last_name, contact_first_name = salesmen.last_name FROM salesmen WHERE salesmen.id = accounts.sales_id;

PostgreSQL 9.1 released

Posted Sep 15, 2011 17:26 UTC (Thu) by paxillus (guest, #79451) [Link]

does the job nicely - thanks