Re: Limit the normal user to see system catalog or not??? And create privilege???

On Wed, May 02, 2012 at 04:03:01PM -0700, Adrian Klaver wrote:
> On 05/02/2012 11:42 AM, Bruce Momjian wrote:
> > On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote:
> >> On 03/28/2012 09:54 AM, leaf_yxj wrote:
> >>> For oracle, the normal user can't see all the system catalog. but for
> >>> postgresql, it looks like all the user can see the system catalog. Should
> >>> we limit the user read privilege to system catalog?
> >>>
> >>> In oracle, the system privilege has create table, create view,create
> >>> function. For postgresql database, how to control the user who only can
> >>> create table but can't create view. Based on the test I did, once the user
> >>> has the create privilege on the schema, the user will have any create
> >>> privilege on that schema. In postgresql, Rule is used to control that ???
> >>> very confused!
> >>
> >> Path to unconfusion:):
> >> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html
> >>
> >> You can grant CREATE on a schema and then restrict CREATE within the
> >> schema for different objects types. In recent versions you are
> >> looking for ALL * IN SCHEMA schema_name where * is the object type.
> >
> > I think the problem with ALL * IN SCHEMA it just applies permissions on
> > all objects in the schema at a point in time, i.e. it doesn't apply to
> > objects created _after_ that command was run.
>
> True, but in the above was an explanation of default privileges which
> led to this link:
>
> http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html
>
> ALTER DEFAULT PRIVILEGES does allow you to control what happens in the future.
> Admittedly not the most obvious connection:)

Oh, I forgot about that one.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +