Subject: db_user_namespace a "temporary measure"

From: Thom Brown <[email-masked]>
Date: Sun, 9 Mar 2014 20:00:53 +0000

Hi,

I've noticed that "db_user_namespace" has had the following note
attached to it since 2002:

"This feature is intended as a temporary measure until a complete
solution is found. At that time, this option will be removed."

It will be 12 years this year since this "temporary measure" was
added.  I'm just wondering, is there any "complete solution" that
anyone had in mind yet?  Or should this just be deprecated?

Thanks

Thom

-- 
Sent via pgsql-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

---

From: Magnus Hagander <[email-masked]>
Date: Tue, 11 Mar 2014 14:32:44 +0100

> anyone had in mind yet?  Or should this just be deprecated?

I'd say +1 to remove it. That would also make it possible to get id of
"password" authentication...

But we might want to officially deprecate it for 9.4, and then remove it
later?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

---

From: Tom Lane <[email-masked]>
Date: Tue, 11 Mar 2014 09:40:39 -0400

> "password" authentication...

If we remove it without providing a substitute feature, people who are
using it will rightly complain.

Are you claiming there are no users, and if so, on what evidence?

			regards, tom lane

---

From: Magnus Hagander <[email-masked]>
Date: Tue, 11 Mar 2014 14:47:49 +0100

> Are you claiming there are no users, and if so, on what evidence?

I am claiming that I don't think anybody is using that, yes.

Based on the fact that I have *never* come across it on any system I've
come across since, well, forever. Except once I think, many years ago, when
someone had enabled it by mistake and needed my help to remove it...

But we should absolutely deprecate it first in that place. Preferrably
visibly (e.g. with a log message when people use it). That could at least
get those people who use it to let us know they do, to that way figure out
if they do - and can de-deprecate it.

Or if someone wants to fix it properly of course :)

---

From: Tom Lane <[email-masked]>
Date: Tue, 11 Mar 2014 09:57:56 -0400

> someone had enabled it by mistake and needed my help to remove it...

A slightly more scientific basis for that would be to ask on
pgsql-general.

> Or if someone wants to fix it properly of course :)

Yeah, that's what we've been hoping for for 12 years.  I stopped holding
my breath awhile ago.

Mind you, I wouldn't be unhappy to see it go away; it's a kluge and always
has been.  I'm just expecting lots of push-back if we try.  And it's kind
of hard to resist push-back when you don't have a substitute to offer.

			regards, tom lane

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 11 Mar 2014 12:23:16 -0400

> of hard to resist push-back when you don't have a substitute to offer.

Or we try to make it work. I don't think the idea is inherently bad, and 
I know there are people (like ISPs) who would like to have it work 
properly. Maybe in these days when most people are on dedicated VMs this 
matters less, but I don't think shared database servers are totally dead 
yet.

The docs say:

    db_user_namespace causes the client's and server's user name
    representation to differ. Authentication checks are always done with
    the server's user name so authentication methods must be configured
    for the server's user name, not the client's. Because md5 uses the
    user name as salt on both the client and server, md5 cannot be used
    with db_user_namespace.

Is that the only major issue? Why not have the server strip out the @db 
part if this is on? If we made this an initdb-time setting rather than a 
GUC then we'd remove the problems caused by turning this on and off. I'm 
not sure what other problems that might cause, but it doesn't seem 
totally intractable at first glance.

cheers

andrew

---

From: Stephen Frost <[email-masked]>
Date: Tue, 11 Mar 2014 12:37:25 -0400

* Andrew Dunstan ([email-masked]) wrote:
> Or we try to make it work. I don't think the idea is inherently bad,
> and I know there are people (like ISPs) who would like to have it
> work properly. Maybe in these days when most people are on dedicated
> VMs this matters less, but I don't think shared database servers are
> totally dead yet.

Agreed.  There are certainly pretty big hosting companies out there
which are already doing multi-tenant PG, but they're using their own
approaches instead of anything we provide (because what we provide
sucks, basically..).

> Is that the only major issue? Why not have the server strip out the
> @db part if this is on? If we made this an initdb-time setting
> rather than a GUC then we'd remove the problems caused by turning
> this on and off. I'm not sure what other problems that might cause,
> but it doesn't seem totally intractable at first glance.

