Postgres Administration roles

Question

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.

Answer 1

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.

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'test';