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


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).

  • Thank you for the reply. Very interesting and a great point about there not being a "deny" acl. But, what I was really asking was more about how you (as a dba) configure your account and the postgres user to make maintenance type of issue easier. Like I mentioned, everytime I go to do a pg_dump and get an access denied message, I nash my teeth a little bit. What I've been toying with is creating a "dba" group and adding myself and postgres to that and then configuring a location where we can easily both access, etc. – David S Oct 1 '12 at 21:44
  • I just tested and it looks like SUPERUSER is not an inherited property, btw. – Chris Travers Oct 2 '12 at 0:22
  • I would therefore suggest the dba's should probably be created as superusers if that doesn't pose other issues. – Chris Travers Oct 2 '12 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.