0

I need to share my database from PostgreSQL in my PC with my colleagues in the same office (in the same network). I did some research but I still have some questions regarding it.

A bit background for myself: I have been using PostgreSQL independently for a while and have created a bunch of useful DBs, and now need to share it with others. So I am familiar with PostgreSQL's usage and its syntax, but for its database management, I am still fairly new... My PostgreSQL version is 10.6 and I'm working on Windows.

During my initial research, I found that this post is helpful to my question, but I need more clarification on it. The answer to the above post mentioned:

The user needs access to the database, obviously:

GRANT CONNECT ON DATABASE my_db TO my_user;

And (at least) the USAGE privilege on the schema:

GRANT USAGE ON SCHEMA public TO my_user;

So here goes the 1st question: where should I run these code? Suppose I am on pgAdmin and in the database I would like to share, should I just open a Query Tool and run the syntax there?

Secondly, is the user name (such as my_user used in above code) the same one as we used for our own superuser name? i.e. If my superuser name is just postgres (I believe it is the default during installation), if there's someone wants to grant me access to his DB, he just use postgres for my_user?

Lastly, if there's an user outside of my office, how to grant remote access then?

Additionally, I've added my pg_hba_conf here:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
Gin
  • 129
  • 2
  • 12
  • 1
    When on local network, users can specify your ip in the postgres url. People outside your office can connect to an office vpn and get on the same network before acessing. For postgres url refer here : https://stackoverflow.com/questions/3582552/postgresql-connection-url – Fawaz Jan 28 '19 at 06:37
  • Thanks for the prompt response. So I should use ip as 'my_user' in my code example? And the ip in my postgres url part, you mean the ip address in the url session when I'm in Pgadmin (such as http://127.0.0.1:50787/browser/)? – Gin Jan 28 '19 at 06:42
  • 1
    No, 127.0.0.1 is your loopback address. In order to access your database by others, they need to use your IPv4. – Santhosh Arun Jan 28 '19 at 06:45
  • Thanks for your explanation, making more sense now. And how should they use my IPv4 then? By the example code I wrote above? i.e. GRANT CONNECT ON DATABASE my_db TO my_user; – Gin Jan 28 '19 at 06:50
  • If you like to give them a separate user access, you need to create new user first. Refer [this] (https://www.postgresql.org/docs/8.0/sql-createuser.html) to create new users. Then you can grant privileges on how they need to access you database. – Santhosh Arun Jan 28 '19 at 06:53
  • To know your IPv4, Go to command prompt and type ipconfig. There you will notice your IP something like 192.xxx.x.xxx. Then you replace your 127.0.0.1 with this address, like 192.xxx.x.xxx:50787/browser – Santhosh Arun Jan 28 '19 at 06:55
  • 1
    Database, user and password are all part of postgres url. So when a user is connecting, that user user will be accessing a database directly and run the queries. You need to create a user with access to that db and share the details first before user can connect. You're welcome!!! – Fawaz Jan 28 '19 at 06:56

1 Answers1

3

Unless you changed the default permissions, both of the GRANT statements you quote are unnecessary: By default PUBLIC (i.e. everybody) has the CONNECT privilege on all databases and the USAGE privilege on schema public.

You might want to REVOKE the CREATE privilege on schema public from PUBLIC if you intend to give others access.

I'd recommend that you don't hand out the credentials to your postgres superuser to your co-workers unless they need to administrate the database and they know enough about PostgreSQL to be trusted not to accidentally break the database.

So I would create a new login role and grant it all necessary privileges on the tables, views and sequences in your database.

There are two more things you might to have to do to make it work:

  • Set listen_addresses = '*' in postgresql.conf and restart the database.

  • Add entries in pg_hba.conf that allow the users in and reload the database.

    An entry that allows user myuser to connect to database mydbname from any computer would be:

    host   mydbname   myuser   0.0.0.0/0   md5

    You can use a more restrictive netmask of course.

To give specific answers to your questions:

  1. You can run these statements in the Query Tool in pgAdmin. That works as well as any other client.

  2. You wouldn't need to grant postgres any privileges, because a superuser like postgres is exempt from permission checks. You'd only have to tell your co-workers the password (and configure PostgreSQL to accept remote connections). But as I wrote above, I recommend that you create a new user specifically for that purpose.

  3. Granting access to people outside your office is no different (PostgreSQL has no idea where your office ends). All you need is for these outside people to have network access to the database machine (and an appropriate pg_hba.conf entry).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks a lot for your clarification! Just a couple of follow ups: 1. It seems that multiple people can access the same server by the same superuser name and password the same time (I understand this is the least recommended way and has many risks)? 2. Would you please give me an example code for 'You might want to REVOKE the CREATE privilege on schema public from PUBLIC if you intend to give others access.'? 3. In above comments of my question, there are folks commented that coworker can access my db by IP, I guess that's an alternative way of your suggestion? Thanks a lot! – Gin Jan 28 '19 at 17:17
  • Moreover... I've edited my question and added the pg_hba.conf. So should I just sub '127.0.0.1/32' with my own IPv4 or should I also include the IPv4 of my coworkers' (where I wanna share to)? And how about the IP in the replication part? shall I change there as well? Thanks. Sorry for the bombardment of questions.... – Gin Jan 28 '19 at 21:11
  • 1
    I have added a sample `pg_hba.conf` entry. You'd have to add the IP addresses of your co-workers' machines (or a netmask that they match). If you don't use streaming replication, the `replication` entries are irrelevant. – Laurenz Albe Jan 29 '19 at 07:28
  • Hi Laurenz, one follow up question as I am going to give access to my server to other users in a couple of weeks. Once I edited my pg_hba.conf and created users I wish to allow access (in another word, I'm all set up in my end), do my coworkers need to edit their pg_hba.conf in their machine (simply, is there anything they need to do in their end)? Or they just need to add a server in their machine to access my server by using the username and credentials I created for them? Thanks. – Gin Feb 12 '19 at 01:14
  • 1
    No, `pg_hba.conf` is only relevant for incoming connections. Don't forget to reload the server after editing it. – Laurenz Albe Feb 12 '19 at 06:38