Re: Locked out of schema public

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