General Tidbits has a number of useful How To documents and code details of articles from previous General Bits Articles. Don't forget to look around!

Also do not forget to check out the Archives. It contains all of the previously published General Bits articles. Click on the Month to see the list of titles.

Beta access to Indexable General Bits is available to Subscribers.

Editor: elein at varlena.com

Don't hack the system tables! Ways to corrupt your database 24-Jun-2004

It is never a good idea to update the system tables in the pg_catalog schema. Unanticipated side affects can ruin your day, week or data. This is especially true when you change anything that affects the structure of any object.

There are standard procedures for adding or removing columns, changing column types, changing column lengths, renaming objects and all of the things you would like to do to change the structure of objects. The standard procedure is to use the ALTER command where ever possible and where it is not possible, use a standard methodology that does not involve the system tables.

One example that someone ran into recently was that one should not fix varchar column size by updating the appropriate system table.

In the past, such as version 7.2, it was possible to adjust varchar columns with the wrong size by updating the attypemod column of the appropriate row in the pg_attributes table to the correct value. As of version 7.4, this approach is no longer safe.

Under some circumstances, hacking the system tables in this way can cause indexes and views which were dependant on the varchar column to behave oddly, such as hiding certain values or returning "Variable Not Found in Subplan List" errors. Unfortunately, if you encounter this issue, the only known fix at that point its to dump your database to backup and re-load into a clean database.

To properly change a datatype in a column, using ALTER add a new column, insert the data from the old column into the new column. Then ALTER again to delete the old column and rename the new column. This sounds simple but can get a little complicated when PRIMARY KEYs, FOREIGN KEYS and INDEXES are involved. These related CONSTRAINTS and INDEXES must be recreated. When there are VIEWS or FUNCTION referencing the tables, these VIEWS and FUNCTIONS must also be recreated.

Below we show

• Changing a column's data type with no relations or constraints
• Changing a tables PRIMARY KEY data type.
• Changing an indexed column's data type.
• Changing a referenced column's data type.

-- ==============================================================
-- Changing the type of a column with no constraint or reference
-- ==============================================================
drop table abc;
create table abc (
   a  integer PRIMARY KEY,
   b  varchar(16),
   c  timestamp
);
-- creates constraint abc_pkey on column a
insert into abc values (1, 'one', now());
insert into abc values (2, 'two', now());
insert into abc values (3, 'three', now());
alter table abc
	add column cc date;
update abc set cc=c;
alter table abc
	drop column c;
alter table abc
	rename cc to c;

-- ====================================
-- changing the type of a primary key
-- ====================================
drop table abc;
create table abc (
	a	integer PRIMARY KEY,
	b	varchar(16),
	c	timestamp
);
-- creates constraint abc_pkey on column a
insert into abc values (1, 'one', now());
insert into abc values (2, 'two', now());
insert into abc values (3, 'three', now());
alter table abc 
	add column aa bigint;
update abc set aa=a;
-- cascade drops primary key constraint
alter table abc
	drop column a cascade; 
alter table abc
	rename column aa TO a;
-- recreate primary key constraint
alter table abc
	add constraint abc_pkey PRIMARY KEY(a);

-- =======================================
-- Changing the type of an indexed column
-- =======================================
drop table abc;
create table abc (
   a  integer PRIMARY KEY,
   b  varchar(16),
   c  timestamp
);
-- creates constraint abc_pkey on column a
insert into abc values (1, 'one', now());
insert into abc values (2, 'two', now());
insert into abc values (3, 'three', now());

create index bvar on abc (b);

alter table abc
	add column bb varchar(32);
update abc set bb = b;
-- cascade drops index on column b
alter table abc
	drop column b cascade; 
alter table abc
	rename column bb to b;
-- recreate index on column b
create index bvar on abc (b);

-- ====================================
-- When there is a foreign key involved
-- column def.d references abc.b
-- ====================================
drop table abc;
create table abc (
   a  integer PRIMARY KEY,
   b  varchar(16) UNIQUE,
   c  timestamp
);
insert into abc values (1, 'one', now());
insert into abc values (2, 'two', now());
insert into abc values (3, 'three', now());

drop table def;
create table def (
	a	integer PRIMARY KEY,
	d	text REFERENCES abc (b),
	e	timestamp
);
insert into def values (2, 'one', now());
insert into def values (4, 'two', now());
insert into def values (6, 'three', now());

alter table abc
   add column bb text;
update abc set bb=b;
-- drops unique constraint on abc.b
-- also drops foreign key constraint on def.d
alter table abc
   drop column b cascade;
