Postgresql – Users groups/permissions when working with PostgreSQL

permissionspostgresql-9.1

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

Best Answer

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

Related Solutions

Postgresql nested row_to_json not working

What about:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select row_to_json(row(array_agg(tweets))) from tweets;

This is actually one of the really cool things about PostgreSQL, arrays, and complex types. One can aggregate them and then convert. You should get a structure which is effectively:

tweets[] (i.e. an array of tweet tuples) which then get should converted to JSON properly).

Now, if you need a more complex structure than this, you probably want to define types separately. Something like:

CREATE TYPE tweet_set AS (
    set_id int,
    tweets tweet[],
    generated_at timestamp, 
    ....
);

And then come up with a query to generate that, and then convert it to JSON.

One of the basic issues is that since row_to_json expects a record type, you have to pass in a record, not an anyarray. You could also do:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select array_to_json(array_agg(tweets)) from tweets;

How should permissions for anonymous users be modelled

I'd go for option 1. With this approach there are no "hacks" in the app for anonymous users. The standard security model continues to apply with the only difference being that if you don't know who the user is (ie, they haven't authenticated) that you pretend they are the anonymous user, which in effect they are.

Option 1 is also the approach used by most (all?) web servers and file servers: they use an "anonymous" or "nobody" user.

Best Answer

Related Solutions

Postgresql nested row_to_json not working

How should permissions for anonymous users be modelled

Related Question