Thread: 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