Users groups/permissions when working with PostgreSQL

Question

While I've been working with PostgreSQL for approximately 1 year, I still feel like a bit of a Newbie. I feel like I'm constantly fighting with psql and pg_dump when it comes to files and permissions. For example, just trying to do a "COPY FROM" and not having permissions to access the file.

How do dbas normally set up their users, etc to make life "easy" (or at least a little less painful).

Also, to "come clean", I have spent most of my career working on Windows. I now primarily work on Linux. I would probably give my self a D+ or a C- (always dangerous to try and self grade) when it comes to understand Linux user/groups/permissions. I'm not completely in the dark and can usually make things work (or at least understand a blog post that I find by Googling a problem) - but I'm certainly not a pro.

I'm currently using PostgreSQL 9.1.5 on Ubuntu 12.04

Answer 1

There are two basic approaches to users and groups. I will also describe what approach I favor here and why.

The first approach is to use login roles to describe what software is logging on and restrict rights accordingly. This has the advantage of being relatively simple to maintain and a number of disadvantages as well, but it is probably the most common approach because of its administrative simplicity.

The second approach, which I favor, is to give every user his or her own role. DB permissions are then attached to groups (i.e. nologin inherit roles) which are granted to individual users. This is more complex to administer. Keep in mind that unlike Linux, PostgreSQL groups can be nested and unlike Windows, there is no deny acl, so permissions are always fully cumulative. In some cases where someone needs a restricted account we can create a separate user. This adds complexity to the administration process but it also helps mitigate security issues in the application because something like a sql injection attack is less likely to have privilege escalation possibilities, and it essentially allows outsourcing of authentication to the database, and PostgreSQL can outsource this to a variety of sources (RADIUS, PAM, KRB5, GSSAPI, SSPI, etc).