Обсуждение: Locked out of schema public

This is FreeBSD 11.3, with postgres installed from ports as 10.10.

There is included a daily utility doing pg_dump:
: ${daily_pgsql_pgdump_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -bF c"}
pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}


Recently I did a restore of some database, as the postgres user, with:
pg_restore -c -d <db> -h <host> <file>

and now ordinary users are locked out of the database:

PG::UndefinedTable: ERROR:  relation "users" does not exist

=> \d users
Did not find any relation named "users".
=> \d
Did not find any relations.
=> \d public.users
                         Table "public.users"
[etc.etc. all is present]

=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

=> select current_schemas(false);
 current_schemas 
-----------------
 {}
(1 row)

eh???? HOPPALA!!!

=> select * from public.users;
ERROR:  permission denied for schema public


How can this happen? I don't think I twiddled anything with schemas,
in fact I never used them in any way.

cheers,
PMc

On 11/6/19 11:11 AM, Peter wrote:
> 
> This is FreeBSD 11.3, with postgres installed from ports as 10.10.
> 
> There is included a daily utility doing pg_dump:
> : ${daily_pgsql_pgdump_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -bF c"}
> pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}
> 

What is ${daily_pgsql_user} equal to?

> 
> Recently I did a restore of some database, as the postgres user, with:
> pg_restore -c -d <db> -h <host> <file>

I am not seeing -U postgres.
Are you sure there is not something else specifying the user e.g. env 
PGUSER?


> 
> and now ordinary users are locked out of the database:

What user are you doing below as?

What does \dn+ show?


> 
> PG::UndefinedTable: ERROR:  relation "users" does not exist
> 
> => \d users
> Did not find any relation named "users".
> => \d
> Did not find any relations.
> => \d public.users
>                           Table "public.users"
> [etc.etc. all is present]
> 
> => show search_path;
>     search_path
> -----------------
>   "$user", public
> (1 row)
> 
> => select current_schemas(false);
>   current_schemas
> -----------------
>   {}
> (1 row)
> 
> eh???? HOPPALA!!!
> 
> => select * from public.users;
> ERROR:  permission denied for schema public
> 
> 
> How can this happen? I don't think I twiddled anything with schemas,
> in fact I never used them in any way.
> 
> cheers,
> PMc
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

okay, lets check these out:

> What is ${daily_pgsql_user} equal to?

postgres. The owner of the installation.

> I am not seeing -U postgres.
> Are you sure there is not something else specifying the user e.g. env 
> PGUSER?

I'm sure. The log shows the nightly backup connections as
postgres:<db>, and all connections except postgres:postgres work only
with kerberos - it cannot do much bogus there.

> What user are you doing below as?

Ordinary application user. The postgres and superusers do get access
to the tables.

> What does \dn+ show?

=> \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | pgsql=UC/postgres    | 

And after restoring with "pg_restore -C -c -d postgres", when it works
correctly again, then it shows:

-> \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres        +| 
        |          | pgsql=UC/postgres    | 

So that was the command I was searching for. Thank You!


For now  I hold on the bug...

cheers,
PMc