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'm new in PostgreSQL and looking to find if this is possible.

There are two superusers, 'postgres' (which is default superuser from PostgreSQL) and 'super2'.

Superuser 'super2' creates a new database named 'Movies' and being the owner of it.

Is it possible to restrict superuser 'postgres' to access the database 'Movies' so that only superuser 'super2' could access it, because he is the owner of the database.

If it is possible, then how to make it happen?

share|improve this question

4 Answers 4

Postgres is a Superuser which override all access restrictions ,I thinks it's not possbile to restrict the superuser 'postgres' to access the database 'Movies'

share|improve this answer
    
I'm still waiting the one who knows how to do it, hopefully it is possible.. –  bagz_man Jul 12 '13 at 2:57
1  
@bagz_man Then don't hold your breath while you wait, because it's a bit like waiting for someone who knows how to stop the burglar from breaking into your house when you gave them the address and a pair of keys. –  Craig Ringer Jul 12 '13 at 8:07
1  
@CraigRinger Nice. I imagine superuser as Judge Dredd. He has unlimited power, access to all houses (databases), can kill any other user (other judges included), give keys to burglars ... bagz_man: If you don't want someone to have such power, don't make him Judge. –  ypercube Jul 12 '13 at 8:18

francs is entirely correct. superuser means just that. They're all-powerful. They can do anything, including load additional code into the database, modify tables on disk directly, etc. See CREATE ROLE and the documentation on client authentication for more information.

If you don't trust them, don't give them superuser rights. In this case, it sounds like you should've just done a CREATE DATABASE movies WITH OWNER the_other_user and given them a normal, non-superuser login. Or if they need to create their own databases, you could give them CREATEDB rights.

The only way to restrict a superuser is by changing the C code inside PostgreSQL directly. Even then you'd probably be wasting your time, as a determined user could get around restrictions like a ProcessUtility_hook filter if they have superuser access.

Remove their superuser access. Unless they've had the foresight to backdoor your system in a way that'll let them regain access (unlikely, and not trivial) you should be OK.

ALTER USER the_user WITH NOSUPERUSER;

You can add CREATEDB rights if you want them to have the ability to create databases.

share|improve this answer

You have already been told that you cannot stop a superuser. You have to aim higher: create another database cluster using initdb (which will run on a separate port).

Users are valid cluster-wide. A superuser in one cluster has no "jurisdiction" in another cluster.

share|improve this answer
2  
Very good point - running a separate cluster on a separate system user account will allow you to isolate the other superuser. It's vital that you use a separate user account, otherwise they can bypass the restrictions with a bit of direct file modification work. –  Craig Ringer Jul 13 '13 at 3:50
    
Nice solution.. –  bagz_man Jul 14 '13 at 17:14

You can "reject" the postgres user for your specifique database using pg_hba.conf.

sample for the database no_postgres (you need to reload your server) :

local   no_postgres     postgres        reject
host    no_postgres     postgres        0.0.0.0 255.255.255.255 reject

And now :

postgres=# create database no_postgres;
CREATE DATABASE
postgres=# \c no_postgres 
FATAL:  pg_hba.conf rejects connection for host "[local]", user "postgres", database "no_postgres"
Previous connection kept
postgres=# 
share|improve this answer
1  
The problem with this is that postgres is still able to drop the database it can't log into. –  dezso Jul 15 '13 at 13:12
    
It's true.But the question : "to restrict superuser 'postgres' to access the database 'Movies'" –  arthurr Jul 15 '13 at 13:44

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.