Thread: Limit the normal user to see system catalog or not??? And create privilege???
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! Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/Limit-the-normal-user-to-see-system-catalog-or-not-And-create-privilege-tp5601146p5601146.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: Limit the normal user to see system catalog or not??? And create privilege???
From
Scott Marlowe
Date:
On Wed, Mar 28, 2012 at 10:54 AM, leaf_yxj <leaf_yxj@163.com> 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? Yeah, postgresql tends to focus on controlling what the user can DO not so much on what they can SEE about the schema. However... > 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! PostgreSQL just doesn't have the fine grained control that Oracle has. If you can create a table, you can create a view. OTOH, since a view is basical an empty table with a rule on top, it's not like it's all that different.
Re: Limit the normal user to see system catalog or not??? And create privilege???
From
Adrian Klaver
Date:
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. > > Thanks. > Regards. > > Grace > > -- -- Adrian Klaver adrian.klaver@gmail.com
Re: Limit the normal user to see system catalog or not??? And create privilege???
From
Bruce Momjian
Date:
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. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Limit the normal user to see system catalog or not??? And create privilege???
From
Adrian Klaver
Date:
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:) -- Adrian Klaver adrian.klaver@gmail.com
Re: Limit the normal user to see system catalog or not??? And create privilege???
From
Bruce Momjian
Date:
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Hi Super Guys,
Thanks. I learned a lot. It's very good for me to know that.
Regards.
Grace
At 2012-05-03 07:15:29,"Bruce Momjian" <bruce@momjian.us> wrote: >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@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. +