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(dot)klaver(at)aklaver(dot)com