RE: Read only to schema

[Charles Clavadetscher]

Hello Jakek

 

From: Łukasz Jarych [mailto:jarys...@gmail.com] 
Sent: Samstag, 28. Juli 2018 14:13
To: Melvin Davidson ; pgsql-gene...@postgresql.org >> 
PG-General Mailing List 
Subject: Re: Read only to schema

 

Hi Guys,

 

thank you for such advances replies and help ! You are the best!

 

I have found out that this command:

 

GRANT ALL PRIVILAGES ON ALL TABLES IN SCHEMA schema_name TO role_name; 

 

Be aware that granting all privilege is not necessarily the best approach. You 
are granting e.g. a user the permission to create triggers on your table that 
can perform actions transparently or to truncate the table.

If this is really what you want and need, well, go for it. Otherwise restrict 
it to the privileges the user really needs, e.g.

 

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ...

 

Regards.

Charles

 

is working for me. 

 

I will read more and try to use it properly! 

 

Thank you very much, 

Best,

Jacek 

 

 

 

pt., 27 lip 2018 o 17:55 Melvin Davidson mailto:melvin6...@gmail.com> > napisał(a):

Jacek, 
you appear to be confused on how security works in PostgreSQL.

Please review the options in 

 

https://www.postgresql.org/docs/10/static/sql-grant.html


and read the Description that follows on that page

As owner or superuser, you can
GRANT CONNECT {CREATE} ON DATABASE;
GRANT USAGE {CREATE} ON SCHEMA schema_name TO role_name;
GRANT SELECT ON TABLE table_name TO role_name;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

What you want is probably the last of these.

If you are still confused, you should probably purchase 
or download 

PostgreSQL Administration Essentials
Chapter 3: Users and Permissions

from https://www.packtpub.com/all?search=PostgreSQL+Administration+Essentials 

 &availability_list%5BAvailable%5D=Available&offset=&rows=&sort=

 

On Fri, Jul 27, 2018 at 11:31 AM, Charles Clavadetscher 
mailto:clavadetsc...@swisspug.org> > wrote:

Hi

 

Please notice that in this mailing list it is not customary to top post.

 

From: Łukasz Jarych [mailto:jarys...@gmail.com  ] 
Sent: Freitag, 27. Juli 2018 12:19
To: Charles Clavadetscher mailto:clavadetsc...@swisspug.org> >; pgsql-gene...@postgresql.org 
  >> PG-General Mailing List 
mailto:pgsql-gene...@postgresql.org> >
Subject: Re: Read only to schema

 

Hi,

 

ok i hate this. 

 

SELECT datname, datacl FROM pg_database where datname = 'AccessLog'; 

 



\What does it mean?

 

The ACL (Access Control List) contains all the information about which role can 
do what on that object, in this case a database and which role granted the 
privileges.

 

You can find detailed information in the documentation:

https://www.postgresql.org/docs/10/static/sql-grant.html

 

An ACL is an array of aclitem, which again is basically a key/value pair with 
an equal sign between the key and the value.

In this context the key is the grantee, i.e. the role being granted a privilege.

The value is the list of privileges (see list following the link above) granted 
to that role, followed by a slash (/) and the role that granted the privilege.

The list of acronyms is a.o. in the link I gave above. Here for your 
convenience:

 

rolename= -- privileges granted to a role

= -- privileges granted to PUBLIC

 

r -- SELECT ("read")

w -- UPDATE ("write")

a -- INSERT ("append")

d -- DELETE

D -- TRUNCATE

x -- REFERENCES

t -- TRIGGER

X -- EXECUTE

U -- USAGE

C -- CREATE

c -- CONNECT

T -- TEMPORARY

  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)

* -- grant option for preceding privilege

 

/ -- role that granted this privilege

 

If the key is empty, it means that the privilege is granted to public, i.e. 
every role.

 

Example:

 

admin=CTc/postgres means that role postgres granted Connect (c), Create (C) and 
Temporary (T) to role admin.

 

Notice that the granting role may differ from the one you are using, if you are 
acting as superuser:

 

“If a superuser chooses to issue a GRANT or REVOKE command, the command is 
performed as though it were issued by the owner of the affected object. In 
particular, privileges granted via such a command will appear to have been 
granted by the object owner. (For role membership, the membership appears to 
have been granted by the containing role itself.)”

 