Isn't the other issue for ISPs essentially that we don't have row-level
security for our global catalogs?  as in- we can't limit what's in
pg_authid to only those entries a given user should be able to see?  I
don't think db_user_namespace addresses that issue (but I didn't go look
either).

	Thanks,

		Stephen

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 11 Mar 2014 12:43:11 -0400

> don't think db_user_namespace addresses that issue (but I didn't go look

Possibly, but we don't have to solve every problem at once. As the 
Sermon on the Mount says, "Sufficient unto the day is the evil thereof."

cheers

andrew

---

From: Tom Lane <[email-masked]>
Date: Tue, 11 Mar 2014 19:41:00 -0400

> Is that the only major issue?

That's one major issue.

Another one is the hacky way of distinguishing global from per-db users
(ie, user must append @ to log in as a global user).  I'd love to get rid
of that requirement, but not sure how.  Would it be all right for the
server to first probe for user@db and then for just user, or vice versa?
How would this integrate with pg_hba.conf?

> Why not have the server strip out the @db part if this is on?

Hmm ... that's a thought.  IIRC, the client doesn't actually know that
this is going on, it just sends the user name, and hashes against that
too.  If the server remembered the bare user name it could hash against
that as well.

At least, that would work for db-local usernames.  It *doesn't* work for
global names, because the client side has no idea that the @ ought to not
be counted for hashing.  Again, if we could get rid of that convention,
it'd be far less messy.

A possible objection would be that changing the definition of what to hash
would invalidate existing MD5 password hashes; but since MD5 passwords
have never been allowed with db_user_namespace names anyway, that doesn't
seem very forceful.

> If we made this an initdb-time setting rather than a 
> GUC then we'd remove the problems caused by turning this on and off.

Why do we need to restrict that?

			regards, tom lane

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 11 Mar 2014 19:57:05 -0400

> server to first probe for user@db and then for just user, or vice versa?

user@db first, I think. I guess that means don't name your global users 
the same as db-specific users. Maybe we should prevent a conflict? Or if 
we allow a conflict then only require user@ in conflicting cases.

> How would this integrate with pg_hba.conf?

Not sure.

> be counted for hashing.  Again, if we could get rid of that convention,

Right.

> have never been allowed with db_user_namespace names anyway, that doesn't

Agreed.

> Why do we need to restrict that?

Oh, probably a thinko in my part. I thought the setting would affect the 
stored password, but now I think about it some more I see it doesn't.

I think we might be on the track of something useful here.

cheers

andre

---

From: Josh Berkus <[email-masked]>
Date: Tue, 11 Mar 2014 17:46:02 -0700

> of hard to resist push-back when you don't have a substitute to offer.

Yeah, what we really need is encapsulated per-DB users and local
superusers.  I think every agrees that this is the goal, but nobody
wants to put in the work to implement a generalized solution.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

---

From: Tom Lane <[email-masked]>
Date: Tue, 11 Mar 2014 21:37:47 -0400

> wants to put in the work to implement a generalized solution.

Well ... you know, how hard would it really be?  Extend the primary
key of pg_authid to be (username, database_oid) with the convention
of database_oid = 0 for a globally known user.  Add some syntax to
CREATE USER to indicate whether you're creating a global user
(the default) or one known only within the current database.  I'm
not quite sure what to do with local users for pg_dump/pg_dumpall;
perhaps pg_dump should get the responsibility of creating local users?
But otherwise it seems like there are no issues that we'd not have to
solve anyway if we wanted to make db_user_name less of a crock.

In particular, I'd like to see an exclusion that prevents local users
from having the same name as any global user, so that we don't have
ambiguity in GRANT and similar commands.  This doesn't seem simple to
enforce (if we supported partial indexes on system catalogs, it would
be ...) but surely this representation is more amenable to enforcing it
than the existing one.

			regards, tom lane

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 11 Mar 2014 23:01:53 -0400

> be ...) but surely this representation is more amenable to enforcing it

Should be workable if you're creating a local name - just check against 
the list of global roles. For creating global names, maybe we could keep 
an invisible shared catalog (or maybe only visible to global superusers) 
of local names / db oids. New global names would be checked against that 
catalog. Something like that.

