Thread: a few simple questions

a few simple questions

From
Donald Ball
Date:
hiya guys. i'm new to postgresql but not to sql servers, UNIX, or open
source software projects. i have a few simple questions:

1. is there a FAQ for postgresql in general or pgsql-admin in
particular? how about archives of the lists on the web?

2. the documentation states that when a database is created, only the
owner has any permissions on that database. however, when i access that
database as another user, i can do anything i want. what gives?

3. how do you change a postgres user's password?

4. if someone's inclined to do some handholding, i'm trying to create a
system consisting of a database and two postgres users (not real UNIX
users). one should be able to insert, update, delete, and select, while
the other should only be able to select. neither should be able to create,
drop, or alter tables. both should be able to connect from a specific
external ip address after authenticating themselves with an encrypted
password. of course, if no one's inclined to hold my hand, i'm certain
i'll muddle my way through it eventually.

i'm using the postgresql-7.0.2-2 RPMs from ftp.postgresql.org. i have read
or skimmed the postgresql documentation as well as the Bruce Momjian's
book. i feel pretty stupid that i wasn't able to answer these basic
questions on my own given the breadth and depth of the documentation
available; i'm grateful for any assistance y'all can provide.

- donald


Re: a few simple questions

From
"Ryan Williams"
Date:
1.  A FAQ can be found at http://www.postgresql.org/docs/faq-english.html,
the a book is at http://www.postgresql.org/docs/awbook.html, searchable
archives of lists at http://www.postgresql.org/gen-info.html and lots of
documentation accessable via postgresql.org under the 'user's lounge'.
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.
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
4.  What exactly do you need help with?

----- Original Message -----
From: "Donald Ball" <balld@webslingerZ.com>
To: <pgsql-admin@hub.org>
Sent: Tuesday, August 29, 2000 9:23 PM
Subject: [ADMIN] a few simple questions


> hiya guys. i'm new to postgresql but not to sql servers, UNIX, or open
> source software projects. i have a few simple questions:
>
> 1. is there a FAQ for postgresql in general or pgsql-admin in
> particular? how about archives of the lists on the web?
>
> 2. the documentation states that when a database is created, only the
> owner has any permissions on that database. however, when i access that
> database as another user, i can do anything i want. what gives?
>
> 3. how do you change a postgres user's password?
>
> 4. if someone's inclined to do some handholding, i'm trying to create a
> system consisting of a database and two postgres users (not real UNIX
> users). one should be able to insert, update, delete, and select, while
> the other should only be able to select. neither should be able to create,
> drop, or alter tables. both should be able to connect from a specific
> external ip address after authenticating themselves with an encrypted
> password. of course, if no one's inclined to hold my hand, i'm certain
> i'll muddle my way through it eventually.
>
> i'm using the postgresql-7.0.2-2 RPMs from ftp.postgresql.org. i have read
> or skimmed the postgresql documentation as well as the Bruce Momjian's
> book. i feel pretty stupid that i wasn't able to answer these basic
> questions on my own given the breadth and depth of the documentation
> available; i'm grateful for any assistance y'all can provide.
>
> - donald
>


Re: a few simple questions

From
Donald Ball
Date:
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