Postgresql forgets grants after recreating tables

Question

We use liquibase to manage and execute our database changes. On our DEV environment (and especially on local machines) we frequently recreate the tables to have a clean slate. We have just migrated from MySQL to Postgres and are facing a problem related to these table recreations.

Initially we have granted our DB user with this:

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO mydbuser;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO mydbuser;

This is executed through psql after connecting to our own DB (where public is the default/only schema).

Everything is fine until we ask liquibase to recreate the tables in which case it will drop all tables and create them again.

After that it appears that mydbuser has lost all its grants on the tables.

According to several resources (like this) we need to alter the default privileges, so we obey:

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO mydbuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO mydbuser;

GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO mydbuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO mydbuser;

However, although this seems very logical it changed nothing. All grants (even select) are still lost after recreating the tables.

What are we doing wrong or what else do we need to do?

UPDATE

\ddp shows: Default access privileges

Owner | Schema | Type | Access privileges
----------+--------+----------+--------------------
postgres | public | sequence | mydb=rU/postgres
postgres | public | table | mydb=arwd/postgres

Answer 1

If trying to mimic what liquibase is doing in a simplified test case, it just works.

Quick demo with 9.3:

1) Create the objects from scratch with the postgres user:

postgres=# create database dbtest;
CREATE DATABASE

postgres=# create user mydbuser;
CREATE ROLE

postgres=# \c dbtest
You are now connected to database "dbtest" as user "postgres".

dbtest=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO mydbuser;
ALTER DEFAULT PRIVILEGES

dbtest=# create table foobar(id int);
CREATE TABLE

2) In another session let's connect with mydbuser and see if SELECT is allowed.

dbtest=> select * from foobar;
 id 
----
(0 rows)

Result: Yes, it's allowed. Note the result of \ddp too:

dbtest=> \ddp
             Default access privileges
  Owner   | Schema | Type  |   Access privileges    
----------+--------+-------+------------------------
 postgres | public | table | mydbuser=arwd/postgres
(1 row)

3) Let's have postgres drop the table and recreate it:

dbtest=# drop table foobar;
DROP TABLE
dbtest=# create table foobar(id int);
CREATE TABLE

4) See if mydbuser can still SELECT from it.

$ psql -d dbtest -U mydbuser
dbtest=> select * from foobar;
 id 
----
(0 rows)

Result: Yes, as expected.

5) See if another user can SELECT from it.

$ psql -d dbtest -U daniel
dbtest=> select * from foobar;
ERROR:  permission denied for relation foobar

Result: No, as expected.

This does not explain what doesn't work for you, but you may compare the above with the set of commands issued by liquibase.