Locked out of schema public

From: Peter <pmc(at)citylink(dot)dinoex(dot)sub(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Locked out of schema public
Date: 2019-11-06 19:11:53
Message-ID: 20191106191153.GA12072@gate.oper.dinoex.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-11-06 20:47:38 Re: Locked out of schema public
Previous Message Tom Lane 2019-11-06 16:21:41 Re: v12 and pg_restore -f-