PostgreSQL ALTER DEFAULT PRIVILEGES – permissions explained
Many people have heard that ALTER DEFAULT PRIVILEGES makes it easy to allow other users access to tables. But then, many people don’t understand the command in depth, and I hear frequent complaints that ALTER DEFAULT PRIVILEGES does not work as expected. Read on if you want to know better!
You cannot understand ALTER DEFAULT PRIVILEGES unless you know what default privileges are. Default privileges are the privileges on an object right after you created it. On all object types, the default privileges allow everything to the object owner. On most objects, nobody else has any privileges by default. But on some objects, PUBLIC (everybody) has certain privileges:
This is normally not a problem, because objects without any privileges are extremely rare. If you need to tell these cases apart, you have to look at the system catalogs:
While “no privileges” is an empty array of aclitems, PostgreSQL stores default privileges as a NULL value. This also explains why \dp shows nothing in that case.
