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?
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.
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.
# 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.
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;
/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
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;
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
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.
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';