Re: Limiting user privileges

On Mon, Jan 10, 2005 at 11:19:10AM -0600, Tad Marko wrote:

> I'm coming to postgresql from mysql, so I have a few preconceived
> notions that are causing me some trouble.

Preconceived notions will do that.  Execute a DROP PRECONCEIVED
NOTIONS statement.

> Specifically, I'm trying to create users that have limited privileges
> on one database. It appears that by default, a new user has a lot
> of privileges all over all the databases.

In typical default configurations users can connect to any database
and they have CREATE and USAGE privileges on the "public" schema.
See the "Schemas" section in the "Data Definition" chapter of the
documentation, in particular "The Public Schema" and "Schemas and
Privileges."  See also the "Client Authentication" chapter, the
"Template Databases" section in the "Managing Databases" chapter,
and the documentation for GRANT and REVOKE under "SQL Commands" in
the "Reference" part.

> 1) How can I remove all privileges from all databases for a given user?

A user's ability to connect to a database is based on pg_hba.conf
(see the "Client Authentication" chapter), so you can configure
that file to define which users can connect to which databases.

Within each database you can use GRANT and REVOKE to set privileges
on the "public" schema and on other database objects (schemas,
tables, views, etc.).  For example, you could execute "REVOKE CREATE
ON SCHEMA public FROM PUBLIC" to remove CREATE privilege on the
"public" schema from all users (the PUBLIC keyword).  You could
even DROP the "public" schema if you don't need it.

You can change how newly-created databases are set up by modifying
the template1 database.  See "Template Databases" as mentioned
above.

> 2) If I put back insert, delete and table creation privileges only on
> one database for a given user, will that user magically have all sorts
> of privileges on any databases that are created after doing #1 above?

No, not if you've prevented the user from connecting to the other
databases or revoked whatever privileges they had there (or created
the database after modifying template1 so users have limited
privileges by default).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/