The gist of the discussion being: what are the ramifications of having PostgreSQL and psql(1) hide information/schema bits that a user doesn't have access to. This would have to be backend enforced, which would mean changing the system catalogs to include some form of row level security so that SELECTs from the system catalogs would only return the rows that a user has privs to see. In more practical terms, in psql(1), the consequences would be:
\dn only shows schemas/namespaces that a user has access to
\df could only show functions that a user can execute (or are visible by namespace)
\dt only shows tables that you have some kind of privileges on
etc. Not much can be done about \l. That said, what are the thoughts of the other developers/admins? Is hiding schema information a good thing? Do people think that the concept of "secure by default" should apply to schema information inside of the database? Should information hiding be done in psql(1) or should this be managed by the backend and all logic kept out of psql(1)? For sure, the advantage of having it managed in the backend is that *all* clients (psql, pgadmin, phpPgAdmin, etc.) would pick up the schema structure hiding.
[ Thread from patches@ + response below ]
This patch does two things:
1) Changes the semantics of assign_search_path()/'SET search_path' so that you can't set your search path to a schema you don't have USAGE privs for.
Why is that needed? It's already a no-op AFAIR. It also is incompatible with the existing behavior, in which nonexistent schemas (think "$user") are dropped silently rather than noisily.
Actually, $user still works... and shouldn't:
test=# CREATE SCHEMA usr;
CREATE SCHEMA
test=# \c test usr
You are now connected to database "test" as user "usr".
test=> SET search_path = '$user';
SET
test=> \dn
List of schemas
Name | Owner
--------------------+-------
information_schema | sean
pg_catalog | sean
public | sean
(3 rows)When the list element is '$user', the loop skips processing and doesn't try and resolve $user to usr and then test to see if usr exists.
Your patch
also breaks the previous careful tweak to allow ALTER DATABASE SET
to succeed when mentioning a schema not present in the current database.
This I haven't investigated... Should you be able to run ALTER DATABASE SET if you're in a different database? *shrug* I'd say no, but I'm biased because I've never used ALTER DATABASE SET outside of the current database. Would it be acceptable to have all SET commands succeed without checking if the current database is different than the database that's being acted on? That's no different than the current behavior.
2) Changes psql's \dn query and its schema tab completion query to
incorporate ACL checking so that \dn only lists schemas that a user has
USAGE privs on.
This requires considerable discussion.
Okey dokey... sending email to [EMAIL PROTECTED]
Should \df only list functions you are allowed to call? \dt only tables you are allowed to read? \h only commands you are allowed to execute?
IMHO, yes... but \h doesn't execute a query and shouldn't exercise any control on what's shown/displayed. PostgreSQL is a rather open database to non-admins. If PostgreSQL were a file system and a database cluster is the root node, the layout would essentially be:
/[databases]/[schemas]/[tables]/[columns]
and the perms on that file system would be:
find / -type d -exec chmod 755 {} \;
find / -type f -exec chmod 600 {} \;and schemas would be mod 711 (find / -type d -depth 2 -exec chmod 711 {} \;). Users can essentially browse the structure of the entire file system/database. As an admin (network, system, database, or otherwise), I want to expose to a user only what they need to be exposed to. Changing a database/directory so that only a user or group has access to it (mod 750/700) can only happen through modifications to pg_hba.conf. *shudders* PostgreSQL makes itself "secure" by default by turning off network access. Once a user is connected, however, PostgreSQL is an open book and reminds me of the olden days when /etc/passwd contained crypt(3)'ed passwords and home directories were created mod 755 or umasks were 0 by default and a mask of 022 was a umask for the "overly paranoid."
-sc
-- Sean Chittenden
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
