steve on Sep 15th 2009 General
If you need to get in and edit some files on your xen domU instance, i.e. it isn’t booting up properly, etc, here’s how to mount it on dom0. In this case, we want to mount the second partion on the virtual disk (our root partition):
Print out the partition layout:
fdisk -l /path/to/img/file.img
Disk System.img: 0 MB, 0 bytes
255 heads, 63 sectors/track, 0 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
System.img1 1 4 32098+ 83 Linux
System.img2 * 5 619 4939987+ 83 Linux
System.img3 620 750 1052257+ 82 Linux swap / Solaris
create directory to mount image under: mkdir /mnt/tmp
mount the image using the offset from the fdisk output, the start block (in this case 5)
mount mount -o loop,offset=$((512*5) /path/to/img/file.img /mnt/tmp
Edit files as needed, and unmount when done.
steve on Aug 24th 2009 tech
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}
