Opened 8 years ago

Last modified 6 years ago

#3667 assigned Feature/Enhancement Request

add postgres server to mfpl standard servers

Reported by: https://id.mayfirst.org/dkg Owned by: https://id.mayfirst.org/jamie
Priority: High Component: Tech
Keywords: postgres viewsic.mayfirst.org Cc:
Sensitive: no

Description

Currently, we offer mysql service on all the standard servers.

It would be nice to offer users postgres (see #3658 for some example discussion).

The first step for this would probably be to think about disk space allocation (a separate filesystem for /var/lib/postgresql maybe ?) and adding the daemon to the relevant machines.

(maybe we want a guinea pig server to try it on first?)

once we have the baseline infrastructure up and running, we can think about controlpanel-style next-steps.

Change History (13)

comment:1 Changed 7 years ago by https://id.mayfirst.org/dkg

  • Type changed from Bug/Something is broken to Feature/Enhancement Request

Is this a possibility? I'm tempted to simply declare a guineapig server someplace so that i can use it, but i'm reluctant to move forward without at least a bit of review from other support-team members.

comment:2 Changed 7 years ago by https://id.mayfirst.org/dkg

  • Keywords f2f added
  • Priority changed from Medium to High

I'd like some feedback on this, please!

comment:3 Changed 7 years ago by https://id.mayfirst.org/gregl

I'd be happy for you to declare a test host for postgres testing.

The only concern I have is potential performance loss from running more than one database server on a given host, particularly I/O performance.

comment:4 Changed 7 years ago by https://id.mayfirst.org/dkg

  • Keywords viewsic.mayfirst.org added
  • Owner changed from https://id.mayfirst.org/jamie to https://id.mayfirst.org/dkg
  • Status changed from new to assigned

After some discussion on IRC (and against gregl's better judgement), i've gone ahead and installed postgresql (and python-psycopg2) on viewsic.

I've set up two postgres user accounts, named identically to two member accounts on viewsic.

I set up one database, owned by one of these user accounts, and granted the other user account access to the first role.

comment:5 Changed 6 years ago by https://id.mayfirst.org/ross

  • Keywords f2f removed

We decided to go ahead and add postgres to all the MOSHes. dkg volunteered to do this.

comment:6 Changed 6 years ago by https://id.mayfirst.org/ross

I've added postgresql, php5-pgsql, and python-psycopg2 to the mosh manifest and pushed to origin. Just need jamie to tag and push puppet changes.

dkg and I concluded that all users in red should be given a postgres role but without database creation privileges. We had more extensive discussions about red interactions with postgres but want to table those and simply begin with the creation of roles for each user.

~/ross

comment:7 Changed 6 years ago by https://id.mayfirst.org/dkg

  • Owner changed from https://id.mayfirst.org/dkg to https://id.mayfirst.org/jamie

has this been included in the latest tag yet? jamie, i'm assigning it to you since the tag seems to depend on you at the moment. It would have been nice to have it in place to help work out #6245.

comment:8 Changed 6 years ago by https://id.mayfirst.org/jamie

I haven't tagged a new release, but the latest puppet changes can be pushed to any server individually via: git push SERVER.

What does it mean to "to be given a postgres role"? Does that mean putting every user in the postgres group?

I don't think that's been added to red, but if you give me the command and a brief explanation or link to an explanation, I can think through how to add that to either puppet or red.

jamie

comment:9 Changed 6 years ago by https://id.mayfirst.org/dkg

No, please do *not* put any user in the postgres group.

Similar to mysql, postgres has its own list of database accounts. Postgres calls these database accounts "roles". Unlike mysql, postgres has a very flexible set of authentication and authorization mechanisms. The stock debian postgres install listens only on a unix-domain socket, and authenticates the connecting users via the SO_PEERCRED mechanism (so it knows which system user is connecting). Then the standard authorization is that a system user is authorized to connect as the role with the same name, if such a role exists.

So if there is a system user account named "foo", all you need to do to grant this user the ability to use postgres is to create a role "foo".

Thus, the proposal is to automatically create a postgres role with the same name as each system user account.

comment:10 Changed 6 years ago by https://id.mayfirst.org/jamie

I'm planning on tagging a puppet release, so I'd like to get this into red before hand.

I think red is the best place for this to happen.

My proposal is:

In red, whenever a user account is granted "server access" (that is when a user account is given a bash shell and put into the sshusers group so it can ssh/sftp to the server and populates the drop down list of available users that a web site can operate as) we also issue the command, as root:

su - -c "createuser -D -S -R $username" postgres

Or... since createuser returns an error if the user exists, we may need something a bit uglier so it can run on update as well (to we can get new users in without having to delete and re-create them):

su - postgres -c "psql --quiet -A -t -c \"SELECT rolname FROM pg_roles WHERE rolname = '$username';\"" | grep $username >/dev/null || su - -c "createuser -D -S -R $username" postgres

If a user account is disabled or deleted, the following command is run:

su - postgres -c "psql --quiet -A -t -c \"SELECT rolname FROM pg_roles WHERE rolname = '$username';\"" | grep $username >/dev/null && su - -c "dropuser $username" postgres || true

The true part at the end is to ensure a exit code of 0 if the user doesn't exist. There must be a better way. Perhaps we need a helper script that can do this more intelligently.

This may have an unintentional effect - if someone wants to disable a user account without realizing that it will kill their postgres-dependent web application. However, since the postgres access really is handled through this user account, it seems like the right thing to do.

Does that seem reasonable?

jamie

comment:11 Changed 6 years ago by https://id.mayfirst.org/dkg

Yes, this approach seems reasonable. If you want to reassign this to me (and elevate priority) i can try to work out a better incantation that will not just hide errors.

I bet the simplest thing would be to use regular if/then instead of the && or || shortcuts if you want it to behave as you expect. This has the nice side effect of making it easier to read as well :)

Last edited 6 years ago by https://id.mayfirst.org/dkg (previous) (diff)

comment:12 Changed 6 years ago by https://id.mayfirst.org/joseph

  • Resolution set to fixed
  • Status changed from assigned to closed

These packages have been added to the mosh.pp file and the changes pushed to all MOSH's.

comment:13 Changed 6 years ago by https://id.mayfirst.org/dkg

  • Resolution fixed deleted
  • Status changed from closed to assigned

Sorry, but this isn't done. there is an outstanding question about which user accounts should have corresponding database accounts.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.