Re: system catalog privilege and create privilege ??? how to control them?? thanks - Mailing list pgsql-general

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?

You can try that, but things may break in unexpected ways.
For example, psql's utility commands will probably stop working.
I would test any such change thoroughly.

Not all system catalogs are visible for everybody, tables and views
containing passwords for example can only be read by superusers.

PostgreSQL has fewer restrictions on reading system catalogs than
Oracle.
I can see how a seasoned Oracle DBA might feel uneasy if everybody
can find out all user names on the database cluster.

> 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's permission system is different from Oracle's.
Oracle has a lot of "system privileges" which PostgreSQL does not
have or need.

In Oracle, every user automatically has a schema of the same name
and there are no permissions on schema basis.  So you need system
privileges if you want to keep users from creating objects.

In PostgreSQL you can use schema permissions.
True, as soon as you have CREATE on a schema, you can create any
kind of object there.  That is, any kind of object that does not
depend on anything else.
To create a function, you need the USAGE privilege on the
procedural function.  You can revoke this right from PUBLIC and
only give it to the users you want.
To create a trigger, you need the TRIGGER privilege on the
table involved and the EXECUTE privilege on the trigger function.

So you see, most of what Oracle handles with system privileges is
handled with object privileges in PostgreSQL.  And you usually
can assign permissions in a finer granularity that way.

Of course it is confusing at first, but once you understand
PostgreSQL's permission system, there are few meaningful things
that you cannot achieve with it.
What's the use case for granting somebody CREATE TABLE, but
not CREATE VIEW?

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

---

If you reply to this email, your message will be added to the discussion below:

http://postgresql.1045698.n5.nabble.com/system-catalog-privilege-and-create-privilege-how-to-control-them-thanks-tp5601150p5602979.html

To unsubscribe from system catalog privilege and create privilege ??? how to control them?? thanks, click here.
NAML