| 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: | Whole Thread | Raw Message | 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
| 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- |