Thread: 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
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 >
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