Re: I'm in need of something that should be there

Ralph Smith wrote:

> And should be easier to find in the manual!
>
> I've looked in many related chapters of the 8.2 manual for a way to
> find out
> WHY a specific user has access to a database.
>
> Chapter 5    Data Definition
> Chapter 18  Database Roles & Privileges
> Chapter 20  Client Authorization


> postgres=# select * from pg_roles;
>   rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb |
> rolcatupdate | rolcanlogin | rolconnlimit | rolpassword |
> rolvaliduntil | rolconfig |  oid
> ----------+----------+------------+---------------+-------------
> +--------------+-------------+--------------+-------------
> +---------------+-----------+-------
> lines removed
>   smithrn  | f        | f          | t             | t           |
> f            | t           |           -1 | ********    |
> infinity      |           | 16393
>
> This user can connect via his .pgpass or manually since he's in a
> netID range that requires a password.
> But he can create and drop tables in any database!!!
>
> Why is that?
> How can I find out what he can do?
> The GRANT and REVOKE sections say nothing about which pg_xxxx tables
> to query, and I've been lookin'!
>
>
> Thank you!
>
> Ralph Smith
>
> =====================

http://www.postgresql.org/docs/8.3/interactive/sql-grant.html

"Depending on the type of object, the initial default privileges might
include granting some privileges to PUBLIC. The default is ...  CONNECT
privilege and TEMP table creation privilege for databases"

http://www.postgresql.org/docs/8.3/interactive
/ddl-schemas.html#DDL-SCHEMAS-PUBLIC

Note that by default, everyone has CREATE and USAGE privileges on the schema
public. This allows all users that are able to connect to a given database
to create objects in its public schema. If you do not want to allow that,
you can revoke that privilege:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;