Thread: Trying to create DB / user to import some data
I am new to Postgres but not to DBMS.
Running on Postgres 9.1 Ubuntu 13.04.
I log in as Postgres user: psql -h localhost -U postgres
and then I create a db and user and allow grants to the user like this:
create user import_dbms_user with password 'import_dbms';
create database import_dbms_db;
grant all privileges on database import_dbms_db to import_dbms_user;
\du shows:
import_dbms_user | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
In the pg_hba.conf I set as:
# local DATABASE USER METHOD [OPTIONS]
local import_dbms_db import_dbms_user md5
and restart Postgres.
However when I try to run psql from the command line:
psql -h localhost -U import_dbms_user -WI enter password when prompted
Password for user import_dbms_user:
psql: FATAL: database "import_dbms_user" does not exist
But I get the error as above.
Trying to understand what I may be doing wrong.
Do I need to assign some kind of login role to import_dbms_user or such?
i assumed in the pg_hba.conf the line above is all I needed to log in
locally?
local import_dbms_db import_dbms_user md5
And instead of local I would enter IP address of machine from which I would
want to log in to the server?
192.168.1.10 import_dbms_db import_dbms_user md5
I am running on a VM and am trying to run some python scripts with psycopg2
to load some data.
So that script cannot log in either. Do I need to create any special role /
priviledge for that user to login from that script as well?
Thanks for your help!
Mono
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sep 26, 2013, at 10:28 PM, mdr <monosij.forums@gmail.com> wrote: > > create user import_dbms_user with password 'import_dbms'; > create database import_dbms_db; > grant all privileg > However when I try to run psql from the command line: > psql -h localhost -U import_dbms_user -WI enter password when prompted > Password for user import_dbms_user: > psql: FATAL: database "import_dbms_user" does not exist > > But I get the error as above. By default, psql will try to use the username as the database name if it is not specified. Since your database name is different from the user, this does not work. You could either name both your user and db "import_dbms" and have the default work, or specify the database explicitly with"-d import_dbms_db" Hope that helps.
Hi Steven - Thanks. Worked great. I assumed it would assume the dbname to be import_dbms_db as import_dbms_user was granted admin privileges on it. Also I do have import_dbms_user and import_dbms_db in my pg_hba.conf as: local import_dbms_db import_dbms_user md5 I still need that - correct? And I will need to have another entry specifying the ip addr of the machine to connect from if I am connecting from a different machine? So to connect from Python scripts (using psycopg2) do I need to specify the database to connect to as well? And if that python script is running from a different machine the ip addr of that machine needs to be in the pg_hba.conf? I ask as this is not connecting for me right now but I will check on the psycopg forum as well. Just wanted to confirm. Thank you for your help. Mono -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568p5772622.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/27/2013 09:26 AM, mdr wrote: > Hi Steven - > > Thanks. Worked great. I assumed it would assume the dbname to be > import_dbms_db as import_dbms_user was granted admin privileges on it. User names are global to a Postgres cluster, they can be used for any database in that cluster. > > Also I do have import_dbms_user and import_dbms_db in my pg_hba.conf as: > local import_dbms_db import_dbms_user md5 > > I still need that - correct? And I will need to have another entry > specifying the ip addr of the machine to connect from if I am connecting > from a different machine? Yes. I would suggest some time looking at: http://www.postgresql.org/docs/9.3/interactive/auth-pg-hba-conf.html The line you showed previously: local import_dbms_db import_dbms_user md5 will only work for local connections. To connect from a different machine you will need a host line. > > So to connect from Python scripts (using psycopg2) do I need to specify the > database to connect to as well? Yes. > > And if that python script is running from a different machine the ip addr of > that machine needs to be in the pg_hba.conf? Yes. > > I ask as this is not connecting for me right now but I will check on the > psycopg forum as well. > > Just wanted to confirm. > > Thank you for your help. > > Mono > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568p5772622.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
Thanks for your help Adrian. Works great. I had a few other questions on creating primary keys after create table using alter table. I will post them separately so this thread is closed. Thank you both again. Mono -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568p5772628.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 9/26/2013 10:28 PM, mdr wrote:
> create user import_dbms_user with password 'import_dbms';
> create database import_dbms_db;
> grant all privileges on database import_dbms_db to import_dbms_user;
that grant only controls connection and create schema privileges. if you
want this user to have full control of this database, you should instead
have made them the owner, like...
alter database import_dbms_db owner import_dbms_user;
(or simply adding 'owner import_dbms_user' to the create database command)
re: pg_hba.conf, I don't micromanage access in there for most of my
systems, rather, I leave it set something like...
local all postgres peer
local all all md5
host all all 127.0.0.0/8 md5
host all all 10.x.y.0/20 md5 # thats my LAN subnet, I'm not
expecting any connections from outside
--
john r pierce 37N 122W
somewhere on the middle of the left coast
Hi John - Thanks for sharing your connection params. Very helpful to have this and to know it has been tested. Much appreciated all answers here. Mono -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568p5772643.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.