CODEWITHSUNDEEP
postgresqlaccess-controlwhitelist
Morpheu5
Morpheu5

Reputation: 2801

How do I whitelist access to a PostgreSQL schema?

I have a certain schema in a PG database that contains some very sensitive data. I'd like to prevent access to it for every role except one, but I can't figure it out from the documentation on permissions.

To begin with, I figured I'd only give CONNECT access to that one special role, but then every other role can connect to the schema. To make things worse, every role can also CREATE, DROP, and do everything else.

What have I missed?

Side question: in time, this schema will be a streaming destination from another instance of postgresql. In short, we have a master db server that supports a live web site, and we need a secondary, read-only copy of it on another machine to perform some computationally intensive queries on. We figured that streaming was the answer. Does this make sense? Is it still possible to protect access to it?

We are using version 9.5, in case this is relevant.

Upvotes: 3

Views: 12935

Answers (1)

Gab
Gab

Reputation: 3520

You can edit the file /etc/postgresql/9.5/main/pg_hba.conf and put this content:

# Database administrative login by Unix domain socket
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             YOUR_USER       [USER_IP]/32            md5

It will allow connections with a password for this specific user from this specific LAN/WAN IP.

Only this user and postgres will be allowed.

in time, this database will be a streaming destination from another instance of postgresql.

When this database becomes a replication master you can add the following to the same file:

# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     REPL_USER        [REPL_HOST]/32            md5

More info on how to set up the replication here: https://www.gab.lc/articles/replication_postgresql

After the changes you need to reload PostgreSQL with:

service postgresql reload

You can drop the roles you don't want to allow.

Update:

If you wish to revoke privileges with a query you can run:

-- Grant privileges to whitelisted user:
GRANT ALL PRIVILEGES ON [database name] TO [good_user];

-- Revoke privileges for other users:
REVOKE ALL PRIVILEGES ON [database name] FROM [bad_user];

Upvotes: 4

Related Questions