Re: a few simple questions - Mailing list pgsql-admin

On Wed, 30 Aug 2000, Ryan Williams wrote:

> 2.  My understanding is that permissions are based on objects within the
> database, not the DB itself...  Example:  if user_a creates a table called
> table_a in a DB, upon connection to the DB, user_b should not be able to
> perform DB functions (such as select, update, delete, etc.) on table_a
> unless specifically granted permissions to do so by user_a (see GRANT in the
> PostgreSQL User Docs).  On another note, by adding entries in pg_hba.conf,
> you can set up PostgreSQL to allow/reject connections to specific DBs from
> specific ips (or blocks of ips) or usernames, while rejecting all other
> connections.

then how can i set up a strictly read-only user? supposing, for the sake
of argument, that i'm going to hand out the username and password for this
user to the general public. what's to prevent someone from trying to max
out my hard drives by creating huge numbers of junk tables with junk data?

also, how do you limit connection on a per-user basis in pg_hba.conf
(without relying on an external password file, that is)?

> 3. Try ALTER USER username WITH PASSWORD 'password', where username is the
> name of the user whose password is to be changed, and password is the new
> password.  (From the PostgreSQL User Docs:) Only a database superuser can
> change privileges and password expiration with this command. Ordinary users
> can only change their own password

geez, how did i miss that? ah, it's not in the admin guide, it's only in
the user guide. i'd humbly suggest that someone add that to the user
management section of the admin guide.

> 4.  What exactly do you need help with?

i think i got it covered now, assuming i can figure out a way to prevent
public user from creating tables. my steps should be:

su - postgres
createdb foo
psql foo
create user foo_public;
create user foo_admin;
alter user foo_public with password 'secret';
alter user foo_alter with password 'secret';
\connect foo foo_admin
create table foo_table (...);
grant select on foo_table to foo_public;
\q
echo "host wz 192.168.0.1 255.255.255.255 crypt" >>/var/lib/pgsql/data/pg_hba.conf
/etc/rc.d/init.d/postgresql restart

yes?

btw, if whoever is in charge of the redhat RPM build is listening - it
seems odd that the documented default behavior of the postgresql daemon is
not to even listen for network connections, while the RPM's default
behavior is to accept network connections.

does postgresql use tcp wrappers or should i strictly secure it at the OS
level using ipchains?

- donald