PostgreSQL – Is It Possible to Block Super User Access?

postgresql

In a scenario that the databases run on the client's server.

Company "A" has a database installed on that server. The customer hires the services of a company "B", which will also install its database on the server.

If company "B" uses a super user, it can have access to the company "A" database. It may even try to harm company "A" by changing information in the database.

Is there a way to block super user access to company "A" database?

Best Answer

That should be easy. Put the databases in different PostgreSQL clusters on the same server.

But even if both databases are in the same cluster, a superuser can only access the database if there is a line in pg_hba.conf that allows it. So you can achieve the goal with a properly configured pg_hba.conf file. That requires that the superuser is known by name – you cannot add a line that says “block all superusers from that database”.

Related Solutions

Postgresql – way to access temporary tables of other sessions in postgres

The lack of access to temporary tables in other sessions is not a matter of permissions, it's a technical limitation of the design. A PostgreSQL backend can't access temporary tables of another backend because none of the usual housekeeping to allow concurrent access is done for temporary tables.

In 9.2 you will want to use an UNLOGGED table instead; this can be visible from other sessions, but retains most of the performance benefits of a temporary table.

Postgresql – Postgres Server access restricted to only one database from list of databases

If you make changes to pg_hba.conf, you need to reload PostgreSQL.

But I think you'd be better off revoking the CONNECT privilege for that user. (Or possibly revoking ALL privileges.) Skeleton syntax is

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

Something like

REVOKE CONNECT ON DATABASE database-name FROM fishy_user;

In PostgreSQL, each role (user or group) can be granted permission to access different databases if that's what you want to do. See Database Roles

Best Answer

Related Solutions

Postgresql – way to access temporary tables of other sessions in postgres

Postgresql – Postgres Server access restricted to only one database from list of databases

Related Question