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_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.

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

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. +

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

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: