This was tested using PostgreSQL 9.1
This is assuming that you only want to be able to manage tables, indexes and other objects from within one database.
You will need to first create your user and database as postgres or a user that has the right to create logins and databases
When you create that user, you can make him the owner of the newly created database. As the owner of the database, that user can drop and create tables as well as other objects within that database.
I have created four different sections.
PostgreSQL is on the same server as the application.
PostgreSQL is running on a distant server.
Locking example.
Disconnect users
1. PostgreSQL server is on the same machine as the application
Just make sure that the users can connect locally. trust means you don't need a password, or you can use md5 to require one.
cat /path/to/postgresql/data/pg_hba.conf
# IPv4 local connections:
local all all trust
host all all 127.0.0.1/32 trust
Change to postgres user
su - postgres
connect to PostgreSQL
Now create the user and the database
-bash-3.2$ psql -d template1
template1=# CREATE ROLE myuser WITH LOGIN PASSWORD 'test123';
CREATE ROLE
template1=# CREATE DATABASE mydatabase WITH OWNER myuser;
CREATE DATABASE
template1-# \q
Connect to postgresql as your user
-bash-3.2$ ./psql -d mydatabase -U myuser
mydatabase=> create table mytable (t int not null);
CREATE TABLE
mydatabase=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------
public | mytable | table | myuser
mydatabase=> drop table mytable;
DROP TABLE
2. PostgreSQL server is on a different machine than that of the application
Edit the authorization file on the remote PostgreSQL server. For users connecting remotely, its better to require a password.
Authorization File
vi /path/to/postgresql/data/pg_hba.conf
Add in the ip address of the server that will be connecting to this PostgreSQL server.
# IPv4 local connections:
host all all ip.address.application_server/32 md5
Edit postgresql.conf file on the remote PostgreSQL server. You need to make sure that PostgreSQL server is listening on some ip address
vi /path/to/postgresql/data/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # list on all ip addresses of the server;
# or
listen_addresses = 'ip.address.psql.server' # listen to one ip address;
Reload PostgreSQL
/etc/init.d/postgresql reload
#or
./pgctl reload -s -D /path/to/postgresql/data
server signaled
Connecting to PostgreSQL From Application server
-bash-3.2$ ./psql -h remote.ip.address.psql -U superuser -d template1
Password for user superuser:
template1=# CREATE ROLE myuser WITH LOGIN PASSWORD 'test123';
CREATE ROLE
template1=# CREATE DATABASE mydatabase WITH OWNER myuser;
CREATE DATABASE
template1-# \q
Connect to distant PostgreSQL server as new user
-bash-3.2$ ./psql -h remote.ip.address.psql -U myuser -d mydatabase
Password for user myuser:
mydatabase=> create table mytable (t int not null);
CREATE TABLE
mydatabase=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------
public | mytable | table | myuser
mydatabase=> drop table mytable;
DROP TABLE
3. Locking example
http://www.postgresql.org/docs/current/interactive/sql-lock.html
Unless I'm mistaken, locking occurs at the table and/or row level unless you can put the database into read-only or standby.
Connected as myuser
mydatabase=> BEGIN WORK;
BEGIN
mydatabase=> LOCK TABLE mytable IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE
mydatabase=> SELECT t FROM MYTABLE ;
t
---
(0 rows)
mydatabase=> INSERT INTO mytable VALUES (1);
INSERT 0 1
mydatabase=> COMMIT WORK;
COMMIT
4. Disconnect users
Find all active connections
SELECT usename, procpid, datname FROM pg_stat_activity;
Output:
test=# select usename, procpid, datname from pg_stat_activity;
usename | procpid | datname
----------+---------+---------
test | 4083 | test
Now that you have the procpid, you can terminate the connection. You must be a superuser to run the next command.
SELECT pg_terminate_backend(4083) FROM pg_stat_activity WHERE datname = 'test';
Then if you want to prevent other normal users from reconnecting you can limit the number of connections to 0. You can run this command as the db owner
ALTER DATABASE test WITH connection limit 0;
If a user attempts to reconnect, he will get this message
test=> \dt
FATAL: terminating connection due to administrator command server
closed the connection unexpectedly This probably means the server
terminated abnormally before or while processing the request. The
connection to the server was lost. Attempting reset: Failed.
On the otherhand, postgres would still be able to connect.
or
Disallow all connections, even postgres. You have to be a superuser to run this. I found this command on another post related to a similar question : Force drop db while others may be connected.
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'test';