[ADMIN] Role based database access
Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. The 'user' field does not help in this either. So is there any chance to achieve what we need without revoking the 'create database' permission and pre-creating the databases for all our users? Thanks, Thomas Mack ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Role based database access
Am Donnerstag, 24. Mai 2007 15:57 schrieben Sie: Thomas Mack wrote: Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. In the database name you can specify @file, which points to a file containing a list of database names that the pg_hba.conf lines applies to. One idea is to have the database creation routine put a line into that file (which would be specific to each user). Ok, this looks reasonable. It probably also means, postgres cannot really restrict database access based on the ownership, which is not nice in this case. But probably no one cares in 'real world' situations. Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Start-up script for Solaris
Am Dienstag, 8. August 2006 13:27 schrieb mcelroy, tim:
Good morning,
Curious if anyone out there has a start-up script for Solaris? A
version of the Linux /etc/init.d/postgresql one. I recently installed
postgres on a Solaris 9 box and although I can start up postgres it
fails to log to the log file as directed and just doesn't look right,
like it does on Linux.
I have a rather old one, which originally started and stopped a 6.5.2
postgres on Solaris 5.6 . Now it does its job on 7.4 and Solaris 10.
Thomas Mack
=
#!/bin/sh
killproc() {# kill the named process(es)
pid=`/usr/bin/ps -e -o pid,args | \
/usr/bin/grep $1\ | \
/usr/bin/grep -v $0\ | \
/usr/bin/grep -v grep | \
/usr/bin/awk '{print $1}'`
[ $pid != ] kill -s INT $pid
[ $pid != ] sleep 2
[ $pid != ] kill -s QUIT $pid
}
#
# Start/stop postgres
#
case $1 in
'start')
echo Starting postgres74...
killproc postmaster
/usr/bin/rm -f /usr/local/pgsql/data/postmaster.pid
su - postgres -c source /usr/local/pgsql/.tcshrc; limit descriptors
512; cd /usr/local/pgsql/bin; ./postmaster -i -p 6007 -o -e server.log
;;
'stop')
killproc postmaster # kill postmaster process
/usr/bin/rm -f /tmp/.s.PGSQL.6007
;;
'restart')
$0 stop
sleep 1
$0 start
;;
*)
echo Usage: /etc/init.d/postgres74 { start | stop | restart }
;;
esac
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] *** How can I install two postgresqls in the same machine? *****
Am Freitag, 14. Juli 2006 08:36 schrieb kitaeda: I want to install two postgresqls in the same machine. How can I do so? What do I have to specific configure options? You have to have them in different locations obviously, so you might use a different --prefix=... for both on configure. And you have to run them on a different port. This works for us back since a 6.5 version of postgres. Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Aborting transactions
Am Dienstag, 27. Juni 2006 15:47 schrieb Tom Lane: Thomas Mack [EMAIL PROTECTED] writes: Am Montag, 26. Juni 2006 22:15 schrieb Tom Lane: What platform is that? If it's Solaris, see our doc/FAQ_Solaris about getting more useful info from ps. Yes, it's Solaris 10. Looking at http://www.postgresql.org/docs/faqs.FAQ_Solaris.html , I did not find anything related. Same with the doc/FAQ_Solaris from the postgresql-8.1.4 sources. Oh, sorry, I assumed it was in the Solaris FAQ, but actually the info is at the bottom of this page: Ok, it might be reasonable to duplicate it in the Solaris FAQ though... http://www.postgresql.org/docs/8.1/static/monitoring.html There are several different conditions that can render ps useless on Solaris :-(, and one of them is sprinkling the postmaster start command with a lot of switches. Put that stuff in postgresql.conf, instead, so you can invoke the postmaster as just postmaster. Oh yes. This explains, why I was missing quite some connections even with /usr/ucb/ps. Might be good to tell in the documentation to move the switches to postgresql.conf . Might otherwise produce additional questions... Oh, well, the 7.4 documentation states these things already as it does in 8.1. Sorry, don't know why I missed it. Maybe I had some other ideas in mind. Not monitoring, but administering or something like this. Thanks for your help, Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] Aborting transactions
Hi, we use postgres for student's education and others. Now we had an issue with some SQL statement in connection with some triggers, that probably caused an infinite loop in processing for at least 10 hours or so on some two to ten line tables. Unfortunately, the students used the phpPgAdmin web interface, so they could not abort the running SQL statement. They could not even continue to work with the database from phpPgAdmin. So the question arose, how to abort running statements at least via the postgres account. I remember some methods from Ingres some years ago, but I did not find anything similar in postgresql. Is there any chance to monitor the current transactions and abort them, in case of need? Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