cheers

andrew

---

From: Tom Lane <[email-masked]>
Date: Tue, 11 Mar 2014 23:06:06 -0400

> the list of global roles.

Concurrent creations won't be safe without some sort of locking scheme.
A unique index would be a lot better way of plugging that hole than a
system-wide lock on user creation.  But not sure how to define a unique
index that allows (joe, db1) to coexist with (joe, db2) but not with
(joe, 0).

			regards, tom lane

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 11 Mar 2014 23:20:01 -0400

> index that allows (joe, db1) to coexist with (joe, db2) but not with

Create (joe, db1), (joe, db2) ... for each global user? Might get a tad 
ugly if you have lots of global users or lots of databases.

Just trying to be a bit creative :-)

cheers

andrew

---

From: Aidan Van Dyk <[email-masked]>
Date: Tue, 11 Mar 2014 23:20:12 -0400

So I'll admit to using it, only in "toy" setups...

I use it with trust and ident, on local connections though, not password....

I try to keep my laptops clean of mysqld, and I use PG.  And only on my
laptop/PC,  I make a database for every user...  And every "app" get's a
userid, and a schema....    Every user get's passwordless access to their
database.  And the "userid" associates the app, and the defaults that get
used on their connections.

So, I think it's "neat", but wouldn't be put out if it was removed ...

-- 
Aidan Van Dyk                                             Create like a god,
[email-masked]                                       command like a king,
http://www.highrise.ca/                                   work like a
slave.

---

From: Jaime Casanova <[email-masked]>
Date: Tue, 11 Mar 2014 22:50:28 -0500

On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane <[email-masked]> wrote:
> But not sure how to define a unique

and why you want that restriction? when you login you need to specify
the db, right? if you don't specify the db then is the global 'joe'
that want to login

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 11 Mar 2014 23:52:47 -0400

> the db, right? if you don't specify the db then is the global 'joe'

You can't login without specifying a db. Every session is connected to a db.

cheers

andrew

---

From: Jaime Casanova <[email-masked]>
Date: Tue, 11 Mar 2014 23:05:41 -0500

> You can't login without specifying a db. Every session is connected to a db.

then, what's the problem with global users?

---

From: David Johnston <[email-masked]>
Date: Tue, 11 Mar 2014 21:39:25 -0700 (PDT)

Andrew Dunstan wrote
>> On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane &lt;

> tgl@.pa

> &gt; wrote:

Random Thoughts:

So if dave is already a user in db1 only that specific dave can be made a
global user - any other dave would be disallowed.

Would "user - password" be a better PK? Even with the obvious issue that
password part of the key can change.  "user-password to database" is a
properly many-to-many relationship.  Or see next for something simpler.

A simple implementation would simply have the global users copied into each
database as it is constructed.  There would also be a link from each of the
database-specific users and the global master so that a password change
issued against the global user propagates to all the database-specific
versions.

Construction of a new global user would cause an immediate copy-over; which
can fail if the simple name is already in use in any of the existing
databases.  "Promoting" becomes a problem that would ideally have a solution
- but then you are back to needing to distinguish between "dave-db1" and
"dave-db2"...

Be nice if all users could be "global" and there would be some way to give
them permissions on databases.

Or go the opposite extreme and all users are local and the concept of
"global" would have to be added by those desiring it.  Basically move
user-management at the cluster level to a cluster support application and
leave databases free-standing.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/db-user-namespace-a-temporary-measure-tp5795305p5795609.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

---

From: Magnus Hagander <[email-masked]>
Date: Wed, 12 Mar 2014 08:22:06 +0100

> wants to put in the work to implement a generalized solution.

Encapsulated would probably be the doable part. But local superuser? Given
that a superuser can load and run binaries, how would you propose you
restrict that superuser from doing anything they want? And if you don't
need that functionality, then hows it really different from being the
database owner?

/Magnus

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 12 Mar 2014 10:03:42 -0300

> index that allows (joe, db1) to coexist with (joe, db2) but not with

Isn't this just a case of creating a suitable operator and an exclusion
constraint?  Defining the constraint in BKI might require extra
infrastructure, but it should be possible.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

---

From: Andres Freund <[email-masked]>
Date: Wed, 12 Mar 2014 14:19:29 +0100

