How to let clients to create new users?

Some (quite long) time ago, someone, somewhere (my memory is pretty fragile) asked a question. I don't have it exact, but the gist was: is it possible to give some users rights to create new users, without making them superusers, and forcing new users to have access only to one particular database.

After some discussion it was clear that the scenario was shared hosting with PostgreSQL, so the situation could look like this:

you are administrator of shared hosting service. One of services is PostgreSQL. You have client, named “depesz", and you want him to be able to create new users, but these users shouldn't be able to connect to any other database than depesz's db.

Is it doable?

Continue reading How to let clients to create new users?

Waiting for 9.1 – final post

And so, it happened. After todays refresh of my Pg, I got:

$ select version();
                                                        version                                                         
------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2devel on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit
(1 row)

Yes. We're in 9.2 development now. Looks like we got another really cool release coming soon.

Thanks a lot to all developers.

OmniPITR – important update

Couple of important points:

  • Fixed omnipitr-backup-slave on Pg 9.0
  • Added tests system which lets me check if the omnipitr actually work after I change stuff
  • Finally added version information.

The fix is important – in some cases, making backups off slave on 9.0 will not work.

By not work I mean that backups would be still done, but Pg wouldn't start from it. If started as slave, it would work OK, but you wouldn't be able to promote it to standalone.

New version of OmniPITR fixes that, as long as you're using -cm switch (and possibly some others like -h, -P, -U or -d – details in docs.

Version information is actually not really simple to get, it requires:

perl -I/opt/omnipitr/lib -le 'use OmniPITR::Program; print $OmniPITR::Program::VERSION'

But it works. It will be changed to normal –version option in near future.

Also, as you can perhaps see, we migrated to github.

Pagination with fixed order

Some time ago I wrote about getting fast pagination. While fast, it had some problems which made it unusable for some. Specifically – you couldn't get page count, and easily jump to page number N.

I did some thinking on the subject, and I think I found a way to make it all work. Quite fast. And with not big overhead. Let me show you.

Continue reading Pagination with fixed order

Understanding postgresql.conf : log*

After loooong pause, adding next (well, second) post to the “series“. This time, I'd like to describe how logging works. And I don't mean binary logging (WAL), but the log for us, humans, to read.

Before I will go to the postgresql.conf options, let me first describe what happens when PostgreSQL wants to log something.

Continue reading Understanding postgresql.conf : log*

Find cheapest combination of rooms in hotels

Today, on Stack Overflow there was interesting question.

Generally, given table that looks like this:

room | people | price   | hotel
 1   |    1   |   200   |   A
 2   |    2   |   99    |   A
 3   |    3   |   95    |   A
 4   |    1   |   90    |   B
 5   |    6   |   300   |   B

Find cheapest combination of rooms that would accomodate given number of guests.

Continue reading Find cheapest combination of rooms in hotels

Waiting for 9.1 – Synchronous replication

On 6th of March, Simon Riggs committed patch:

Efficient transaction-controlled synchronous replication.
If a standby is broadcasting reply messages and we have named
one or more standbys in synchronous_standby_names then allow
users who set synchronous_replication to wait for commit, which
then provides strict data integrity guarantees. Design avoids
sending and receiving transaction state information so minimises
bookkeeping overheads. We synchronize with the highest priority
standby that is connected and ready to synchronize. Other standbys
can be defined to takeover in case of standby failure.
 
This version has very strict behaviour; more relaxed options
may be added at a later date.
 
Simon Riggs and Fujii Masao, with reviews by Yeb Havinga, Jaime
Casanova, Heikki Linnakangas and Robert Haas, plus the assistance
of many other design reviewers.

Continue reading Waiting for 9.1 – Synchronous replication