If the ACL is empty (NULL) then the predefined default privileges apply. >From 
the doc:

 

“PostgreSQL grants default privileges on some types of objects to PUBLIC. No 
privileges are granted to PUBLIC by default on tables, table columns, 
sequences, foreign data wrappers, foreign serve

Re: postgres with xcode

[Adrian Klaver]

On 07/28/2018 04:04 AM, Sumit Chaturvedi wrote:
Hello everyone. I'm a third year student from IIT Bombay, India. Since 
I'm currently learning about databases, I wanted to debug postgresql 
with xcode. However, I am having a lot of problems.

OS - 10.13.6 (High Sierra)
Xcode - 9.4.1

To set up my system, I tried to replicate the instructions found at 
https://wiki.postgresql.org/wiki/Working_with_Eclipse 
 for my case.


Everything proceeded fine until a point where I had to change the run 
configuration (scheme in xcode terms). I set the executable as postgre 
from /src/backend and gave the appropriate arguments. Then, when I 
pressed run, I got an error as shown in the attachment.


I can tell you where the error comes from:

postmaster.c

/*
* On macOS, libintl replaces setlocale() with a version that calls
* CFLocaleCopyCurrent() when its second argument is "" and every relevant
* environment variable is unset or empty.  CFLocaleCopyCurrent() makes
* the process multithreaded.  The postmaster calls sigprocmask() and
* calls fork() without an immediate exec(), both of which have undefined
* behavior in a multithreaded program.  A multithreaded postmaster is the
* normal case on Windows, which offers neither fork() nor sigprocmask().
*/
if (pthread_is_threaded_np() != 0)
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("postmaster became multithreaded during 
startup"),
errhint("Set the LC_ALL environment variable to 
a valid locale.")));


To solve it, I set up my environment variables in xcode appropriately to 
make sure that locale information was reaching wherever it was needed. 
But even now, when I press run I get the same error.


I do not see that the LC_ALL environment variable is set, per the HINT 
to the FATAL message.




Hoping someone can share with me how they went about with their setup.

--
Sumit Chaturvedi



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: postgres with xcode

[Adrian Klaver]

On 07/28/2018 07:15 AM, Sumit Chaturvedi wrote:
Please reply to list also.
Ccing list.


Hello. Thanks a lot for your response. Yes that was also there.


Note: Bottom posting is the style on this list. Unfortunately the 
screenshot is not coming through on this reply. For those that see this 
the screenshot showed:


LC_ALL en_US.UTF8

Generally it is a better idea to cut and paste from command line output 
or a text file then show a screenshot. For the reason above and also 
because screenshots make it difficult for other users to cut and paste 
into their machines for testing/debugging purposes.


Back to the problem at hand. The error message only shows up in the 
section I quoted before with regards to locale handling on Macs.


Are you having any issues with starting Postgres outside of xcode?



On Sat, Jul 28, 2018 at 7:41 PM Adrian Klaver > wrote:


On 07/28/2018 04:04 AM, Sumit Chaturvedi wrote:
 > Hello everyone. I'm a third year student from IIT Bombay, India.
Since
 > I'm currently learning about databases, I wanted to debug postgresql
 > with xcode. However, I am having a lot of problems.
 > OS - 10.13.6 (High Sierra)
 > Xcode - 9.4.1
 >
 > To set up my system, I tried to replicate the instructions found at
 > https://wiki.postgresql.org/wiki/Working_with_Eclipse
 >  for my case.
 >
 > Everything proceeded fine until a point where I had to change the
run
 > configuration (scheme in xcode terms). I set the executable as
postgre
 > from /src/backend and gave the appropriate arguments. Then, when I
 > pressed run, I got an error as shown in the attachment.

I can tell you where the error comes from:

postmaster.c

/*
* On macOS, libintl replaces setlocale() with a version that calls
* CFLocaleCopyCurrent() when its second argument is "" and every
relevant
* environment variable is unset or empty.  CFLocaleCopyCurrent() makes
* the process multithreaded.  The postmaster calls sigprocmask() and
* calls fork() without an immediate exec(), both of which have undefined
* behavior in a multithreaded program.  A multithreaded postmaster
is the
* normal case on Windows, which offers neither fork() nor sigprocmask().
*/
if (pthread_is_threaded_np() != 0)
      ereport(FATAL,

  (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),

                          errmsg("postmaster became multithreaded
during
startup"),
                          errhint("Set the LC_ALL environment
variable to
a valid locale.")));

 > To solve it, I set up my environment variables in xcode
appropriately to
 > make sure that locale information was reaching wherever it was
needed.
 > But even now, when I press run I get the same error.

I do not see that the LC_ALL environment variable is set, per the HINT
to the FATAL message.

 >
 > Hoping someone can share with me how they went about with their
setup.
 >
 > --
 > Sumit Chaturvedi


-- 
Adrian Klaver

adrian.kla...@aklaver.com 

--
Sumit Chaturvedi



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Read only to schema

[Łukasz Jarych]

Thank you Charles once again !

And best wishes,
Jacek

sob., 28 lip 2018 o 15:58 Charles Clavadetscher 
napisał(a):

> Hello Jakek
>
>
>
> *From:* Łukasz Jarych [mailto:jarys...@gmail.com]
> *Sent:* Samstag, 28. Juli 2018 14:13
> *To:* Melvin Davidson ; pgsql-gene...@postgresql.org
> >> PG-General Mailing List 
> *Subject:* Re: Read only to schema
>
>
>
> Hi Guys,
>
>
>
> thank you for such advances replies and help ! You are the best!
>
>
>
> I have found out that this command:
>
>
>
> *GRANT ALL PRIVILAGES ON ALL TABLES IN SCHEMA schema_name TO role_name;*
>
>
>
> Be aware that granting all privilege is not necessarily the best approach.
> You are granting e.g. a user the permission to create triggers on your
> table that can perform actions transparently or to truncate the table.
>
> If this is really what you want and need, well, go for it. Otherwise
> restrict it to the privileges the user really needs, e.g.
>
>
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ...
>
>
>
> Regards.
>
> Charles
>
>
>
> is working for me.
>
>
>
> I will read more and try to use it properly!
>
>
>
> Thank you very much,
>
> Best,
>
> Jacek
>
>
>
>
>
>
>
> pt., 27 lip 2018 o 17:55 Melvin Davidson 
> napisał(a):
>
>
> *Jacek, you appear to be confused on how security works in PostgreSQL.*
>
> *Please review the options in *
>
>
>
> *https://www.postgresql.org/docs/10/static/sql-grant.html
> *
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *and read the Description that follows on that pageAs owner or superuser,
> you canGRANT CONNECT {CREATE} ON DATABASE;GRANT USAGE {CREATE} ON SCHEMA
> schema_name TO role_name;GRANT SELECT ON TABLE table_name TO
> role_name;GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO
> role_name;What you want is probably the last of these.If you are still
> confused, you should probably purchase or download PostgreSQL
> Administration EssentialsChapter 3: Users and Permissionsfrom
> https://www.packtpub.com/all?search=PostgreSQL+Administration+Essentials&availability_list%5BAvailable%5D=Available&offset=&rows=&sort=
> *
>
>
>
> On Fri, Jul 27, 2018 at 11:31 AM, Charles Clavadetscher <
> clavadetsc...@swisspug.org> wrote:
>
> Hi
>
>
>
> Please notice that in this mailing list it is not customary to top post.
>
>
>
> *From:* Łukasz Jarych [mailto:jarys...@gmail.com]
> *Sent:* Freitag, 27. Juli 2018 12:19
> *To:* Charles Clavadetscher ;
> pgsql-gene...@postgresql.org >> PG-General Mailing List <
> pgsql-gene...@postgresql.org>
> *Subject:* Re: Read only to schema
>
>
>
> Hi,
>
>
>
> ok i hate this.
>
>
>
> SELECT datname, datacl FROM pg_database where datname = 'AccessLog';
>
>
>
> [image: image.png]
>
> \What does it mean?
>
>
>
> The ACL (Access Control List) contains all the information about which
> role can do what on that object, in this case a database and which role
> granted the privileges.
>
>
>
> You can find detailed information in the documentation:
>
> https://www.postgresql.org/docs/10/static/sql-grant.html
>
>
>
> An ACL is an array of aclitem, which again is basically a key/value pair
> with an equal sign between the key and the value.
>
> In this context the key is the grantee, i.e. the role being granted a
> privilege.
>
> The value is the list of privileges (see list following the link above)
> granted to that role, followed by a slash (/) and the role that granted the
> privilege.
>
> The list of acronyms is a.o. in the link I gave above. Here for your
> convenience:
>
>
>
> rolename= -- privileges granted to a role
>
> = -- privileges granted to PUBLIC
>
>
>
> r -- SELECT ("read")
>
> w -- UPDATE ("write")
>
> a -- INSERT ("append")
>
> d -- DELETE
>
> D -- TRUNCATE
>
> x -- REFERENCES
>
> t -- TRIGGER
>
> X -- EXECUTE
>
> U -- USAGE
>
> C -- CREATE
>
> c -- CONNECT
>
> T -- TEMPORARY
>
>   arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
>
> * -- grant option for preceding privilege
>
>
>
> / -- role that granted this privilege
>
>
>
> If the key is empty, it means that the privilege is granted to public,
> i.e. every role.
>
>
>
> Example:
>
>
>
> admin=CTc/postgres means that role postgres granted Connect (c), Create
> (C) and Temporary (T) to role admin.
>
>
>
> Notice that the granting role may differ from the one you are using, if
> you are acting as superuser:
>
>
>
> “If a superuser chooses to issue a GRANT or REVOKE command, the command is
> performed as though it were issued by the owner of the affected object. In
> particular, privileges granted via such a command will appear to have been
> granted by the object owner. (For role membership, the membership appears
> to h

Re: postgres with xcode

[Sumit Chaturvedi]

Hello.

Thanks for those pointers. Will keep them in mind in the future. No, there
is no problem outside Postgres.

Could it be that postmaster becomes multithreaded for some other reason??

On Sat, Jul 28, 2018 at 9:57 PM, Adrian Klaver 
wrote:

> On 07/28/2018 07:15 AM, Sumit Chaturvedi wrote:
> Please reply to list also.
> Ccing list.
>
> Hello. Thanks a lot for your response. Yes that was also there.
>>
>
> Note: Bottom posting is the style on this list. Unfortunately the
> screenshot is not coming through on this reply. For those that see this the
> screenshot showed:
>
> LC_ALL en_US.UTF8
>
> Generally it is a better idea to cut and paste from command line output or
> a text file then show a screenshot. For the reason above and also because
> screenshots make it difficult for other users to cut and paste into their
> machines for testing/debugging purposes.
>
> Back to the problem at hand. The error message only shows up in the
> section I quoted before with regards to locale handling on Macs.
>
> Are you having any issues with starting Postgres outside of xcode?
>
>
>> On Sat, Jul 28, 2018 at 7:41 PM Adrian Klaver > > wrote:
>>
>> On 07/28/2018 04:04 AM, Sumit Chaturvedi wrote:
>>  > Hello everyone. I'm a third year student from IIT Bombay, India.
>> Since
>>  > I'm currently learning about databases, I wanted to debug
>> postgresql
>>  > with xcode. However, I am having a lot of problems.
>>  > OS - 10.13.6 (High Sierra)
>>  > Xcode - 9.4.1
>>  >
>>  > To set up my system, I tried to replicate the instructions found at
>>  > https://wiki.postgresql.org/wiki/Working_with_Eclipse
>>  >  for my
>> case.
>>  >
>>  > Everything proceeded fine until a point where I had to change the
>> run
>>  > configuration (scheme in xcode terms). I set the executable as
>> postgre
>>  > from /src/backend and gave the appropriate arguments. Then, when I
>>  > pressed run, I got an error as shown in the attachment.
>>
>> I can tell you where the error comes from:
>>
>> postmaster.c
>>
>> /*
>> * On macOS, libintl replaces setlocale() with a version that calls
>> * CFLocaleCopyCurrent() when its second argument is "" and every
>> relevant
>> * environment variable is unset or empty.  CFLocaleCopyCurrent() makes
>> * the process multithreaded.  The postmaster calls sigprocmask() and
>> * calls fork() without an immediate exec(), both of which have
>> undefined
>> * behavior in a multithreaded program.  A multithreaded postmaster
>> is the
>> * normal case on Windows, which offers neither fork() nor
>> sigprocmask().
>> */
>> if (pthread_is_threaded_np() != 0)
>>   ereport(FATAL,
>>   (errcode(ERRCODE_OBJECT_NOT_
>> IN_PREREQUISITE_STATE),
>>   errmsg("postmaster became multithreaded
>> during
>> startup"),
>>   errhint("Set the LC_ALL environment
>> variable to
>> a valid locale.")));
>>
>>  > To solve it, I set up my environment variables in xcode
>> appropriately to
>>  > make sure that locale information was reaching wherever it was
>> needed.
>>  > But even now, when I press run I get the same error.
>>
>> I do not see that the LC_ALL environment variable is set, per the HINT
>> to the FATAL message.
>>
>>  >
>>  > Hoping someone can share with me how they went about with their
>> setup.
>>  >
>>  > --
>>  > Sumit Chaturvedi
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>> --
>> Sumit Chaturvedi
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
Sumit Chaturvedi

Re: postgres with xcode

[Adrian Klaver]

On 07/28/2018 11:08 AM, Sumit Chaturvedi wrote:

Hello.

Thanks for those pointers. Will keep them in mind in the future. No, 
there is no problem outside Postgres.


So you can start the Postgres server and use it from a program other 
then xcode?




Could it be that postmaster becomes multithreaded for some other reason??


That error message only shows up in one place in the code and that is 
when dealing with Mac locales.


You can download the source from here:

https://www.postgresql.org/ftp/source/

and look for yourself.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: postgres with xcode

[Sumit Chaturvedi]

Yes, I can start Postgres server from the terminal. I was thinking, if I
comment out these lines and another assert later on which checks whether
postmaster is using multiple threads, I'll be fine right?


On Sat, Jul 28, 2018 at 11:46 PM, Adrian Klaver 
wrote:

> On 07/28/2018 11:08 AM, Sumit Chaturvedi wrote:
>
>> Hello.
>>
>> Thanks for those pointers. Will keep them in mind in the future. No,
>> there is no problem outside Postgres.
>>
>
> So you can start the Postgres server and use it from a program other then
> xcode?
>
>
>> Could it be that postmaster becomes multithreaded for some other reason??
>>
>
> That error message only shows up in one place in the code and that is when
> dealing with Mac locales.
>
> You can download the source from here:
>
> https://www.postgresql.org/ftp/source/
>
> and look for yourself.
>
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
Sumit Chaturvedi

Re: postgres with xcode

[Adrian Klaver]

On 07/28/2018 11:24 AM, Sumit Chaturvedi wrote:
Ccing list
Yes, I can start Postgres server from the terminal. I was thinking, if I 
comment out these lines and another assert later on which checks whether 
postmaster is using multiple threads, I'll be fine right?


If by fine you mean driving further into the crash then yes. The error 
is FATAL for a reason, to stop the postmaster before you do damage.


The issue seems to be with xcode, that is where you should spend your 
time troubleshooting. If it where me I would start by not setting the 
environment variables in xcode. Further help may involve going to an 
xcode mailing list/forum and asking advice.




On Sat, Jul 28, 2018 at 11:46 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 07/28/2018 11:08 AM, Sumit Chaturvedi wrote:

Hello.

Thanks for those pointers. Will keep them in mind in the future.
No, there is no problem outside Postgres.


So you can start the Postgres server and use it from a program other
then xcode?


Could it be that postmaster becomes multithreaded for some other
reason??


That error message only shows up in one place in the code and that
is when dealing with Mac locales.

You can download the source from here:

https://www.postgresql.org/ftp/source/


and look for yourself.





-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Sumit Chaturvedi



--
Adrian Klaver
adrian.kla...@aklaver.com

Publication/Subscription Questions

[xOChilpili]

Hi everyone,

Im just testing/learning how subscriptions and publications work, then this
is what i have done until now:

Server A :
create database test_pubsubs;

create table foo(
 id_foo serial not null primary key,
foo varchar(20) not null
​);​
insert into foo values(1,'foo');
insert into foo values(2,'foobar');


create table foobar(
  id_foobar serial not null primary key,
 foobar varchar(20) not null
);
insert into foobar values(1,'foobaz');
insert into foobar values(2,'foobax');


create publication my_publication for table foo;

Server B :

create database test_pubsubs;

create table foo(
 id_foo serial not null primary key,
foo varchar(20) not null
​);​

create table foobar(
  id_foobar serial not null primary key,
 foobar varchar(20) not null
);


create subscription my_subscription connection 'host=server_a
dbname=test_pubsubs user=my_user password=my_password port=5432'
publication my_publication;

select * from foo;
id_foo | foo
1 | foo
2 | foobar
select * from foobar;
0 Rows


Server A:

alter publication my_publication add table foobar;

Server B:
alter subscription my_subscription refresh publication;

select * from foobar;
id_foobar | foobar
1 | foobaz
2 | foobax

Then, here's my question :

Still on Server B:

delete from foo;
delete from foobar;

select * from foo;
0 Rows
select * from foobar;
0 Rows

alter subscription my_subscription refresh publication;

select * from foo;
0 Rows
select * from foobar;
0 Rows

Why ? If i remove rows, from Server B and refresh publication, why data is
not re-sync ?
But if i :
drop subscription my_subscription;
and create it again, then i have all data back...


Thanks a lot!


-- 
xOCh


--
PAranoids Group

218

Re: Publication/Subscription Questions

[Christophe Pettus]

> On Jul 27, 2018, at 01:34, xOChilpili  wrote:
> 
> Why ? If i remove rows, from Server B and refresh publication, why data is 
> not re-sync ?

ALTER SUBSCRIPTION ... REFRESH PUBLICATION doesn't do another initial copy of 
the data for existing tables in the publication.  Its function is to add tables 
that were added to the publication after the subscription was last created or 
refreshed.  It does (by default) copy the data from newly-added tables, but it 
does not resync the data from the existing tables.

--
-- Christophe Pettus
   x...@thebuild.com

Re: Publication/Subscription Questions

[Adrian Klaver]

On 07/27/2018 01:34 AM, xOChilpili wrote:

Hi everyone,

Im just testing/learning how subscriptions and publications work, then 
this is what i have done until now:


Server A :
create database test_pubsubs;

create table foo(
  id_foo serial not null primary key,
foo varchar(20) not null
​);​
insert into foo values(1,'foo');
insert into foo values(2,'foobar');


create table foobar(
   id_foobar serial not null primary key,
  foobar varchar(20) not null
);
insert into foobar values(1,'foobaz');
insert into foobar values(2,'foobax');


create publication my_publication for table foo;

Server B :

create database test_pubsubs;

create table foo(
  id_foo serial not null primary key,
foo varchar(20) not null
​);​

create table foobar(
   id_foobar serial not null primary key,
  foobar varchar(20) not null
);


create subscription my_subscription connection 'host=server_a 
dbname=test_pubsubs user=my_user password=my_password port=5432' 
publication my_publication;


select * from foo;
id_foo | foo
1 | foo
2 | foobar
select * from foobar;
0 Rows


Server A:

alter publication my_publication add table foobar;

Server B:
alter subscription my_subscription refresh publication;

select * from foobar;
id_foobar | foobar
1 | foobaz
2 | foobax

Then, here's my question :

Still on Server B:

delete from foo;
delete from foobar;

select * from foo;
0 Rows
select * from foobar;
0 Rows

alter subscription my_subscription refresh publication;

select * from foo;
0 Rows
select * from foobar;
0 Rows

Why ? If i remove rows, from Server B and refresh publication, why data 
is not re-sync ?


https://www.postgresql.org/docs/10/static/sql-altersubscription.html

"Fetch missing table information from publisher. This will start 
replication of tables that were added to the subscribed-to publications 
since the last invocation of REFRESH PUBLICATION or since CREATE 
SUBSCRIPTION."


You have not added a table to the publication there is nothing to sync.


But if i :
drop subscription my_subscription;
and create it again, then i have all data back...


Thanks a lot!


--
xOCh


--
PAranoids Group

218



--
Adrian Klaver
adrian.kla...@aklaver.com