Postgresql. User rights superuser. Access control?

Good afternoon.

(1)it is Necessary in postgresql to limit access to a particular schema or table. It must be done so that the administrators of the DB, including owning the account superuser had access rights to this schema to read from it, etc., in General any rights she had. Access should only be provided for specific roles or users (in the schema of the stored sensitive data) through a thin-client.
If you can implement it and how?
(2) can I encrypt data at rest?
Thank you.
April 19th 20 at 12:34
3 answers
April 19th 20 at 12:36
No you can not. Only if you encrypt the data in the intermediate layer but in this case, the loss of a key will result in the loss of all data.
i.e. data alone cannot be encrypted? - Willard commented on April 19th 20 at 12:39
April 19th 20 at 12:38
Stand in need of restriction of access to the place where you will not be beyond the control of superuser.
in General, there is the admin database, they should not see the data in the schema. Is it possible in one database to do this? - Willard commented on April 19th 20 at 12:41
@Willard, at administrators, at least, have access to opportunities to make a dump to triplicate entire cluster somewhere, make snapshot of FS, etc.

What you want - some local contrivances can not be solved only by the separation of entities. - robert commented on April 19th 20 at 12:44
@robert, for example? what are the entities involved? - Willard commented on April 19th 20 at 12:47
@Willard, in the case of a database - any physically separated from the data source with its set of accesses. In the particular case the other database to which there is no access from those administrators from whom you want to secure your data.

https://wiki.postgresql.org/wiki/Foreign_data_wrappers - robert commented on April 19th 20 at 12:50
@robert, thanks for the link, will read it.
Do I understand correctly that in case of usage of FDW is created in the destination database entity (table, schema) that defines the structure of the remote data (source)? Ie in fact this is the view to the remote database? - Willard commented on April 19th 20 at 12:53
April 19th 20 at 12:40
There is such a solution in Oracle. Called OLS (Label Security) and allows to organize a military level of security. So that even DBA do not see the data rows.

In PostgreSQL this seems to present. But you can at the client level to encrypt all data. Among the shortcomings.
Worse performance (all string data you have to keep in base64 wrapper) and strongly prosyadet efficiency index search. If it ever will be.

In General you cheaper to buy in Amazon your PG server and pay for it yourself.
Thank you. Oracle had to deal with in terms of security. Postgresql has faced just now, I see this things worse. On the client side, unfortunately, there is no possibility to encrypt. I would think. - Willard commented on April 19th 20 at 12:43
By the way, theoretically, if we encrypt data on the client side (e.g., AES 128) and pass in the target database, it will be possible to reflect user need in the web interface (browser)? - Willard commented on April 19th 20 at 12:46
Someone has to decrypt again. And it's definitely not a browser. - princess_Bradtke commented on April 19th 20 at 12:49

Find more questions by tags Information securityPostgreSQL