> infrastructure, but it should be possible.

Except that we don't have the infrastructure to perform such checks
(neither partial, nor expression indexes, no exclusion constraints) on
system tables atm. So it's not a entirely trivial thing to do.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Mar 2014 10:48:13 -0400

> and why you want that restriction?

So that if I say "GRANT SELECT ON mytable TO joe", it's unambiguous which
user I'm granting to.  There should be at most one "joe" that can access
any given database.

If we don't have such a restriction, we'll need syntax kluges in GRANT,
ALTER OWNER, and probably other commands to disambiguate whether a local
or global user is meant.  Right now with the db_user_namespace mechanism,
you have to say GRANT ... TO "joe@db1" if you were granting to a local
user in db1.  That's unambiguous all right, but it has little else to
recommend it.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Mar 2014 10:52:14 -0400

> need that functionality, then hows it really different from being the

A local user with the superuser privilege would not be able to log into
another database, because superuser doesn't give you any extra privilege
until you've logged in.

Yeah, as superuser you could still break things as much as you pleased,
but not through SQL.

I share your doubts as to how useful such a concept actually is, but
it'd work if we had real local users.

			regards, tom lane

---

From: Magnus Hagander <[email-masked]>
Date: Wed, 12 Mar 2014 15:58:24 +0100

> Yeah, as superuser you could still break things as much as you pleased,

You could COPY over the hba file or sometihng like that :)  Or just
pg_read_binary_file() on the files in another database, which is accessible
through SQL as well.

> it'd work if we had real local users.

It can also do interesting things like ALTER SYSTEM, replication, backups,
etc. All of which could be used to escalate privileges beyond the local
database.

So you'd have to somehow restrict those, at which point what's the point of
the property in the first place?

---

From: Stephen Frost <[email-masked]>
Date: Wed, 12 Mar 2014 11:06:49 -0400

> etc. All of which could be used to escalate privileges beyond the local

Probably DROP ROLE for global users too.

> the property in the first place?

We've been asked quite often for a not-quite-superuser, as in, one which
can bypass the normal GRANT-based permission system but which can't do
things like create untrusted functions or do other particularly bad
activities.  I can certainly see value in that.  Another oft-requested
option is a read-only role which pg_dump or an auditor could use.

Anyway, this is getting a bit far afield from the original discussion,
which looked like it might actually be heading somewhere interesting..

	Thanks,

		Stephen

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Mar 2014 11:10:38 -0400

> pg_read_binary_file() on the files in another database, which is accessible

More directly, he could alter pg_authid to make himself a not-local user.
But I don't see that it's our responsibility to prevent that.  As long as
the combination of features works in a straightforward way, I'm happy
with it --- and it would, AFAICS.

			regards, tom lane

---

From: Stephen Frost <[email-masked]>
Date: Wed, 12 Mar 2014 11:20:36 -0400

> with it --- and it would, AFAICS.

That depends on exactly what you mean by 'those features'.  There's
quite a difference between "you can set the superuser flag on a local
user and then that user will be a superuser" and "a local user with
superuser flag will only be able to impact the database they are local
to".  I agree that there's nothing stopping us from having a "local"
user which is marked as a superuser from a technical level.

What Magnus and I are worried about is the *implication* of such a
configuration is and what the user will think it means.  Specifically,
there will be an assumption that "local" users can only access or impact
the databases which they have access to, which wouldn't be accurate for
a "local" user who is a superuser.  Certainly, documenting this would
help with that but with as many warnings as we'd have to put up about
that being dangerous and that it isn't actually going to prevent that
superuser from accessing the other databases if they really wanted to,
or prevent them from making a global superuser account, etc, I'm just
not convinced that it's worth it for the "feature" of allowing a "local"
account to be set as superuser- I don't see a huge use-case there.

	Thanks,

		Stephen

---

From: Josh Berkus <[email-masked]>
Date: Wed, 12 Mar 2014 11:09:48 -0700

> need that functionality, then hows it really different from being the

Well, if you really want my "I want a pony" list:

Local superusers (maybe this concept needs another name) would be able
to do the following things in a *single* database:

