Basic PostgreSQL administration questions? (pg_hba.conf, roles, grants, etc) - Mailing list pgsql-novice

I've been using PostgreSQL for a while, but now I'm trying to really
wrap my head around the ins and outs of each layer, and how to properly
secure each layer and grant selective access.  Conceptually, I'm seeing
pgsql security as a series of layers that have to be gotten past in
order to get at the data.

Specifically I have questions about:

- minimum privileges needed at the database level? (CREATE, CONNECT,
TEMPORARY)

- minimum privileges needed at the schema level?

But I'd also appreciate feedback on whether I've misunderstood
something.  I'm fairly confident in my understanding of firewalls and
the pg_hba.conf file, but it gets hazy after that point.

A) O/S level stuff (firewall) - This layer is mostly about allowing
access to the TCP/IP port that PostgreSQL is listening on.  With
IPTables, even though PostgreSQL might be configured to listen on
5432/tcp, you won't be able to connect if iptables does not allow that.

In CentOS/RedHat, this is controlled by the /etc/sysconfig/iptables
file.  Other distros or firewalls control things using a different file.
  Use of "nmap" is one way to verify that PostgreSQL is listening on the
defined port and that the firewall will allow communication.

B) The pg_hba.conf file.  This is the next layer of security.  In order
to connect to a database, you must authenticate in a manner that is
allowed by the pg_hba.conf file.

Attempting to connect to your database using pgAdmin III typically
results in a very good error message if access is denied due to the
pg_hba.conf file.  So this layer is easy to troubleshoot.

Changes to the pg_hba.conf file will require PostgreSQL to be reloaded
(in CentOS/RedHat, this can be done with "service postgresql reload").

C) Logging in. User roles that have LOGIN privileges.  The role that is
being used to authenticate must have LOGIN privileges.  The password
must match what is in the cluster's authentication list (assuming that
you are not using "trust").

D) Here's where it gets hazy.  What privileges are needed at the
database level?  We have CREATE, CONNECT, and TEMPORARY privileges that
can be granted at this level.  What is the minimum privilege needed in
order to be a read-only user?

My guess is that you need to have CONNECT at a minimum, and that this
privilege works in addition to being allowed to connect to this database
in the pg_hba.conf file.

CREATE seems like it would only be used in cases where the user needs to
be able to create new schemas.  So very few roles will be granted this
privilege (depending on the use case).

TEMPORARY seems to be only useful in certain situations as well and that
it would not be a required privilege for SELECT-only users.

E) Schema level.  Do privileges actually get enforced at the schema
level?  Can you restrict users to only being able to read/write to
tables in a specific schema?  Or do you have to define those privileges
at the table level?

There's only two privileges at the schema level.  Right?  CREATE is
pretty obvious and explained well in the documentation (under "GRANT").
  The USAGE privilege is a bit hazy, but doesn't seem like its needed
for a read-only table user?

So a read-only user would not be granted either privilege at the schema
level?

If the tables are contained in a non-public schema, do the rules change
a bit?

F) Table level.  These are back to being clear-cut.  The list of SELECT
| INSERT | UPDATE | DELETE | REFERENCES | TRIGGER is very well explained
in the documentation.

...

If I've understood everything correctly, then the sequence to setup a
read-only user, with limited access to a few tables would be:

CREATE ROLE username WITH LOGIN PASSWORD 'password';

GRANT CONNECT ON DATABASE dbname TO rolename;

GRANT SELECT ON schemaname.tablename TO rolename;