Privilege	short name	Description
SELECT	r	Can read data from the object.
INSERT	a	Can insert data into the object.
UPDATE	w	Can change data in the object.
DELETE	d	Can delete data from the object.
RULE	R	Can create a rule on the table
REFERENCES	r	Can create a foreign key to a table. Need this on both sides of the key.
TRIGGER	t	Can create a trigger on the table.
TEMPORARY	T	Can create a temporary table.
EXECUTE	X	Can run the function.
USAGE	U	Can use the procedural language.
ALL	arwdRxt	All appropriate privileges. For tables, this equates to arwdRxt

You can apply these privileges to users, groups or a special target called PUBLIC, which is any user on the system.

Viewing privileges

You can view permissions using the \z command in psql.

You can use \d to view the owner.
dbaref=# \dp
 

Adding privileges

You can assign privileges using the GRANT command. GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]


The WITH GRANT OPTION allows you give the person you are granting the privileges the ability to grant that privilege themselves. We can give bob the ability to make any changes to the data in suppliers using: GRANT INSERT, UPDATE, DELETE ON TABLE suppliers TO bob;


Removing privileges

You can also remove privileges using the REVOKE which has the same syntax as the GRANT.REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]


GRANT OPTION FOR allows you to remove the ability to grant privileges to others, and not the privileges themselves. Suppose you want to remove privileges from bob, and anyone he has granted it to, we can use the CASCADE option. REVOKE INSERT UPDATE DELETE ON TABLE suppliers FROM bob CASCASE


Column Level Privileges

PostgreSQL doesn't directly support privileges at the column level but you can fake the, using views. To do this, you create a view with all the columns you want that person to see and grant them privileges to view that view.

Changing Ownership

It is possible to change the ownership of objects using the ALTER TABLE: ALTER TABLE suppliers OWNER TO bob;


This can be time consuming to do if you have a lot of tables. A quicker, but possibly dodgy way to fix this is to use the following untested SQL command. You need to set relowner to the sysid of the new owner, which you can find by checking pg_shadow. UPDATE pg_class SET relowner = 100 WHERE pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = 'public';