Revoking usage of pg_catalog

Revoking usage of pg_catalog

am 09.05.2007 15:05:21 von Daniel Cristian Cruz

------=_Part_130501_22049601.1178715921332
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_130501_22049601.1178715921332
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_130501_22049601.1178715921332--

Re: Revoking usage of pg_catalog

am 09.05.2007 16:52:18 von Tom Lane

"Daniel Cristian Cruz" 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Revoking usage of pg_catalog

am 09.05.2007 19:52:59 von Scott Marlowe

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.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Revoking usage of pg_catalog

am 09.05.2007 20:09:41 von Daniel Cristian Cruz

2007/5/9, Scott Marlowe :
> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Revoking usage of pg_catalog

am 10.05.2007 01:47:57 von John DeSoi

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend