search

2ndQuadrant / pglogical

Logical Replication extension for PostgreSQL 17, 16, 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
http://2ndquadrant.com/en/resources/pglogical/
Other
1.01k stars 153 forks source link

Please document: "grant usage on schema pglogical to <dbname>" (permission denied for schema pglogical) #361

Open tomharrisonjr opened 2 years ago

tomharrisonjr commented 2 years ago

An issue opened in 2016 is still unresolved and undocumented, specifically any database on a server with pglogical installed will get permission denied for schema pglogical on certain destructive operations like drop. There may be some good technical reasons why this is so, but at the very least, it should be mentioned in the documentation, right?

Solution, or at least a workaround appears to be for a privileged user to execute grant usage on schema pglogical to my_db.

petere commented 2 years ago

Can you show in more details what steps you are taking to arrive at this error?

Creating an extension requires superuser privileges, so it's generally assumed that someone managing pglogical has superuser access somehow. That is not to say your request is invalid, but I suppose not many people run into it.

kmoppel-cognite commented 2 years ago

Still seems to be a problem with the latest (2.4.1) version. The workaround was to run GRANT USAGE ON SCHEMA pglogical TO public;

What was done (on PostgreSQL 12.11):

  1. Set up pglogical (shared_preload_libraries + create ext)
  2. Create a publication for all tables
  3. Set up a Postgres v14 replication subscriber
  4. Drop the Sub / replication slot and also the publication
  5. Couldn't drop an existing-before-pglogical-setup table after that with a normal non-privileged user that once create the table: 
    DROP TABLE IF EXISTS feature_test_173116415;
ERROR: permission denied for schema pglogical
ns-mkusper commented 1 year ago

Same issue here with 2.4.3 and postgres 15.3. The same workaround also works to 'resolve' it, but far from desirable behavior.