Postgresql – Postgres role without attribute Login

The sad truth is that you cannot do anything computerwise about the 'sa' activities. This is because the 'sa' (or a member of the sysadmin server role) are defined to be able to do pretty much anything.

So, of course, you should only have sysadmin's that you can trust to do their best to adhere to the standards for the database. But even the best sysadmin is imperfect. So you might set up some auditing to report on changes made in the database.

(Or, more low tech, periodically check your views for a restricted table name in a view.)

So it depends on what you are concerned about and how much effort you need to put into protecting the secure resources.

In terms of views that expose secure information, you can DENY permissions on a view. Even if a sysadmin created the view, the DENY of the view for those without approval will prevent their access.

Perhaps any high security views could be named something like SecureViewXYZZY so as to emphasize the reason for those views and the restriction of who can use them.

All these questions are basically answered in the documentation. To be specific:

A role is an entity that can own database objects and have database privileges; a role can be considered a "user", a "group", or both depending on how it is used.

That implies that internally there is no difference between these, except the LOGIN option. If you specify LOGIN (or use CREATE USER):

CREATE ROLE dezso WITH LOGIN PASSWORD 'bla';

dezso will become a login role, otherwise not. You can specify passwords for non-login roles, too - if you later decide to make it a login role, use

CREATE ROLE non_login_role PASSWORD 'bla';

--later
ALTER ROLE non_login_role WITH LOGIN;

You are right when saying 'it's only possible to authenticate with login role'. Note, however, that a password is not always necessary - if you use anything else than the password or md5 authentication methods. On the other hand this means that a role without the LOGIN option set cannot log in in any way.

Finally, you may set rules in pg_hba.conf for non-login roles, but as it's used only for authentication, those roles will have no effect (unless you enable login later, as above).