Here is my (incomplete) database creation script:

Code:
-- Create the database. --
CREATE DATABASE db_website;

\connect db_website;

-- Drop default creation privileges. --
REVOKE CREATE ON SCHEMA PUBLIC FROM PUBLIC;

-- Register the plpgsql language. --
CREATE PROCEDURAL LANGUAGE plpgsql;

-- Define a custom datatype to use with images. --
CREATE TYPE image AS (mime_type text, width integer, height integer, contents bytea);

-- Register C functions used for image I/O. --
CREATE FUNCTION fn_delete_image(integer) RETURNS void LANGUAGE c STRICT AS '/var/lib/postgresql/8.4/data/extra/lib/website.so';
CREATE FUNCTION fn_get_fullsize_image(integer) RETURNS image LANGUAGE c STRICT AS '/var/lib/postgresql/8.4/data/extra/lib/website.so';
CREATE FUNCTION fn_get_thumbnail_image(integer) RETURNS image LANGUAGE c STRICT AS '/var/lib/postgresql/8.4/data/extra/lib/website.so';
CREATE FUNCTION fn_set_image(integer, bytea) RETURNS void LANGUAGE c STRICT AS '/var/lib/postgresql/8.4/data/extra/lib/website.so';

-- Create primary key sequences. --
CREATE SEQUENCE sq_tbl_news_category_id;
CREATE SEQUENCE sq_tbl_news_entry_id;
CREATE SEQUENCE sq_tbl_photo_album_id;
CREATE SEQUENCE sq_tbl_photo_image_id;

-- Create base tables. --
CREATE TABLE tbl_news_category (id integer DEFAULT nextval('sq_tbl_news_category_id') PRIMARY KEY, title text NOT NULL, description text NOT NULL);
CREATE TABLE tbl_news_entry (id integer DEFAULT nextval('sq_tbl_news_entry_id') PRIMARY KEY, news_category_id integer NOT NULL REFERENCES tbl_news_category ON UPDATE CASCADE ON DELETE CASCADE, title text NOT NULL, "timestamp" timestamp without time zone DEFAULT now() NOT NULL, body text NOT NULL);
CREATE TABLE tbl_photo_album (id integer DEFAULT nextval('sq_tbl_photo_album_id') PRIMARY KEY, title text NOT NULL, description text NOT NULL);
CREATE TABLE tbl_photo_image (id integer DEFAULT nextval('sq_tbl_photo_image_id') PRIMARY KEY, photo_album_id integer NOT NULL REFERENCES tbl_photo_album ON UPDATE CASCADE ON DELETE CASCADE, title text NOT NULL, description text NOT NULL, "timestamp" timestamp DEFAULT now() NOT NULL);

-- Trigger function for deleting an image on disk when its table record has been deleted. --
CREATE FUNCTION fn_tr_delete_image() RETURNS trigger LANGUAGE plpgsql AS
$$
	BEGIN
		PERFORM fn_delete_image(OLD.id);
		RETURN NULL;
	END;
$$;

-- Register the trigger to delete disk images when their respective table records are deleted. --
CREATE TRIGGER tr_delete_image AFTER DELETE ON tbl_photo_image FOR EACH ROW EXECUTE PROCEDURE fn_tr_delete_image();

-- Secure sensitive functions against default execution. --
REVOKE EXECUTE ON FUNCTION fn_delete_image(integer) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION fn_set_image(integer, bytea) FROM PUBLIC;

-- Give the website user account minimal read privileges. --
GRANT SELECT ON SEQUENCE sq_tbl_news_category_id TO website;
GRANT SELECT ON SEQUENCE sq_tbl_news_entry_id TO website;
GRANT SELECT ON SEQUENCE sq_tbl_photo_album_id TO website;
GRANT SELECT ON SEQUENCE sq_tbl_photo_image_id TO website;
GRANT SELECT ON TABLE tbl_news_category TO website;
GRANT SELECT ON TABLE tbl_news_entry TO website;
GRANT SELECT ON TABLE tbl_photo_album TO website;
GRANT SELECT ON TABLE tbl_photo_image TO website;
GRANT EXECUTE ON FUNCTION fn_get_fullsize_image(integer) TO website;
GRANT EXECUTE ON FUNCTION fn_get_thumbnail_image(integer) TO website;
I want to, by default, not allow users to execute those two functions. I'm under the impression that the two "REVOKE EXECUTE" lines should do this.

Newly created accounts can still execute those functions, however.