The ramblings of Steve-0Posts RSS Comments RSS

Archive for August, 2009

steve on Aug 24th 2009 tech

Creating a true read-only user in PostgreSQL

I develop a product at work that (among many other things) allows users to easily create and manage databases to interact with other packages on their appliance. One feature that was requested and green-lighted is the ability to create a read-only user for existing databases- a user that can connect to a given database and access all tables and views in that DB, and nothing else.

Since PostgreSQL is our database of choice, I started researching the process, thinking it would be a couple mysql-esqe GRANT statements and that would be it. Turns out that it is a huge PITA in postgres – even my solution I’m documenting here has its shortcomings, but as far as I know is the best / only way to accomplish this task. I ran across quite a few sites that helped with pieces of this, but none that actually tied the process together for production usage. I am by no means a Postgres expert, but do have a good bit of experience mucking around with back-end settings and figuring how to script common tasks.

There are two main things to watch out for when trying to create a read-only user in PostgreSQL, especially if you come from a DB like MySQL:

  • PostgreSQL only sets permissions on objects, not on databases, so you need to grant read access to all your tables/views/etc, and if you add a table down the line, you need to remember to manully grant read access to it after creating the table.
  • I’m guessing 95% of postgres users just use the default “public” schema, and as such, you need to revoke create privileges from the PUBLIC group. Otherwise, your “read-only” user will still be allowed to create tables that it owns, even if you’ve only given it read only access to all other objects in your database.

    • For this example, we’ll use database name “mydb”, database user/owner “mydbuser”, and we’ll create a read-only user named “mydbuser_ro”. This assumes that you did not define a schema for your database and are using the default “public” schema.

  • Revoke default permissions from public group:
    REVOKE CREATE ON SCHEMA public FROM PUBLIC;
    REVOKE USAGE ON SCHEMA public FROM PUBLIC;
  • Add back permissions for your database owner:
    GRANT CREATE ON SCHEMA public TO mydbuser;
    GRANT USAGE ON SCHEMA public TO mydbuser;
  • Create the new user via the command line, or pgadmin/etc:
    psql -U postgres -t -c "create role mydbuser_ro password 'abc123' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
  • Grant usage permissions for your read-only user:
    GRANT USAGE ON SCHEMA public TO mydbuser_ro;
  • Grant select permissions on all database tables from the command line:
    psql -U postgres -qAt -c "select 'grant select on ' || tablename || ' to \"mydbuser_ro\";' from pg_tables where schemaname = 'public'" mydb | psql -U postgres mydb
  • Setup remote access for the read only user in pg_hba.conf as appropriate

    • Once complete, you can verify the settings with a quick sql query. You should see something like this, with the user=UC (for Usage/Create), and user_ro=U (for Usage):
    mydb=> select * from pg_namespace where nspname='public';
    nspname | nspowner | nspacl
    ---------+----------+------------------------------------------------------------------
    public | 10 |
    {postgres=UC/postgres,mydbuser=UC/postgres,mydbuser_ro=U/postgres}

    One response so far

    steve on Aug 4th 2009 tech

    Syncing Adium chat logs across multiple Macs, v2.0

    I previously posted about a rather convoluted method to use mobileme and idisk to sync adium logs across computers, but after using that for a while, it turned into an exercise in frustration, as it was not reliable, and idisk is horribly slow.
    I just came across a new solution to the same problem that is very simple, costs nothing, and performs great. Basically, let dropbox do the syncing for you. Instructions as follows:
    - Sign up, download and install dropbox here
    - Shutdown adium (or other chat client)
    - Open a Terminal session
    - Move your adium logs folder to dropbox:
    mv ~/Library/Application\ Support/Adium\ 2.0/Users/Default/Logs ~/Dropbox/Private/AdiumLogs
    -create a symbolic link from your adium folder to your dropbox private folder:
    ln -s ~/Dropbox/Private/AdiumLogs ~/Library/Application\ Support/Adium\ 2.0/Users/Default/Logs

    On any other computer you’d like to keep in sync with:
    - sync over your existing logs with rsync:
    rsync -avl ~/Library/Application\ Support/Adium\ 2.0/Users/Default/Logs/ ~/Dropbox/Private/AdiumLogs
    - Move your old logs folder out of the way:
    mv ~/Library/Application\ Support/Adium\ 2.0/Users/Default/Logs ~/Library/Application\ Support/Adium\ 2.0/Users/Default/Logs.old
    -create a symbolic link from your adium folder to your dropbox private folder:
    ln -s ~/Dropbox/Private/AdiumLogs ~/Library/Application\ Support/Adium\ 2.0/Users/Default/Logs

    It really is that simple, and works great – thanks Dropbox for a great bit of software.

    One response so far