Security - PostgreSQL 9 Administration - page 130

Giving limited superuser powers to specific
users
First, the superuser role has some privileges, which can also be granted to non-superuser
roles separately.
To give the role Bob the ability to create new databases, run the following:
ALTER ROLE BOB WITH CREATEDB;
To give the role bob the ability to create new users, run the following:
ALTER ROLE BOB WITH CREATEUSER;
However, it is also possible to give ordinary users more fine-grained and controlled access to
some action reserved for superusers by using SECURITY DEFINER functions. The same trick
can also be used for passing on partial privileges between different users.
Getting ready
First, you must have access to the database as a superuser in order to delegate some powers.
Here, we assume the use of the default superuser named postgres.
We will demonstrate two cases of making some superuser-only functionality available to select
an ordinary user.
The database must have support for embedded language PL/pgSQL installed. Starting from
PostgreSQL 9.0 the recommended default behavior is to have pl/pgSQL installed in newly
created database, but this can be changed by package creators or site administrators. If it is
not, run the following as PostgreSQL superuser:
test2=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE
How to do it...
One thing that a superuser can do, and ordinary users cannot, is telling postgres to copy table
data from file:
pguser@hvost:~$ psql -U postgres
test2
...
test2=# create table lines(line text);
CREATE TABLE
test2=# copy lines from '/home/bob/names.txt';