1 change permissions for other users on that database and its objects
2 load extensions from a predefined .so directory / list
3 create/modify untrusted language functions
4 create per-database users and change their settings
5 change database settings (SET stuff)
6 NOT change their own user settings
7 NOT change any global users
8 NOT run SET PERSISTENT or other commands with global effect

The above is fairly similar to what Amazon currently offers for Postgres
RDS, except that they only have 1 database per instance, so clearly they
haven't worked out some of the security issues.

Now, obviously permission (3) could be used to escalate a local
superuser to global superuser permissions, so local superusers aren't
really a secure concept, unless you don't add any untrusted languages to
the list of allowed extensions.  Alternately, we could drop (3) from the
list of features.

Hmmmm. On the other foot, though: all of 1,2,4 and 5 could conceivably
be done via a set of Security Definer functions loaded into the
database, with a lot less complexity and security risk.  So if we're
sacrificing untrusted languages, then we really don't need a local
superuser at all; just a bunch of variadic SD functions which can handle
user creation and permissions issues. It would limit the permissions
syntax we can express, but not critically so in my opinion. This could
all be done as an extension, and would probably be the better for it.

Assuming we have db-local users in the first place, of course.

> global user - any other dave would be disallowed.

Correct.  Well, unless the other dave was promoted first.  However, I
personally don't see any reason why we should even support promoting
users from local to global.  It adds complexity to the concept, and the
value of it eludes me.

> properly many-to-many relationship.  Or see next for something simpler.

I have no idea where you're going with this.

> issued against the global user propagates to all the database-specific

That's called "massive backwards compatibility failure", and I don't
think we'll go any further with your concept.

> them permissions on databases.

Um, there is:
http://www.postgresql.org/docs/9.3/static/sql-grant.html

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

---

From: Andrew Dunstan <[email-masked]>
Date: Wed, 12 Mar 2014 14:21:19 -0400

> 8 NOT run SET PERSISTENT or other commands with global effect

Item 3 gives away the store. AFAIK Amazon doesn't load untrusted 
languages, period.

cheers

andrew

---

From: Stephen Frost <[email-masked]>
Date: Wed, 12 Mar 2014 14:25:13 -0400

> 1 change permissions for other users on that database and its objects

What about "bypass" permissions, ala what superuser does today?  Or are
you saying we'd only need to allow this new kind of role to bypass the
checks in the GRANT/REVOKE system?

> 2 load extensions from a predefined .so directory / list

This would obviously have to be a curated list that avoids things like
'adminpack'...

> 3 create/modify untrusted language functions

Uhh, I don't believe RDS allows you to do this..?

> 4 create per-database users and change their settings

Presumably just for the 'local' DB?

> 5 change database settings (SET stuff)

This can be done by the database-owner already, no?

> 6 NOT change their own user settings

Don't think this is quite that simple (passwords?).

> 7 NOT change any global users

What about role membership, wrt local vs. global roles?

> 8 NOT run SET PERSISTENT or other commands with global effect

Indeed, or use 'COPY'..

> the list of allowed extensions.  Alternately, we could drop (3) from the

That'd certainly be the main issue that I see with this proposal.  Doing
the rest but allowing untrusted languages would just get the naive in
trouble and not help those of us who want this, as we wouldn't be able
to use it.

> database, with a lot less complexity and security risk.

For my part- I don't see having everyone write their own set of SECURITY
DEFINER functions as being either less complex or less risk.  They're
also a lot less convenient to use.  That's not what RDS did, is it?  No,
and I agree with them on that part.

Agreed.

	Thanks,

		Stephen

---

From: Josh Berkus <[email-masked]>
Date: Wed, 12 Mar 2014 11:32:06 -0700

> checks in the GRANT/REVOKE system?

More like what we have for the database owner role today.

> This would obviously have to be a curated list that avoids things like

It would need to be a list created by the global superuser.  By default,
nothing would be on it.

Right.

> This can be done by the database-owner already, no?

Oh, point.

> Don't think this is quite that simple (passwords?).

Well, we already limit what things users can change about themselves;
they can't promote themselves to superuser, for example.

> What about role membership, wrt local vs. global roles?

Allowing global users to join local ROLEs is its own can'o'worms that
would merit an entire other thread.  Assuming, of course, that we had
local users in the first place.