alter table abc
   rename bb to b;
-- recreate unique constraint by creating a unique index
create unique index unqb on abc (b);
alter table def
	add  constraint fkd FOREIGN KEY(d) REFERENCES abc(b);

Contributors: josh at agliodbs.com elein at varlena.com

Proof of Concept: Row Level Security Row level security based on USER 27-Jun-2004

Oracle has a concept where only owners of a given ROW can update or delete that row. This idea piqued my interest and so I've put together a proof of concept that this is implementable easily using RULES in PostgreSQL.

I would be very interested in other techniques that people may have to implement the same concept. The requirement is that the queries need not be known in advance.

The cost of this implementation is in the set up of the RULES, GRANTS and that a user identifier be placed in each row. In this implementation I've used USER. The USER could be hashed to a smaller value if row space is an issue. The USER variable is connection dependent and available to all queries within the session. This means that if you re-use connections you must reset the USER to ensure the proper behavior.

This concept requires the use of GRANT and REVOKE to set the privileges so that the underlying table is not altered without the security check. Be careful if you are fast and loose with enabling users to have postgresql superuser privileges. If the user is a super user, the underlying table will be able to be changed, whether or not they created the table.

In this implementation, this is what we do for each table we want to participate in the row ownership scheme:

• Create the base table with a login column
• Create a view on the base table which does not display the login column
• Create an INSERT RULE on the view which populates the login column.
• Create UPDATE and DELETE RULES on the view which qualify the change by the USER.
• Revoke all privileges from the underlying table.
• Add INSERT, UPDATE and DELETE privileges to the view.

	--
	-- Row level security based on USER
	--
	\c pgdb postgres
	drop table abc cascade;
	create table abc (
	   a integer PRIMARY KEY,
	   b integer,
	   login text
	);
	create view abcv as select a, b from abc;
	
	create or replace rule abc_in AS ON INSERT
	   TO abcv
	   DO INSTEAD
	      insert into abc values (NEW.a, NEW.b, USER );
	
	create or replace rule abc_upd AS ON UPDATE
	   TO abcv
	   DO INSTEAD 
	      update abc SET a=NEW.a, b=NEW.b
	      where a=OLD.a and login = USER;
	
	create or replace rule abc_del AS ON DELETE
	   TO abcv
	   DO INSTEAD 
	      delete from abc 
	      where a=OLD.a and login=USER;
	
	revoke all on abc from bob;
	revoke all on abc from elein;
	grant insert, update, delete on abcv to elein;
	grant insert, update, delete on abcv to bob;

To emphasize the point about super users the statements below show that the creator of the table and super user postgres can change the underlying table but the user bob cannot. Also note that elein is also a super user and can also change the underlying tables. The REVOKE and GRANT are ignored in that case.

	pgdb=# \c pgdb postgres
	You are now connected to database "pgdb" as user "postgres".
	pgdb=# insert into abc values (1,2,'postgres');
	INSERT 19051594 1
	pgdb=# insert into abcv values (2,3);
	INSERT 19051595 1

	pgdb=# select * from abc;    
	 a | b |  login   
	---+---+----------
	 1 | 2 | postgres
	 2 | 3 | postgres
	(2 rows)
	
	pgdb=# \c pgdb bob
	You are now connected to database "pgdb" as user "bob".
	pgdb=> insert into abc values (4,5,'who?');
	ERROR:  permission denied for relation abc
	pgdb=> insert into abc values (6,7,'bob');
	ERROR:  permission denied for relation abc
	pgdb=> update abc set login='bob' where a=1;
	ERROR:  permission denied for relation abc
	pgdb=> delete from abc where a=1;
	ERROR:  permission denied for relation abc

	pgdb=> \c pgdb elein
	You are now connected to database "pgdb" as user "elein".
	pgdb=# insert into abc values (4,5,'who?');
	INSERT 19051596 1
	pgdb=# insert into abc values (6,7,'bob');
	INSERT 19051597 1
	pgdb=# update abc set login='bob' where a=1;
	UPDATE 1
	pgdb=# delete from abc where a=1;
	DELETE 1

	pgdb=# select * from abc;
	 a | b |  login   
	---+---+----------
	 2 | 3 | postgres
	 4 | 5 | who?
	 6 | 7 | bob
	(3 rows)

One more thing to notice is that in psql, super user prompts are database=# and non-super user prompts are database=>.

The SQL for this example is available for download.

Contributors: elein at varlena.com kudos at #postgresql greg at eyetools.com