Re: PostgreSQL security concerns

I have to apologize. Its clear that I did not sufficiently test
accessibility with a non-owner postgresql account. Since I was able to
see the list of relations I guess I just assumed that I had access to
them. This message got me looking a little deeper and I found that the
documentation for GRANT and REVOKE seemed to imply that only the owner
would have any real access. Further testing seems to indicate that this
is the case and so the default is exactly what I wanted. ;) I knew I
should think a couple of more times before posting. Thanks everyone for
your responses.

Ken Causey

>>>>

<excerpt>In article
<<3(dot)0(dot)1(dot)32(dot)20010531103344(dot)0168f98c(at)pop3(dot)premiernet(dot)net>, "Ken

Causey" <<ken(at)ineffable(dot)com> wrote:

> OK, I am aware of this file. I need to provide a little more detail.

>

> The situation is that of a shared webserver and a shared SQL server.

> Access to the SQL server is limited to the webserver already. Users
can

> only run CGI scripts which will of course execute as the webserver
user.

> What I'm looking for is restricting access by postgresql user. All

> logins will be coming from the same host and same host user. I don't

> see this capability as part of pg_hba.conf. Did I miss it?

You can restrict access on a table-by-table basis using the SQL

GRANT command. For instance, for web access using Apache server

side includes, the user nobody must have the appropriate access. I

allow web users to read a database but not change it, so for any table

a web user might need to read I run the command:

GRANT SELECT ON <<table name> TO nobody ;

Of course, for this to work the user 'nobody' must be already a

PostgreSQL user. BTW, the opposite of GRANT is REVOKE, which

you can use to revoke a database privilege for a PostgreSQL user.

</excerpt><<<<<<<<