From: Michael Fuhr <mike@fuhr.org>
To: Tad Marko <tmarko@metrosplash.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Limiting user privileges
Date: Mon, 10 Jan 2005 11:31:11 -0700
Message-ID: <20050110183111.GA2279@winnie.fuhr.org> (raw)
In-Reply-To: <1105377550.11245.0.camel@boojiboy.metrosplash.com>
References: <1105377550.11245.0.camel@boojiboy.metrosplash.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mike@fuhr.org, tmarko@metrosplash.com
Subject: Re: Limiting user privileges
In-Reply-To: <20050110183111.GA2279@winnie.fuhr.org>
References: <1105377550.11245.0.camel@boojiboy.metrosplash.com> <20050110183111.GA2279@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox