> On 24 Jul 2010, at 24:20, Michael A. Peters wrote: > *snip* > > Schema's in Postgres are similar to different databases in MySQL. They > allow you to organise your tables in groups of tables belonging to similar > functionality, for example. They have their own permissions too, which is > nice if you need to restrict certain users to certain functionality. And > of course you can access tables cross-schema, if you aren't denied the > permissions. > > In your case, you could move those "troublesome" tables into their own > schema and adjust the search_path accordingly for the user your PHP > application uses to connect to the DB. I spent last night playing with schemas and I must say, they absolutely rock. I especially like the fact that you can still do queries involving multiple schemas if you need to because they are still part of the same database, and pg_dump keeping track of the various user authentications granted to a schema and its tables is class. It's the right way to do things. *snip* > > You could move Sphyder's tables into a separate schema too, but... if you > disallow the accompanying role (let's say 'sphyder') access to the public > schema, then it can't read various system tables either. That can cause > issues with looking up FK constraints and the like. > Mind that I've never been in a situation where I needed to disallow some > roles to access to the public schema, I'm not 100% sure about this - a > simple test case is easy to create though. > > I'd probably just put most (or all) of my main database in a schema other > than 'public' so that the sphyder role can still access the system tables > it needs (and it won't be able to change those if that role is set up with > sufficiently restrictive permissions). That's what I'm doing now. > > As an aside; I'm not familiar with Sphyder, but Postgres' TSearch 2 is > pretty good too. It's built into the main database since version 8.3, not > in your version. For 8.1 there is an extension with largely the same > functionality, in case you're interested. I'm not sure how easy that would > be to upgrade to the builtin version once you get to 8.3 or newer > though... I am going to look into that. ----- Michael A. Peters http://www.shastaherps.org/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
