Table permissions problem

On Friday I asked.

Has something changed recently? I created a new database and it won't
let me insert unless I specifically grant myself privledges. It
shows as being owned by me. Also, I can't create a view. It says
"pg_rewrite: Permission denied." and doesn't create the view.

I have since found out that it was changed so that no one, not even the
owner, had full privs on the tables. I went in and changed my code
to grant ALL on each database to the db owner. Everything is fine
except that I still can't create a view. I still get that "pg_rewrite:
Permission denied." error. Any ideas?

Only superuser. From TODO:

CREATE VIEW requires super-user priviledge

This is because someone who can modify pg_rewrite can create their own
view to modify any table.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thus spake Bruce Momjian

I have since found out that it was changed so that no one, not even the
owner, had full privs on the tables. I went in and changed my code
to grant ALL on each database to the db owner. Everything is fine
except that I still can't create a view. I still get that "pg_rewrite:
Permission denied." error. Any ideas?

Only superuser. From TODO:

CREATE VIEW requires super-user priviledge

This is because someone who can modify pg_rewrite can create their own
view to modify any table.

Hmm. That makes it difficult. I would like to build databases from
scripts. I guess I can build them as the super user.

Shouldn't the database owner have more privs then someone using the
database? Is that what that TODO item is actually suggesting?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thus spake Bruce Momjian

I have since found out that it was changed so that no one, not even the
owner, had full privs on the tables. I went in and changed my code
to grant ALL on each database to the db owner. Everything is fine
except that I still can't create a view. I still get that "pg_rewrite:
Permission denied." error. Any ideas?

Only superuser. From TODO:

CREATE VIEW requires super-user priviledge

This is because someone who can modify pg_rewrite can create their own
view to modify any table.

Hmm. That makes it difficult. I would like to build databases from
scripts. I guess I can build them as the super user.

Shouldn't the database owner have more privs then someone using the
database? Is that what that TODO item is actually suggesting?

Remember some tables are shared with all databases. Makes things more
difficult.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thus spake Bruce Momjian

I have since found out that it was changed so that no one, not even the
owner, had full privs on the tables. I went in and changed my code
to grant ALL on each database to the db owner. Everything is fine
except that I still can't create a view. I still get that "pg_rewrite:
Permission denied." error. Any ideas?

Only superuser. From TODO:

CREATE VIEW requires super-user priviledge

This is because someone who can modify pg_rewrite can create their own
view to modify any table.

Hmm. That makes it difficult. I would like to build databases from
scripts. I guess I can build them as the super user.

Shouldn't the database owner have more privs then someone using the
database? Is that what that TODO item is actually suggesting?

Remember some tables are shared with all databases. Makes things more
difficult.

And that's why I suggested a uid/euid model over functions,
triggers and views, where the permission checks are done
against the function/view owner instead of the current user.
If nobody reverted things, this is already done for views
(pg_user vs pg_shadow). The ACL checks are done during the
rewrite.

So CREATE VIEW or CREATE RULE could eventually use some trick
to get around the restrictions on pg_rewrite but ensure that
the rule owner is the one creating it. Pg_rewrite itself must
be protected, otherwise a user could change the ownership of
a view and get around access restrictions.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Remember some tables are shared with all databases. Makes things more
difficult.

And that's why I suggested a uid/euid model over functions,
triggers and views, where the permission checks are done
against the function/view owner instead of the current user.
If nobody reverted things, this is already done for views
(pg_user vs pg_shadow). The ACL checks are done during the
rewrite.

So CREATE VIEW or CREATE RULE could eventually use some trick
to get around the restrictions on pg_rewrite but ensure that
the rule owner is the one creating it. Pg_rewrite itself must
be protected, otherwise a user could change the ownership of
a view and get around access restrictions.

Couldn't we do some permission checks on tables at runtime from the
rewrite system ONLY when a table is being added or UPDATE is added to
SELECT, etc?

Would that solve the problem? Maybe not because you would have to rip
apart the plan, wouldn't you?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thus spake Bruce Momjian

Hmm. That makes it difficult. I would like to build databases from
scripts. I guess I can build them as the super user.

Shouldn't the database owner have more privs then someone using the
database? Is that what that TODO item is actually suggesting?

Remember some tables are shared with all databases. Makes things more
difficult.

I didn't realize that. I assume that you don't mean some system tables
as they could, I am sure, be special cased. Can you expand? I once
had a situation where a table in one database was trashed by being filled
with data from another table of the same name in another database. Was
I bitten by this feature somehow? At the same time I like the idea of
sharing tables. It would be perfect for things like time zone and
province/state tables.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thus spake Bruce Momjian

