Обсуждение: Revoking usage of pg_catalog

Hi there!

Is it possible to revoke usage of pg_catalog for a specific user?

The reason is to secure PostgreSQL. If a user can connect to a database, it could query pg_class, pg_attribute, pg_proc search for specific tables and if using dblink, even database passwords...

I just made a test, revoking usage of pg_catalog from PUBLIC, but tables are still available through "SELECT * FROM pg_class", but not through "SELECT * FROM pg_catalog.pg_class". I found in manual, where it says pg_catalog is searched before any schema on search_path...

If schema pg_catalog became blocked, PostgreSQL could be used? Could it be possible to made queries on allowed schemas and tables? This could be an item for the wishlist?

Kind regards,
--
Daniel Cristian Cruz

"Daniel Cristian Cruz" <danielcristian@gmail.com> writes:
> Is it possible to revoke usage of pg_catalog for a specific user?

No, not if you'd like them to be able to do anything useful.

            regards, tom lane

On Wed, 2007-05-09 at 08:05, Daniel Cristian Cruz wrote:
> Hi there!
>
> Is it possible to revoke usage of pg_catalog for a specific user?
>
> The reason is to secure PostgreSQL. If a user can connect to a
> database, it could query pg_class, pg_attribute, pg_proc search for
> specific tables and if using dblink, even database passwords...

That's not security, it's obscurity.

You can grant / revoke access to anything a user should or should not be
able to access anyway.

2007/5/9, Scott Marlowe <smarlowe@g2switchworks.com>:
> On Wed, 2007-05-09 at 08:05, Daniel Cristian Cruz wrote:
> > Hi there!
> >
> > Is it possible to revoke usage of pg_catalog for a specific user?
> >
> > The reason is to secure PostgreSQL. If a user can connect to a
> > database, it could query pg_class, pg_attribute, pg_proc search for
> > specific tables and if using dblink, even database passwords...
>
> That's not security, it's obscurity.

Yes, I used the wrong expression.

> You can grant / revoke access to anything a user should or should not be
> able to access anyway.

It's a web application user. I was trying to make some database magic,
hardening SQL injections... But its wrong, the application must be
secure. Unfortunelly I can't have a database user for each web user...

Thanks...
--
Daniel Cristian Cruz

On May 9, 2007, at 2:09 PM, Daniel Cristian Cruz wrote:

> It's a web application user. I was trying to make some database magic,
> hardening SQL injections... But its wrong, the application must be
> secure. Unfortunelly I can't have a database user for each web user...

I don't see the issue if users don't connect directly to the
database, only through your web application. You then have complete
control over any query executed. You should not have to worry about
SQL injection if you use prepared queries and stored procedures.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL