Per-user log settings

Can we set log_statement on a per-user basis?

e.g.

ALTER USER [user] SET log_statement TO [none|ddl|mod|all];

Objective is to override the global setting in postgresql.conf; for example, we are required to log all activity from one set of users, but not others, and don't want our logs to be bigger than necessary.

Discussion with some devs and the docs imply that we can do this:

• http://postgresql.1045698.n5.nabble.com/per-user-logging-td1848980.html
• http://www.postgresql.org/docs/9.0/interactive/sql-alteruser.html
• http://www.postgresql.org/docs/9.0/interactive/sql-alterrole.html
• http://www.postgresql.org/docs/9.0/interactive/sql-set.html

Unfortunately, we can't get this to work. Overview of tests below.

So:

• is this a bug in that it should be allowed, but isn't?
• or, is this expected behavior in that it's a "session" setting, and doesn't affect the unprivileged user who's setting has been changed (because it's not that user's session?)
• or, is this a bug in the tab completion? (It would be better if we'd give an error if this isn't allowed.)
• or incomplete docs?
• or we're just doing it wrong?

Additional questions:

• if this worked, how would we find out which user has which setting?
• are there other settings that *do* work?

Initial tests

Here is the SQL I ran as an unprivileged user:

-- this should appear under 'ddl' level
CREATE TABLE testy
(id serial primary key,
name text);
-- this should only appear under 'mod' level
INSERT INTO testy (name)
VALUES
('gabrielle'),
('selena'),
('dan');
-- this should only appear under 'all' level
SELECT * FROM testy;
-- this should only appear under 'mod' level
DROP TABLE testy;

Test 1:

log_statement = 'mod' #in postgresql.conf

postgres=# ALTER USER gabrielle SET log_statement TO 'ddl';
LOG:  statement: ALTER USER gabrielle SET log_statement TO 'ddl';
ALTER ROLE

This led me to believe I was successful setting this param.

However, the user's ddl and mod statements were logged; I expected only the ddl statement. Same results setting user's log_statement to none and all.

Test 2:

log_statement = 'all' #in postgresql.conf

This time, all the user's statements were logged, regardless of what the user's log_statement was set to. IOW, same results as before.

Additional thing to check, per Rob Treat's request:

gabrielle=> ALTER USER gabrielle SET log_statement TO 'mod';
ERROR:  permission denied to set parameter "log_statement"

So, the user can't change it for herself. However, the tab completion implies it's an option. :)