Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to work with a remote postgresql server. My application needs to perform the following actions:

  • to be able to create/drop database (I only need to work with 1 specific database)
  • to be able to lock db for new connections / to be able to drop all connections.
  • to work with the DB directly (SQL queries)

Assuming that I can request an administrator user for the DB I am going to work with, and that I'm not going to get a user with administrative permissions for the complete DB server, what is the possible solution for this matter? Is it possible to have one user that can do all that I need?

Thanks.

share|improve this question
    
An application that needs to create or drop a database? Sounds like a questionable design. What kind of application is that? –  a_horse_with_no_name Feb 12 '12 at 17:45
    
A one that creates or drops DB. I can manage without creating, but I need to be able to drop all the data from the DB, even if there are connections to it ... –  Alex Feb 12 '12 at 22:09
    
Can you please give the version of the postgresql server you're using? 8.4, 9.0... –  Craig Efrein Feb 13 '12 at 13:09
add comment

1 Answer

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.

  1. PostgreSQL is on the same server as the application.

  2. PostgreSQL is running on a distant server.

  3. Locking example.

  4. 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 in postgresql.

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'test';
share|improve this answer
    
Erwin Thank you for the response. I will continue your train of thought, and talk about the case #2 (remote DB). Now that I'm an administrator, I need to perform an upgrade/rollback/cleanup actions. How can I do that? Assume that I have active connections to the DB but I don't care to disconnect them. –  Alex Feb 14 '12 at 11:01
    
Ah, Craig, sorry. I use Postgres 9 for this issue. –  Alex Feb 14 '12 at 11:03
    
Let me know if you have any questions about my answer. I tested everything I gave you on 9.1, so you shouldn't run into any compatibility issues. –  Craig Efrein Feb 14 '12 at 11:07
    
Craig You're right, I have no compatibility issues. But is there a way to disconnect all users from my remote DB without superuser privileges? –  Alex Feb 15 '12 at 14:31
    
You have to be a superuser to disconnect other users. Otherwise you get the error: must be superuser to signal other server processes. –  Craig Efrein Feb 15 '12 at 15:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.