>> 8 NOT run SET PERSISTENT or other commands with global effect

Yeah.  In theory, we should allow the local superuser to use COPY; in
practice, nobody will care because they'll be using client-side COPY
since the entire use-case for this is cloud-hosted DBs anyway.

> and I agree with them on that part.

I was thinking of having an extension in contrib, actually.  That is, a
canonical set of security definer functions.  But if you think it's
easier to actually implement the permissions restrictions in the actual
utility functions, I wouldn't argue.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Mar 2014 14:40:17 -0400

> Item 3 gives away the store.

Indeed.  If you can do (3), you can break out of any of the other
constraints.  I suspect even (1) and/or (5) would be enough to mount
trojan-horse attacks against real superusers who visit your database.

I do not put any stock in the notion of "constrained superuser".

			regards, tom lane

---

From: Josh Berkus <[email-masked]>
Date: Wed, 12 Mar 2014 12:03:07 -0700

> trojan-horse attacks against real superusers who visit your database.

... nobody reads my whole post, except Stephen.  :-(

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

---

From: Stephen Frost <[email-masked]>
Date: Wed, 12 Mar 2014 15:05:38 -0400

> More like what we have for the database owner role today.

eh?  Can you clarify what you think the database owner role can do today
that actually matches what you're asking for above, or even what I'm
suggesting?

> It would need to be a list created by the global superuser.  By default,

Hmm, so this would be independent from what's installed on the system at
a file level.  That's an interesting idea.

> they can't promote themselves to superuser, for example.

Just saying that we need to be more specific on this point.

> would merit an entire other thread.  

What would the caveats here be then..?  Local roles can join global
ones, but global roles can't join local ones?  I'm not sure how much of
an issue this will really be if they're all sharing one catalog with
different namespaces (eg: the '@db' stuff).

> Assuming, of course, that we had

That's what this thread is about, isn't it..?

> since the entire use-case for this is cloud-hosted DBs anyway.

How could we allow the local "superuser" to use COPY?  They've be able
to overwrite files in other databases, or possibly modify
postgresql.conf, etc, depending on the overall system environment.

> utility functions, I wouldn't argue.

Having it in contrib would be better than nothing, but I'd advocate
putting this in as part of our actual permissions model, along with the
read-only/auditor-type option.  Having this not-quite-superuser which is
able to read all tables, possibly modify any of them, without having to
change the permissions system could be extremely useful and would be
rather painful to implement through SD functions.

	Thanks,

		Stephen

---

From: Robert Haas <[email-masked]>
Date: Wed, 12 Mar 2014 19:44:40 -0400

> system tables atm. So it's not a entirely trivial thing to do.

I'm probably woefully underinformed here, but it seems like getting
exclusion constraints working might be simpler than partial indexes or
expression indexes, because both of those involve being able to
evaluate arbitrary predicates, whereas exclusion constraints just
involve invoking index access methods to look for conflicting rows via
smarts built into your index AM.  The latter seems to involve less
risk of circularity (but I might be wrong).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Mar 2014 20:54:36 -0400

> risk of circularity (but I might be wrong).

You might be right.  I don't think anyone's ever looked at what it
would take to support that particular case.  We have looked at the
other cases and run away screaming ... but I think that was before
exclusion constraints existed.

			regards, tom lane

---

From: Andres Freund <[email-masked]>
Date: Thu, 13 Mar 2014 12:24:13 +0100

> > risk of circularity (but I might be wrong).

Exclusion constraints support being partial... But I guess we could
forbid using that.

> exclusion constraints existed.

Hm. Is it actually that complicated to support checking predicates and
computing expressions for system catalogs during index insertions? If
we'd only create those indexes once the the basic bootstrap is over, I
don't see too much problems with circularity? Creating indexes on shared
catalogs after the immediate bootstrap isn't entirely trivial, but
should be doable.
I've searched for "running away screaming", but even with extending the
search critera a bit I unfortunately came up empty.

I don't really see much need for expression indexes on catalogs, but
partial unique constraints would surely be useful.

Now, what I *do* see problems with would be to try to evaluate
predicates/expressions when filling system caches. But it looks to be me
like the primary interest at least here is partial unique constraints?

Greetings,

Andres Freund