Hmm. That makes it difficult. I would like to build databases from
scripts. I guess I can build them as the super user.

Shouldn't the database owner have more privs then someone using the
database? Is that what that TODO item is actually suggesting?

Remember some tables are shared with all databases. Makes things more
difficult.

I didn't realize that. I assume that you don't mean some system tables
as they could, I am sure, be special cased. Can you expand? I once
had a situation where a table in one database was trashed by being filled
with data from another table of the same name in another database. Was
I bitten by this feature somehow? At the same time I like the idea of
sharing tables. It would be perfect for things like time zone and
province/state tables.

Especially system tables (pg_database, pg_shadow!, ...).

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Remember some tables are shared with all databases. Makes things more
difficult.

And that's why I suggested a uid/euid model over functions,
triggers and views, where the permission checks are done
against the function/view owner instead of the current user.
If nobody reverted things, this is already done for views
(pg_user vs pg_shadow). The ACL checks are done during the
rewrite.

So CREATE VIEW or CREATE RULE could eventually use some trick
to get around the restrictions on pg_rewrite but ensure that
the rule owner is the one creating it. Pg_rewrite itself must
be protected, otherwise a user could change the ownership of
a view and get around access restrictions.

Couldn't we do some permission checks on tables at runtime from the
rewrite system ONLY when a table is being added or UPDATE is added to
SELECT, etc?

Would that solve the problem? Maybe not because you would have to rip
apart the plan, wouldn't you?

--
Bruce Momjian | 830 Blythe Avenue

I checked it. It's all already there. The rewrite handler
checks for all the range table entries he adds to the query,
if the rule owner has the appropriate permissions
(read/write). And it sets the skipAcl field for these rte's
so the executor later will ignore them.

The only thing missing is that CREATE VIEW and CREATE RULE
require write access to pg_rewrite (create view does a create
table and create rule internally). But we cannot grant write
access on pg_rewrite to looser users. So I think a global
variable, turning off the whole ACL system temporary, would
do.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Thus spake Bruce Momjian

Hmm. That makes it difficult. I would like to build databases from
scripts. I guess I can build them as the super user.

Shouldn't the database owner have more privs then someone using the
database? Is that what that TODO item is actually suggesting?

Remember some tables are shared with all databases. Makes things more
difficult.

I didn't realize that. I assume that you don't mean some system tables
as they could, I am sure, be special cased. Can you expand? I once
had a situation where a table in one database was trashed by being filled
with data from another table of the same name in another database. Was
I bitten by this feature somehow? At the same time I like the idea of
sharing tables. It would be perfect for things like time zone and
province/state tables.

See pgsql/data/ and you will see the tables that are shared:

pg_database
pg_group
pg_shadow
pg_variable

Makes sense they have to be shared.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Would that solve the problem? Maybe not because you would have to rip
apart the plan, wouldn't you?

--
Bruce Momjian | 830 Blythe Avenue

I checked it. It's all already there. The rewrite handler
checks for all the range table entries he adds to the query,
if the rule owner has the appropriate permissions
(read/write). And it sets the skipAcl field for these rte's
so the executor later will ignore them.

The only thing missing is that CREATE VIEW and CREATE RULE
require write access to pg_rewrite (create view does a create
table and create rule internally). But we cannot grant write
access on pg_rewrite to looser users. So I think a global
variable, turning off the whole ACL system temporary, would
do.

I understand. We don't have APPEND rights separate from MODIFY rights.
Actually, I think they are defined in the system, but not used. We have
a similar problem with pg_database. People who can create databases can
remove other database entries from pg_database.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thus spake Bruce Momjian

Remember some tables are shared with all databases. Makes things more
difficult.

I didn't realize that. I assume that you don't mean some system tables
as they could, I am sure, be special cased. Can you expand? I once
had a situation where a table in one database was trashed by being filled
with data from another table of the same name in another database. Was
I bitten by this feature somehow? At the same time I like the idea of
sharing tables. It would be perfect for things like time zone and
province/state tables.

See pgsql/data/ and you will see the tables that are shared:

pg_database
pg_group
pg_shadow
pg_variable

Makes sense they have to be shared.

Sure. I guess that had nothing to do with my problem then. I guess it
also means that I can't share tables myself like I mentioned. Oh well.

So could we special case these tables then and let the database owner
create views on the rest?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.