Postgres Pro
Downloads
Home   >   mailing lists

Thread: Preventing database listing?

Preventing database listing?

From
Adam Rich
Date:
This seems like a simple question that would have come up, but I'm not
able to find an answer in google, PG docs, or PG mailing list archives.

How do I prevent a user from being able to list all databases in my
cluster?  I want to restrict them to seeing just the databases they have
connect rights to.

Thanks
Adam

Re: Preventing database listing?

From
Sam Jas
Date:
Below are the options that you can use to create user and assign them privileges according to your environment.

Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE rolename [, ...]
    | IN GROUP rolename [, ...]
    | ROLE rolename [, ...]
    | ADMIN rolename [, ...]
    | USER rolename [, ...]
    | SYSID uid

postgres=# \h grant
Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCE sequencename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE langname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schemaname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespacename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT role [, ...] TO rolename [, ...] [ WITH ADMIN OPTION ]


--
Thanks
Sam DJ











--- On Thu, 22/10/09, Adam Rich <adam.r@sbcglobal.net> wrote:

From: Adam Rich <adam.r@sbcglobal.net>
Subject: [GENERAL] Preventing database listing?
To: "postgresql Forums" <pgsql-general@postgresql.org>
Date: Thursday, 22 October, 2009, 4:18 AM


This seems like a simple question that would have come up, but I'm not able to find an answer in google, PG docs, or PG mailing list archives.

How do I prevent a user from being able to list all databases in my cluster?  I want to restrict them to seeing just the databases they have connect rights to.

Thanks
Adam



-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Keep up with people you care about with Yahoo! India Mail. Learn how.

Re: Preventing database listing?

From
John R Pierce
Date:
Adam Rich wrote:
>
> This seems like a simple question that would have come up, but I'm not
> able to find an answer in google, PG docs, or PG mailing list archives.
>
> How do I prevent a user from being able to list all databases in my
> cluster?  I want to restrict them to seeing just the databases they
> have connect rights to.


the \l command that lists all databases excutes a query like...

SELECT d.datname as "Name",
       r.rolname as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
ORDER BY 1;

so, perhaps revoking read permissions on pg_catalog.pg_database, but I
have no idea what other problems that might cause