Ok so if you've come to this point you now have your database up and running and you want to allow others to access it. Some people say it's not the brightest idea, but hey, this data ain't going to share itself. Obviously letting them use your account is out of the question because as a superuser that will just allow them to wreak havoc any time they want on your meticulously gathered data. That's were new users come in to save the day
Postgres users work pretty much the same way as Mac OSX users. You have a username and a password, and each user is given rules and limitations on what they can do. I'll get more into what privileges are available for a Postgres user in the next tutorial, but for now just keep in mind that they are the rules that let the database administrator know what each individual user can do on the Postgres server
Difference Between a User and a Role
In older versions of Postgres a User and a Role used to be more seperately defined. But from version 8.0 onward a User has been defined as a subset of a Role, with the main difference being that when you create a User Postgres assumes that it has login privileges to the Postgres sever. If you create a role it does not have login privileges unless you specifically define those privileges.
Gennerally what I like to do is create users and then create roles that I assign to each individual user to manage their privileges of what databases they can connect to and what actions they can take on each database.
Yes it is a bit confusing I understand, but I'll go over how I generally do things that helps keep it seperate for me. So ok let's look at it in practice and see if that makes things a little clearer.
Creating Your First User
Ok so you need to add one user to your Postgres server then this would be the syntax:
CREATE USER matt WITH PASSWORD 'password';
Ok so with that you created one user with the user name
and the password
password. Obviously, that's an awful password
and don't ever do that unless you want to have a bad time. The good news is
you created your first user, the bad news is they can't do anything yet other
than login to the Postgres server.
If you ever lose track of the Users, or Roles, you've created on your
Postgres server all you need to do is type
\du and Postgres
will show you a table of all the roles available on the server.
But what good is a user if they can't do anything? No good if you ask me
that's why I'll show you how to grant them
to a table so they can at least access your data. Here is the syntax to do
GRANT SELECT ON ALL TABLES IN SCHEMA public TO matt;
So now the user
matt has the ability to
query all the tables that have the schema public. If you are just starting
out with postgres most of your tables will use the public schema as that is
the default choice. This also raises another issue as well with your new
user. Since they have access to the public schema by default on Postgres
that means they have
USAGE or read permissions and
which means they can create new tables on your database.
Obviously as a superuser you could just delete what they create, but it's better to just not allow it in the first place. This means you need to revoke that privilege which is done with this syntax:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
This removes the ablity for your new user to create a new table on the database using the public schema. This is generally a good idea as a user that creates a table becomes an onwer of that table; meaning they can insert whatever data they want to the data and grant other users privileges to it as well. You always want to make sure that your users can only do what you want them to do. If you're fine with them creating new tables then ignore this, but most likely you won't be.