Monday, January 21, 2013

ALTER ROLE in Postgres


[ec2-user@ip-10-195-17-79 ~]$ sudo su -  // become superuser
[root@ip-10-195-17-79 ~]# su postgres -   // become user:postgres
bash-4.1$ psql -U postgres -c '\du'  //UNIX bash shell - check the role
// 'psql -U postgres' means type in command as user postgres
could not change directory to "/root"

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 charlie   | Superuser                                      | {}
 load      |                                                | {}
 lord      |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 serf      |                                                | {}

Problem 1---------------could not change directory to "/root" 

viju <ace_of_blackstar(at)yahoo(dot)com> writes:
> I am new to postgresql. I am having some weird problems.
> Output
>  attached below. Please any help would be appreciated.

> bash-3.2$
>  createdb test1
> could not change directory to "/root"
> bash-3.2$ 
> dropdb test
> could not change directory to "/root"
> bash-3.2$ 

Apparently you did "su postgres" from the root account, so you're
still in root's home directory.  It'd be better to do "su - postgres"
to ensure you've acquired all of the postgres account's environment.
Reading "man su" might help you out here.

   regards, tom lane

-----------------------------
Solution: exit and do it again using su - postgres


[root@ip-10-195-17-79 ~]# su - postgres
-bash-4.1$ psql -U postgres
psql (9.2.2)
Type "help" for help.

postgres=#

//now, I can run sql query on the db: postgres
//now, I am gonna add role attributes to lord and serf
//remember to use semicolon because this is the sql clause.
postgres=# DROP ROLE load;
DROP ROLE
postgres=# DROP ROLE lord;
DROP ROLE
postgres=# drop role serf;
ERROR:  role "serf" cannot be dropped because some objects depend on it
DETAIL:  owner of database serfdb
//role attributes
SUPERUSER
NOSUPERUSER
CREATEDB
NOCREATEDB
CREATEROLE
NOCREATEROLE
CREATEUSER
NOCREATEUSER
INHERIT
NOINHERIT
LOGIN
NOLOGIN
CONNECTION LIMIT connlimit
PASSWORD password
ENCRYPTED
UNENCRYPTED
VALID UNTIL 'timestamp'


postgres=# create role lord with superuser encrypted password 'xxxxxxx';
CREATE ROLE
postgres=# alter role serf with encrypted password 'xxxxxx';
ALTER ROLE



No comments:

Post a Comment