Re: [GENERAL] Limit the normal user to see system catalog or not??? And create privilege???
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 br...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: [GENERAL] Limit the normal user to see system catalog or not??? And create privilege???
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Limit the normal user to see system catalog or not??? And create privilege???
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Limit the normal user to see system catalog or not??? And create privilege???
On Wed, Mar 28, 2012 at 10:54 AM, leaf_yxj leaf_...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Limit the normal user to see system catalog or not??? And create privilege???
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
